Help :: syntax error Command for OleDbDataAdapter
-
Hi all, I got syntax error when I update the database, and I don't know what I;m did wrong. Can some of you give me some hints I have a database table, with the field "First Name","Last Name" , "Phone","Age" and "Status" I don't know is the problem due to the tag "First Name" with the space character? the following is my code for create OleDbDataAdapter:
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(selectCommand, connection) adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey adapter.InsertCommand = New OleDbCommand( _ "INSERT INTO wpGuest (First Name, Last Name, Phone, Age, Status) " & _ "VALUES (?, ?, ?, ?, ?)", connection) adapter.UpdateCommand = New OleDbCommand( _ "UPDATE wpGuest SET First Name = ?, Last Name = ? , Phone = ? , Age = ? , Status = ? " & _ "WHERE First Name = ?", connection) adapter.DeleteCommand = New OleDbCommand("DELETE FROM wpGuest WHERE First Name = ?", connection) ' Create the parameters. adapter.InsertCommand.Parameters.Add( _ "@First Name", OleDbType.Char, 50, "First Name") adapter.InsertCommand.Parameters.Add( _ "@Last Name", OleDbType.Char, 50, "Last Name") adapter.InsertCommand.Parameters.Add( _ "@Phone", OleDbType.Char, 50, "Phone") adapter.InsertCommand.Parameters.Add( _ "@Age", OleDbType.Char, 50, "Age") adapter.InsertCommand.Parameters.Add( _ "@Status", OleDbType.Char, 50, "Status") adapter.UpdateCommand.Parameters.Add( _ "@First Name", OleDbType.Char, 50, "First Name").Value = "c" adapter.UpdateCommand.Parameters.Add( _ "@Last Name", OleDbType.Char, 50, "Last Name").Value = "c" adapter.UpdateCommand.Parameters.Add( _ "@Phone", OleDbType.Char, 50, "Phone").Value = "c" adapter.UpdateCommand.Parameters.Add( _ "@Age", OleDbType.Char, 50, "Age").Value = "c" adapter.UpdateCommand.Parameters.Add( _ "@Status", OleDbType.Char, 50, "Status").Value = "c" adapter.UpdateCommand.Parameters.Add( _ "@First Name", OleDbType.Char, 50, "First Name").SourceVersion = _ DataRowVersion.Original adapter.DeleteCommand.Parameters.Add( _ "@First Name", OleDbType.Char, 50, "First Name").SourceVersion = _ DataRowVersion.Original -
Hi all, I got syntax error when I update the database, and I don't know what I;m did wrong. Can some of you give me some hints I have a database table, with the field "First Name","Last Name" , "Phone","Age" and "Status" I don't know is the problem due to the tag "First Name" with the space character? the following is my code for create OleDbDataAdapter:
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(selectCommand, connection) adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey adapter.InsertCommand = New OleDbCommand( _ "INSERT INTO wpGuest (First Name, Last Name, Phone, Age, Status) " & _ "VALUES (?, ?, ?, ?, ?)", connection) adapter.UpdateCommand = New OleDbCommand( _ "UPDATE wpGuest SET First Name = ?, Last Name = ? , Phone = ? , Age = ? , Status = ? " & _ "WHERE First Name = ?", connection) adapter.DeleteCommand = New OleDbCommand("DELETE FROM wpGuest WHERE First Name = ?", connection) ' Create the parameters. adapter.InsertCommand.Parameters.Add( _ "@First Name", OleDbType.Char, 50, "First Name") adapter.InsertCommand.Parameters.Add( _ "@Last Name", OleDbType.Char, 50, "Last Name") adapter.InsertCommand.Parameters.Add( _ "@Phone", OleDbType.Char, 50, "Phone") adapter.InsertCommand.Parameters.Add( _ "@Age", OleDbType.Char, 50, "Age") adapter.InsertCommand.Parameters.Add( _ "@Status", OleDbType.Char, 50, "Status") adapter.UpdateCommand.Parameters.Add( _ "@First Name", OleDbType.Char, 50, "First Name").Value = "c" adapter.UpdateCommand.Parameters.Add( _ "@Last Name", OleDbType.Char, 50, "Last Name").Value = "c" adapter.UpdateCommand.Parameters.Add( _ "@Phone", OleDbType.Char, 50, "Phone").Value = "c" adapter.UpdateCommand.Parameters.Add( _ "@Age", OleDbType.Char, 50, "Age").Value = "c" adapter.UpdateCommand.Parameters.Add( _ "@Status", OleDbType.Char, 50, "Status").Value = "c" adapter.UpdateCommand.Parameters.Add( _ "@First Name", OleDbType.Char, 50, "First Name").SourceVersion = _ DataRowVersion.Original adapter.DeleteCommand.Parameters.Add( _ "@First Name", OleDbType.Char, 50, "First Name").SourceVersion = _ DataRowVersion.OriginalIt's not a good idea to put spaces in field names. If you do, they MUST be delimited by square brackets, like this:
INSERT INTO wpGuest ([First Name], [Last Name], Phone, Age, Status) VALUES (?, ?, ?, ?, ?)
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007