[vardy]

I am currently having problems with adding new rows to my database via the datagridview. I am able to edit previously existing data fine, however adding a new row in the datagridview then pressing this button gives the error 'Syntax error in Insert INTO statement'

 
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        Dim i As Integer

        DB.Open()
        SQL = "SELECT * FROM Production"
        da = New OleDb.OleDbDataAdapter(SQL, DB)
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Try

            i = da.Update(ds, "Production_Table")

            MsgBox(i & " " & "Records Updated")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        DB.Close()

    End Sub

if you need to see any of the other code it is here

[November-06]

Do you mean that your database is already displayed in your datagridview then your user can edit the datagridview as if their using MS Excel?

I am not sure if this is what you want. But if this is what you need, then the simple update won't solve your problem. If the user has the ability to add or delete a row in your datagridview, then there will be a problem with regards to updating of the database especially when the number of rows in your database and datagridview don't much.

I have tried to create a similar program to my thesis before. The user uses the datagridview like in excel. He can add, edit, and delete rows. He can change any data in the datagridview save for the primary keys and columns with formula.

So what I did to prevent error on save...

Everytime the user is going to save the datagridview, all the rows in the database are first deleted. And then all the rows in the datagridview in Visual Studio is saved to the database.

So basically after you delete all rows, then you add all rows with .NewRow()

You can try it if you want.

[vardy]

The solution I eventually found was down to a mistake I made with the query to the database. It appears that Days is a reserved word with the engine, so naming one of my columns 'Days' caused the SQL error.

The Update method allows for the database to be updated, so only new data is added/edited. There should be no need to delete the whole table beforehand.

My final code for that button looks like this and is working fully.

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        Dim i As Integer
        Dim cb As New OleDb.OleDbCommandBuilder(da)

        da.InsertCommand = cb.GetInsertCommand()

        Try

            i = da.Update(ds, "Production_Table")
            MsgBox(i & " " & "Records Updated")

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub