help needed to update database using vb.net

Question

1.00/5 (1 vote)

See more:

im trying to update a table in my ms access database using vb.net. i have tried using the command builder but everytime i rune the code i get an error it says "Syntax error in UPDATE statement"

how can i fix this to successfully updae the ms database?

here is my code:

VB

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

       Dim ca As New OleDb.OleDbCommandBuilder(da)


       ds.Tables("s").Rows(inc).Item(1) = TextBox2.Text
       ds.Tables("s").Rows(inc).Item(2) = TextBox3.Text
       ds.Tables("s").Rows(inc).Item(3) = TextBox4.Text
       ds.Tables("s").Rows(inc).Item(4) = TextBox5.Text
       ds.Tables("s").Rows(inc).Item(5) = TextBox6.Text
       ds.Tables("s").Rows(inc).Item(6) = TextBox7.Text
       ds.Tables("s").Rows(inc).Item(7) = TextBox8.Text
       ds.Tables("s").Rows(inc).Item(8) = TextBox9.Text

       da.Update(ds, "s")


       MsgBox("updated")

all help appreciated.

thanks

Posted 18-Feb-12 13:51pm

alom_93

Updated 18-Feb-12 14:01pm

v3

Add a Solution

Comments

Dave Kreskowiak 18-Feb-12 21:49pm

Without seeing the code that build the DataAdapter, especially the SELECT statement you used, it's impossible to tell you what went wrong.

alom_93 19-Feb-12 11:45am

dataadapter:
dim da as new oledb.oledbdataadapter

SELECT * FROM S
that is the staement used..

can u provide help?

Dave Kreskowiak 19-Feb-12 14:31pm

Do you get this error all the time with every record or just a record that contains a "'" character in one of the fields??

Replace the * with actual column names. You'll be much safer doing it that way.

alom_93 19-Feb-12 18:06pm

on my form if i enter the details for every column and then press update i get this error message but it points at this code:

da.Update(ds, "s")

and says "syntax error in UPDATE statement"

and says

Dave Kreskowiak 19-Feb-12 21:47pm

Yeah, and I asked you WHAT is being entered into those TextBoxes.

alom_93 20-Feb-12 11:01am

text is being entered such as product names, quantity ordered, in stock, product id, date of order etc

Dave Kreskowiak 20-Feb-12 18:18pm

If you returned the primary key of the table (which you did with the * in the SELECT statement) you cannot have the primary key column in the INSERT values collection in the INSERT statement in a table with an autogenerated ID column.

alom_93 20-Feb-12 18:35pm

its not an autogenerated ID column the product ID is set as primary key and data type is text

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)

Ganesan Senthilvel · Answer 1 · 2012-02-18T20:35:00

Solution 1

One of the common issue in Access Update query is that ID is strictly a Text type in Access, string in C#.

Even though Access accepted it from the inside, if called from the program, Access dislikes Found as a column name. You need change the name of the column and referenced it accordingly to execute successfully.

Posted 18-Feb-12 20:35pm

Ganesan Senthilvel

Comments

alom_93 19-Feb-12 11:46am

Thanks for your reply, however i'm a little confused.. Could you kindly explain clearly.

Thanks