Syntax Error with Update Statement

Question

0.00/5 (No votes)

See more:

SO basically i am trying to create a VB.net connection to Microsoft Access.
My problem is that whenever i try to update the rows in my Access through my VB.net it doesn't work at all. it keep popping out the same error stated above. I have bolded the Area where the error is always appearing. I would be nice if someone can take the time to read my code and find what my mistake is.

VB

Public Class Customer

    Dim con As New OleDb.OleDbConnection
    Dim dbProdivder As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim inc As Integer
    Dim MaxRows As Integer

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

        dbProdivder = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = C:\Users\Owner\DVD System Rental.mdb"

        con.ConnectionString = dbProdivder & dbSource

        con.Open()
        sql = "SELECT * FROM Customer"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "DVD System Rental")

        con.Close()

        MaxRows = ds.Tables("DVD System Rental").Rows.Count
        inc = -1

    End Sub


    Private Sub NavigateRecords()

        TxtCustomerID.Text = ds.Tables("DVD System Rental").Rows(inc).Item(0)
        TxtCustomerName.Text = ds.Tables("DVD System Rental").Rows(inc).Item(1)
        TxtDOB.Text = ds.Tables("DVD System Rental").Rows(inc).Item(2)
        TxtAddress.Text = ds.Tables("DVD System Rental").Rows(inc).Item(3)
        TxtContact.Text = ds.Tables("DVD System Rental").Rows(inc).Item(4)

    End Sub

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

    Private Sub BtnPrevious_Click(sender As System.Object, 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(sender As System.Object, e As System.EventArgs) Handles BtnLast.Click
        If inc <> MaxRows - 1 Then
            inc = MaxRows - 1
            NavigateRecords()
        End If
    End Sub

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

    Private Sub BtnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles BtnUpdate.Click

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        ds.Tables("DVD System Rental").Rows(inc).Item(0) = TxtCustomerID.Text
        ds.Tables("DVD System Rental").Rows(inc).Item(1) = TxtCustomerName.Text
        ds.Tables("DVD System Rental").Rows(inc).Item(2) = TxtDOB.Text
        ds.Tables("DVD System Rental").Rows(inc).Item(3) = TxtAddress.Text
        ds.Tables("DVD System Rental").Rows(inc).Item(4) = TxtContact.Text

        da.Update(ds, "DVD System Rental") <------- Problem here

        MsgBox("Data updated")
    End Sub

    Private Sub BtnAdd_Click(sender As System.Object, e As System.EventArgs) Handles BtnAdd.Click
        BtnCommit.Enabled = True
        BtnAdd.Enabled = False
        BtnUpdate.Enabled = False
        BtnDelete.Enabled = False

        TxtCustomerID.Clear()
        TxtCustomerName.Clear()
        TxtDOB.Clear()
        TxtAddress.Clear()
        TxtContact.Clear()

    End Sub

    Private Sub BtnClear_Click(sender As System.Object, e As System.EventArgs) Handles BtnClear.Click
        BtnCommit.Enabled = False
        BtnAdd.Enabled = True
        BtnUpdate.Enabled = True
        BtnDelete.Enabled = True

        inc = 0
        NavigateRecords()
    End Sub

    Private Sub BtnCommit_Click(sender As System.Object, 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("DVD System Rental").NewRow()

            dsNewRow.Item(0) = TxtCustomerID.Text
            dsNewRow.Item(1) = TxtCustomerName.Text
            dsNewRow.Item(2) = TxtDOB.Text
            dsNewRow.Item(3) = TxtAddress.Text
            dsNewRow.Item(4) = TxtContact.Text

            ds.Tables("DVD System Rental").Rows.Add(dsNewRow)

            da.Update(ds, "DVD System Rental") <------- Problem here

            MsgBox("New Record added to the Database")

            BtnCommit.Enabled = False
            BtnAdd.Enabled = True
            BtnUpdate.Enabled = True
            BtnDelete.Enabled = True

        End If


    End Sub

    Private Sub BtnDelete_Click(sender As System.Object, e As System.EventArgs) Handles BtnDelete.Click
        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

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        ds.Tables("DVD System Rental").Rows(inc).Delete()
        MaxRows = MaxRows - 1

        inc = 0
        da.Update(ds, "DVD System Rental") <------- Problem here
        NavigateRecords()
    End Sub

Posted 16-Sep-14 3:56am

BeginnerCoding

Updated 16-Sep-14 4:50am

v3

Add a Solution

Comments

[no name] 16-Sep-14 9:59am

Edit your question and use code tags. I am not reading all that.

[no name] 16-Sep-14 10:01am

http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update(v=vs.110).aspx

[no name] 16-Sep-14 10:34am

Thank you. Now i don't see any bold so please paste the line here that the error appear. Thanks

BeginnerCoding 16-Sep-14 10:52am

The error always appear after this which i have indicated using <---- problem here

da.Update(ds, "DVD System Rental")

[no name] 16-Sep-14 12:08pm

If your form loads and the first thing you do; is hit BtnDelete, the integer is set to -1 on load. So in your button BtnDelete, that surely should cause you problems on this line: ds.Tables("DVD System Rental").Rows(inc).Delete()
As you are not passing any additional values to the BtnDelete subroutine to change inc value. What I really advise you to do is to step into your code using your debugger and see how your code functions from there.

BeginnerCoding 16-Sep-14 18:33pm

I get that part but i am still curious about my BtnUpdate as it worked before i added the da.Update(ds, "DVD System Rental") by using Commandbuilder but i can only save in the dataset but not the actual Database. So mind helping me check my BtnUpdate

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

TheHellGirl · Answer 1 · 2014-09-16T19:55:00

Try Using It in your update button:

VB

Try
           da = New OleDbDataAdapter("SELECT * FROM NamesList WHERE ID", conn)
           Dim cmd As New OleDb.OleDbCommand
           Dim Str As String


           Str = "update tablename set columnName="
           Str += """" & textbox1.text & """"
           Str += " where ID="
           Str += Label6.Text.Trim()
           cmd = New OleDbCommand(Str, conn)
           cmd.ExecuteNonQuery()

           ds.Clear()


           da.Fill(ds, "tablename")
           MsgBox("Updated Successfully...")


       Catch ex As Exception
           MsgBox(ex.Message & "," & ex.Source)
       End Try

Delete Button

VB

If MsgBox("Are you Sure you want to Delete this record?", MsgBoxStyle.YesNo + MsgBoxStyle.Question, "Delete") = vbYes Then
           Try
               Dim sql As String = "delete from TBLName where ID =" & DataGridView1.SelectedRows(0).Cells(0).Value & ";"
               Dim cmd As New OleDbCommand
               cmd.Connection = conn
               cmd.CommandText = sql
               cmd.ExecuteNonQuery()
               MsgBox("Successfully Deleted!", MsgBoxStyle.Question Or MsgBoxStyle.Information, "Information")


           Catch ex As Exception
               MessageBox.Show("Please Select First!")
           End Try

       Else
           MsgBox("Deleting Files is Cancelled!", MsgBoxStyle.Question Or MsgBoxStyle.Information, "Information")
       End If