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
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
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
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
Declare the dataadapter at class level and use same instance of this adapter to fill and update the data table.
ASKER
Ok, I've tried that, but now I get the error
My entire code is now
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.