?
Solved

Error updating DataTable in VB 2010

Posted on 2012-09-20
3
Medium Priority
?
107 Views
Last Modified: 2015-09-19
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
0
Comment
Question by:geekgal
  • 2
3 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38418805
Declare the dataadapter at class level and use same instance of this adapter to fill and update the data table.
0
 

Author Comment

by:geekgal
ID: 38419375
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

0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 38419562
>da.Dispose()

Remove that from form load.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question