2 Replies - 2169 Views - Last Post: 01 May 2011 - 07:13 PM Rate Topic: -----

#1 vardy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 30-April 11

Updating the database with datagridview

Posted 30 April 2011 - 05:29 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Updating the database with datagridview

#2 November-06  Icon User is offline

  • D.I.C Regular

Reputation: 45
  • View blog
  • Posts: 376
  • Joined: 04-January 11

Re: Updating the database with datagridview

Posted 01 May 2011 - 07:00 PM

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.
Was This Post Helpful? 0
  • +
  • -

#3 vardy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 30-April 11

Re: Updating the database with datagridview

Posted 01 May 2011 - 07:13 PM

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


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1