DataAdapter Update Error :Syntax error in INSERT INTO statement

I am trying to add a record to an Access DB Table, everytime I try to update the DataAdapter with the updated DataSet I receive the Error: Syntax error in INSERT INTO statement. Here is the code:

Dim da As OleDb.OleDbDataAdapter

Dim ds As New DataSet

Dim con As New OleDb.OleDbConnection

Dim sSQL As String

con.ConnectionString = "PROVIDER=Microsoft.jet.OLEDB.4.0;Data Source=Alarms.mdb"

con.Open()

sSQL = "SELECT Message FROM [AlarmsList]"

da = New OleDb.OleDbDataAdapter(sSQL, con)

da.Fill(ds,"AlarmList")

Dim dsNR As DataRow

Dim cb = New OleDb.OleDbCommandBuilder(da)

dsNR = ds.Tables("AlarmList").NewRow

dsNR.Item("Message") = "TEST"

ds.Tables("AlarmList").Rows.Add(dsNR)

da.Update(ds, "AlarmList") 'ERROR HERE!!!!

Answer this question

DataAdapter Update Error :Syntax error in INSERT INTO statement

knightmare

hi,

to update you have to add updatecommand to your adapter something like this

con.ConnectionString = "PROVIDER=Microsoft.jet.OLEDB.4.0;Data Source=Alarms.mdb"
con.Open
sSQL = "SELECT Message FROM [AlarmsList]"
da = New OleDbDataAdapter(sSQL, con)
Dim updateSQL As String = "UPDATE TableName set Column1 = value, Column2 = value Where PrimaryKeyColumn = recordKey"
Dim update As OleDbCommand = New OleDbCommand(updateSQL, con)
da.UpdateCommand = update

you can visit this link for SQL update Command

http://sqlcourse.com/update.html

hope this helps

Mark Schmidt

On No, Mr. Bill.... is a thinker of LARGE concepts... not details.
Mike1820
Besides all this, I thought Mr. Bill would know all this stuff!!!

james

aka:Trucker
Engr.Faisal
I think Ken spotted the problem!!

You need to put your column name in brackets in the Select statement [message]

james

aka:Trucker
reukiodo
Try changing

sSql = "Select [Message] from AlarmList"
Ricardo UY
You need to put your update in a Try-Catch to get more details on your error:

Try

da.Update(ds,"AlarmList")

Catch ex as OleDbException

msgBox("ERROR:" & ex.Source & " " & ex.Message, msgBoxStyle.OkOnly)

End Try

Also, if your table contains more than one column, it may be that one of the columns will not accept a nullvalue (no value) and in this case you are updating only one column and ignoring the rest. Be sure that the value(s) you are updating match the types you set when you built your update command. ( you did build an update command didn't you )

james

aka:Trucker
DataAdapter Update Error :Syntax error in INSERT INTO statement

Answer this question