How to add additional text to a row in database via vb.net

Question

0.00/5 (No votes)

See more:

VS2010

Hi Guys,

I am creating a program in Visual Studio using vb. I have a database in access 2013, I have a row of data for a client with a notes column, I want to be able to add information to the notes column, go away, come back and add more information without loosing the information i already have. I'm using a UPDATE statement but cant get it to work, your help is much appreciated

Here's my code for the UPDATE STATEMENT:

VB

Dim cn As New OleDbConnection
        cn.ConnectionString = My.Settings.ChiChiEmporiumConnectionString
        Dim cmd As New OleDbCommand
        cmd.CommandText = ("UPDATE Client SET Notes = '" + vbNewLine + ItemIDTextBox.Text + "-" + Transaction_DateDateTimePicker.Value + "' WHERE ([Membership Number] = '" + Membership_NumberTextBox.Text + "')")
        cmd.Connection = cn

        cn.Open()
        cmd.ExecuteNonQuery()

Posted 24-Oct-14 16:23pm

Jonesy1394

Updated 24-Oct-14 16:29pm

v3

Add a Solution

Comments

DamithSL 24-Oct-14 22:28pm

update question with your code

Jonesy1394 24-Oct-14 22:30pm

Have done :)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Answer 1 · 2014-10-24T16:35:00

Solution 1

When appending text, use following syntax

SQL

update
  tablename
set
  fieldname = convert(nvarchar(max),fieldname) + 'appended string'
where 
  fieldname2 = value2

or if you have old text and newly added text in one string. ( also you better use parameters)

VB

cmd.CommandText = "update Client set  Notes = ? where   [Membership Number]= ?"
cmd.Parameters.AddWithValue("@param1", combinedText)
cmd.Parameters.AddWithValue("@param2", Membership_NumberTextBox.Text)

Posted 24-Oct-14 16:35pm

DamithSL

Updated 24-Oct-14 16:47pm

v2

Comments

Jonesy1394 24-Oct-14 22:47pm

Right having used your second lot of code, all that happened was what normally happens the old information was replaced. Gah! Am I missing something?

cmd.CommandText = ("UPDATE Client SET Notes = ? WHERE [Membership Number] = ?")
cmd.Parameters.AddWithValue("@param1", ItemIDTextBox.Text + "-" + Transaction_DateDateTimePicker.Value)
cmd.Parameters.AddWithValue("@param2", Membership_NumberTextBox.Text)