Thread: Help updating database through datagridview

Hi,

I am trying to update a database table via a datagridview. I can successfully populate the grid with data from the table, but when I try to call the update function I get a Syntax error. Based on the error I think I am somehow not linking the datagridview columns to the SQL Update statement, but any help will be appreciated.

Here are the code snippets.

Declarations

Code:

    Public connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Comic-Master\testdatabase.accdb"
    Public sql As String = "SELECT ID, Series, Issue, Publisher FROM testissues"
    Public connection As New OleDb.OleDbConnection(connectionString)
    Public dataadapter As New OleDb.OleDbDataAdapter(sql, connection)
    Public ds As New DataSet()
    Public dTable As DataTable
    Public sqlCmdBuilder As New OleDb.OleDbCommandBuilder(dataadapter)

Open and Filling Datagridview

Code:

        connection.Open()
        dataadapter.Fill(ds, "testissues_table")
        connection.Close()

        mainform.DataGridView2.DataSource = ds.Tables("testissues")
        mainform.DataGridView2.DataMember = "testissues_table"
        dataadapter.UpdateCommand = sqlCmdBuilder.GetUpdateCommand()

Now, here is where I encounter the error. I try to update the table calling the Update function from the dataadapter and get the following error:

Syntax error (missing operator) in query expression '((ID = ?) AND ((? = 1 AND Series IS NULL) OR (Series = ?)) AND ((? = 1 AND Issue IS NULL) OR (Issue = ?)) AND ((? = 1 AND Publisher IS NULL) OR (Publisher = ?))

Update code

Code:

dataadapter.Update(ds.Tables(0))

I've tried several different approaches, like using a bindingsource but still get the same error.

FIXED! Pretty simple....just needed the following...

Code:

        sqlCmdBuilder.QuotePrefix = "["
        sqlCmdBuilder.QuoteSuffix = "]"