Link to home
Start Free TrialLog in
Avatar of geekgal
geekgal

asked on

Error updating DataTable in VB 2010

Hi
The following code is trying to update changes made to a DataTable via a DataGridView. I'm using Visual Studio 2010 Pro and an Access .mdb file.

My code for filling the DataTable and displaying in the DataGridView works fine. The following code, though, returns the error
Syntax error in INSERT INTO statement.
when I click the 'Save' button.

   Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        cs.Open()

        Dim changes As Integer = 0
        Dim da As New OleDbDataAdapter(sql, cs)
        Dim cb As New OleDb.OleDbCommandBuilder(da)

        changes = da.Update(dt)

        If changes > 0 Then
            MsgBox(changes & " changed rows were stored in the database.")
        Else
            MsgBox("No changes made.")
        End If

        cs.Close()
        cs.Dispose()
        da.Update(dt)
        da.Dispose()

End Sub

Open in new window


I've referred to various sources and I just can't figure out
(a) Why the code is making the error
(b) How to fix it

Thanks
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Declare the dataadapter at class level and use same instance of this adapter to fill and update the data table.
Avatar of geekgal
geekgal

ASKER

Ok, I've tried that, but now I get the error

The DataAdapter.SelectCommand property needs to be initialized.
I'm not sure what this means though??

My entire code is now

Imports System.Data
Imports System.Data.OleDb

Public Class Form1

    Dim cs As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\042617\Documents\Visual Studio 2010\Projects\DB Tests\Books Database.mdb")

    Dim sql As String = "SELECT * FROM [Books in Stock]"
    Dim da As New OleDbDataAdapter(sql, cs)
    Dim dt As New DataTable

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        dt.Clear()

        da.Fill(dt)
        da.Dispose()

        DataGridView1.DataSource = dt

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        Dim changes As Integer = 0

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        changes = da.Update(dt)

        If changes > 0 Then
            MsgBox(changes & " changed rows were stored in the database.")
        Else
            MsgBox("No changes made.")
        End If

        cs.Close()
        cs.Dispose()
        da.Update(dt)
        da.Dispose()

    End Sub

End Class

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial