[RESOLVED] datagridview update problem

i get this error when i click on my update button:
Syntax error in UPDATE statement.

my primary key is in column(0) but i hide this column from the user
am i setting up my update command correctly?

vb.net Code:

Private cnxnString As String = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\SaturationTags.mdb")
    Private cnxn As New System.Data.OleDb.OleDbConnection(cnxnString)
    Private sql As New System.Data.OleDb.OleDbCommand
    Private myDataAdapter As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [tblFields]", cnxn)
    Private cmdBuilder As New System.Data.OleDb.OleDbCommandBuilder(myDataAdapter)
    Private dbTable As New DataTable
 
    Public Sub frmDisplayFields_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        myDataAdapter.Fill(dbTable)
        With DataGridView1
            .DataSource = dbTable
            .AllowUserToAddRows = False
            .AllowUserToDeleteRows = False
            .AllowUserToOrderColumns = False
            .AllowUserToResizeColumns = False
            .AllowUserToResizeRows = False
            .Columns(0).Visible = False
            .Columns(1).ReadOnly = True
            .Columns(2).Width = 200
        End With
    End Sub
 
    Private Sub cmdDone_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDone.Click
        myDataAdapter.UpdateCommand = cmdBuilder.GetUpdateCommand
        myDataAdapter.Update(dbTable)
    End Sub

This has got nothing to with the DataGridView. The most common cause of this error is the use of a column name that is an SQL reserved word. The CommandBuilder doesn't wrap identifiers in brackets for you so a reserved word will cause a syntax error. Avoid using reserved words as column and table names if possible. If not, if you write out your query in full and use brackets yourself then I think the CommandBuilder should do the same.

Also, get rid of this line:

vb.net Code:

myDataAdapter.UpdateCommand = cmdBuilder.GetUpdateCommand

It's pointless.

damn, your right. the field name i was trying to update was called "field" (bad choice for a field name). thanks a million. i probably wouldn't have figured that one out for a while.

Quote:

---

Originally Posted by bezaman

damn, your right. the field name i was trying to update was called "field" (bad choice for a field name). thanks a million. i probably wouldn't have figured that one out for a while.

---

It took me a while to figure it out when I first encountered the same thing four or five years ago. Glad to save someone else the same frustration. :)