[RESOLVED] Commit changes to database produces error

[bwoodhouse]

So I'm going through a tutorial on how to get stuff in my vb form to update a Access Database. I know, I should be using sql, but Im just learning. Anyways, here is the code...

Public Class Form1
    WithEvents bsData As New BindingSource
    'setting up variables for database
    Dim inc As Integer
    Dim MaxRows As Integer
    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String


    Private Sub frmMyDemo_Load( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'TestApp.tblTestApp' table. You can move, or remove it, as needed.
        Me.TblTestAppTableAdapter.Fill(Me.TestApp.tblTestApp)

        'setting up database connection
        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = C:\TestApp\TestAppDB.mdb"

        con.ConnectionString = dbProvider & dbSource

        con.Open()

        sql = "SELECT * FROM tblTestApp"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "TestApp")

        MsgBox("Database is now open")

        con.Close()
        MsgBox("Database is now closed")


        MaxRows = ds.Tables("TestApp").Rows.Count
        inc = -1
   Private Sub NavigateRecords()

        txtFirstName.Text = ds.Tables("TestApp").Rows(inc).Item(1)
        txtSurname.Text = ds.Tables("TestApp").Rows(inc).Item(2)

    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If inc <> MaxRows - 1 Then
            inc = inc + 1
            NavigateRecords()
        Else
            MsgBox("No More Records")
        End If
    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If inc > 0 Then
            inc = inc - 1
            NavigateRecords()
        ElseIf inc = -1 Then
            MsgBox("No Records Yet")
        ElseIf inc = 0 Then
            MsgBox("First Record")
        End If
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        If inc <> MaxRows - 1 Then
            inc = MaxRows - 1
            NavigateRecords()
        End If
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        If inc <> 0 Then
            inc = 0
            NavigateRecords()
        End If
    End Sub

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

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        ds.Tables("TestApp").Rows(inc).Item(1) = txtFirstName.Text
        ds.Tables("TestApp").Rows(inc).Item(2) = txtSurname.Text

        da.Update(ds, "TestApp")

        MsgBox("Data Updated")

    End Sub

    Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click

        btnCommit.Enabled = True
        btnAddNew.Enabled = False
        btnUpdate.Enabled = False
        btnDelete.Enabled = False

        txtFirstName.Clear()
        txtSurname.Clear()

    End Sub

    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click

        btnCommit.Enabled = False
        btnAddNew.Enabled = True
        btnUpdate.Enabled = True
        btnDelete.Enabled = True

        inc = 0
        NavigateRecords()

    End Sub

    Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click
        If inc <> -1 Then
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsNewRow As DataRow

            dsNewRow = ds.Tables("TestApp").NewRow()

            dsNewRow.Item(1) = txtFirstName.Text
            dsNewRow.Item(2) = txtSurname.Text

            ds.Tables("TestApp").Rows.Add(dsNewRow)
            MsgBox("New Record added to the database")

            btnCommit.Enabled = False
            btnAddNew.Enabled = True
            btnUpdate.Enabled = True
            btnDelete.Enabled = True
        End If
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)

        If MessageBox.Show("Do you really want to delete this record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then

            MsgBox("Operation Cancelled")
            Exit Sub
        End If

        ds.Tables("TestApp").Rows(inc).Delete()
        MaxRows = MaxRows - 1

        inc = 0
        NavigateRecords()
        da.Update(ds, "TestApp")

    End Sub

    Private Sub TblTestAppBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Validate()
        Me.TblTestAppBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.TestApp)

    End Sub

    Private Sub TblTestAppBindingSource_CurrentChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TblTestAppBindingSource.CurrentChanged

    End Sub
End Class

I get an error stating "OleDbException was unhandled".

I get this for the "Update" and "Delete" buttons. I have gone through the tutorial several times to compare, and I don't see the problem. The error references the line

da.Update(ds, "TestApp")

under the update and Delete buttons.

Furthermore, as written, when you select the "Add New" option, it clears the fields so you can enter your data. Then, according to the tutorial, you hit the "Commit" button, and it gets written to the dataSet and the database, but nothing happens for me. I do not get a new record, as reported, nor an error. Just nothing...

Found the tutorial at www.homeandlearn.co.uk/net/nets12p4.html

Help...

[jmcilhinney]

If there's an OleDbException then there is an error message that will give you, and us, more specific information on what went wrong.

[bwoodhouse]

It says "Syntax error in UPDATE statement"

[kaliman79912]

I am a bit confused here, are you using the BindingSources / TableAdapters generated when you use your local DataSet? or oppening directly with a SQL command.?

[bwoodhouse]

I am not fully educated in this as of yet. This tutorial was to teach me about vb code...

but I do believe the point of the exercise was to build the commands ourselves, not use the ones generated when the local dataset was created.

I may be missing a step, or even the point of the exercise...

If I am really far off, I will start again from scratch and see if I can find the error...

[kaliman79912]

In order for COMMIT to do something, it needs to update the underlying table, you are just changing the values in the data adapter which is not the actual physical table.

Add

da.Update(ds)

after you add the row to the dataset

With only this change you will not see the new row in your current session as the data table is not being filled. But it will be there the next time you launch. Try Filling the table after the Update to fix this.

[kaliman79912]

I have a different version of Acess and a different view. I got it to work with this changes:

on your form load:

sql = "SELECT * FROM tblTestApp"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds)

Your select statement specifies just one table, no need to do that on the da.Fill

Your update sub:

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
 
        Dim cb As New OleDb.OleDbCommandBuilder(da)
 
        ds.Tables(0).Rows(inc).Item(0) = txtFirstName.Text
        ds.Tables(0).Rows(inc).Item(1) = txtSurname.Text
 
        da.Update(ds)
 
        MsgBox("Data Updated")
 
    End Sub

Note that I am using Item(0) and Item(1) vs you using Item(1) and Item(2), There may be a case in which your way is the correct one, but that would be odd. I think you just missed the fact that Item is zero based. Or is it an AutoNumber?

[jmcilhinney]

When you get a syntax error in auto-generated SQL code in pretty much always means there is an issue with one or more of your column names. Either there are special characters in there, e.g. spaces, or else your using a reserved word. There are a few options but the best two are:

1. Change the column names in the database so you're not using any special characters or reserved words.

2. Instruct your command builder to escape the column names by setting its QuotePrefix and QuoteSuffix properties.

If you go with option 2, the values for Access are "[" and "]" respectively.

[bwoodhouse]

I have gone back through the tutorial and it seems to be working for me now. I don't know where I missed a step the first time around still, but now it is working for me.

I tried the suggestion from Kaliman, and it produced a new error. "NulreferenceException was unhandled, Object reference not set to an instance of an object"

However, I am going back through the tutorial and ensuring I do it right this time. Maybe Ill actually learn something and know how to answer your questions or know what the heck you guys are saying one of these days. You are all smarter than I, and thank you for the help, in any case. It seems that the issue has been resolved by going back through the tutorial. I have completely abandoned the code previously posted, and am now working with a new project.

Thanks!

[kaliman79912]

I have gone back through the tutorial and it seems to be working for me now. I don't know where I missed a step the first time around still, but now it is working for me.

I tried the suggestion from Kaliman, and it produced a new error. "NulreferenceException was unhandled, Object reference not set to an instance of an object"

However, I am going back through the tutorial and ensuring I do it right this time. Maybe Ill actually learn something and know how to answer your questions or know what the heck you guys are saying one of these days. You are all smarter than I, and thank you for the help, in any case. It seems that the issue has been resolved by going back through the tutorial. I have completely abandoned the code previously posted, and am now working with a new project.

Thanks!

I would not say anyone is smarter than you, its an experience issue. Keep at it and you will be talking like us...

[Shaggy Hiker]

The NullReferenceException is not one that should cause you any great unease. While they are real problems, they are problems that are readily identifiable and have the same initial steps for solving them. There may be no other exception that always has the same initial steps like that one.

The thing to do when you get that error is examine the line where the error occurred. You can sometimes do this by moving the mouse over different parts, but that seems to rarely work for me. The way that ALWAYS works is to highlight a piece of the code and press Shift+F9, which will show you what is in that piece.

Knowing that, when the exception occurs, you want to look at each object in the line. What is an object isn't always obvious until you have a bit of experience, but I'll use a line I chose at random from one of Kaliman's posts as an example:

ds.Tables(0).Rows(inc).Item(0) = txtFirstName.Text

The objects in this line are:
1) ds
2) ds.Tables(0)
3) ds.Tables(0).Rows(inc)
4) ds.Tables(0).Rows(inc).item(0)
5) txtFirstName
6) txtFirstName.Text

If that happend to be the line that threw the exception, then when you examined those objects, one of them would be Nothing, and that is the cause of the problem. You would soon realize that some of the items on that list really couldn't be the cause of the problem, too, so in short order you would know which ones are likely and which are not. For instance, item #6 on that list would never cause that exception under any circumstances, and item #5 is extremely unlikely to ever be the culprit.

Once you know which item is Nothing, you have the problem half solved. Unfortunately, the other half depends on the actual line, and which item is Nothing. However, that's the way to start, and quite often, once you determine which object is Nothing, it becomes obvious why.