Question Updating records

P

pirate

Member
Joined
Mar 12, 2013
Messages
10
Programming Experience
Beginner
Here is my code :

the program stops at da.Update(ds, "Vita") it says Syntax error in UPDATE statement.

VB.NET: 
Imports System.Data.OleDb


Public Class Form2


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


    Private Sub VitaBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
        Me.Validate()
        Me.VitaBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.VitaPlus_1DataSet)


    End Sub


    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'ProductsDataSet.Soup_etc' table. You can move, or remove it, as needed.
        Me.Soup_etcTableAdapter.Fill(Me.ProductsDataSet.Soup_etc)
        'TODO: This line of code loads data into the 'ProductsDataSet.Beauty_Products' table. You can move, or remove it, as needed.
        Me.Beauty_ProductsTableAdapter.Fill(Me.ProductsDataSet.Beauty_Products)
        'TODO: This line of code loads data into the 'ProductsDataSet.Beauty_Products' table. You can move, or remove it, as needed.
        Me.Beauty_ProductsTableAdapter.Fill(Me.ProductsDataSet.Beauty_Products)
        'TODO: This line of code loads data into the 'ProductsDataSet.Products' table. You can move, or remove it, as needed.
        Me.ProductsTableAdapter.Fill(Me.ProductsDataSet.Products)
        'TODO: This line of code loads data into the 'VitaPlus_1DataSet.Vita' table. You can move, or remove it, as needed.
        Me.VitaTableAdapter.Fill(Me.VitaPlus_1DataSet.Vita)








        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = C:\Users\pirate\Documents\Visual Studio 2012\Projects\FirstVitaPlusI\VitaPlus 1.mdb"




        con.ConnectionString = dbProvider & dbSource


        con.Open()
        sql = " SELECT *FROM Vita"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Vita")
        MsgBox("Database is now open")


        con.Close()


        MsgBox("Database is now Closed")


        Dim vita As New DataTable




        'ito ung code sa isa
        GroupBox4.Visible = True
        MaxRows = ds.Tables("Vita").Rows.Count
        inc = -1


        showitems()






    End Sub


    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        Me.VitaBindingSource.Filter = "First_Name= '" & Me.TextBox1.Text & "'"
    End Sub


 


    Private Sub Button6_Click(sender As Object, e As EventArgs)
        Form3.Show()




    End Sub


    Private Sub Button6_Click_1(sender As Object, e As EventArgs) Handles Button6.Click
        My.Computer.Audio.Play(My.Resources.Windows_User_Account_Control, AudioPlayMode.WaitToComplete)
        Form3.Show()


    End Sub






    Private Sub RadioButton1_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton1.CheckedChanged
        If RadioButton1.Checked = True Then
            DataGridView1.Visible = True
            Beauty_ProductsDataGridView.Visible = False
            Soup_etcDataGridView.Visible = False
        End If
    End Sub


    Private Sub RadioButton2_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton2.CheckedChanged
        If RadioButton2.Checked = True Then
            DataGridView1.Visible = False
            Beauty_ProductsDataGridView.Visible = True
            Soup_etcDataGridView.Visible = False


        End If
    End Sub


    Private Sub RadioButton3_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton3.CheckedChanged
        If RadioButton3.Checked = True Then
            DataGridView1.Visible = False
            Beauty_ProductsDataGridView.Visible = False
            Soup_etcDataGridView.Visible = True


        End If
    End Sub


    Private Sub webBrowser_NewWindow(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles WebBrowser1.NewWindow
        e.Cancel = True
    End Sub


    Private Sub Button4_Click(sender As Object, e As EventArgs)


        My.Computer.Audio.Play(My.Resources.Windows_User_Account_Control, AudioPlayMode.WaitToComplete)
        Me.Hide()
        Form4.Show()


    End Sub


    Private Sub ToolStripButton14_Click(sender As Object, e As EventArgs) Handles ToolStripButton14.Click
        Me.Validate()
        Me.VitaBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.VitaPlus_1DataSet)
    End Sub




    Private Sub mnuExit_Click(sender As Object, e As EventArgs) Handles mnuExit.Click
        Me.Close()


    End Sub


    Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
        Try
            TextBox2.Text = TextBox2.Text
            Soup_etcDataGridView.DataSource = Me.ProductsDataSet.Soup_etc.Select("Product like'" & TextBox2.Text & "'")
        Catch ex As System.Data.StrongTypingException
        Finally
        End Try


    End Sub






    Private Sub RadioButton4_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton4.CheckedChanged




        My.Computer.Audio.Play(My.Resources.Windows_User_Account_Control, AudioPlayMode.WaitToComplete)
        GroupBox1.Visible = True
        GroupBox2.Visible = False
        GroupBox3.Visible = False
        GroupBox4.Visible = True
        GroupBox6.Visible = False


    End Sub


    Private Sub RadioButton5_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton5.CheckedChanged
        My.Computer.Audio.Play(My.Resources.Windows_User_Account_Control, AudioPlayMode.WaitToComplete)
        GroupBox1.Visible = False
        GroupBox2.Visible = True
        DataGridView1.Visible = True
        GroupBox3.Visible = False
        GroupBox4.Visible = False
        GroupBox6.Visible = False


        Me.Dealers_NumberTextBox.ReadOnly = True
        Me.Nick_NameTextBox.ReadOnly = True
        Me.Last_NameTextBox.ReadOnly = True
        Me.First_NameTextBox.ReadOnly = True
        Me.Middle_NameTextBox.ReadOnly = True
        Me.NationalityTextBox.ReadOnly = True
        Me.AdressTextBox.ReadOnly = True
        Me.Civi_StatusTextBox.ReadOnly = True
        Me.SexTextBox.ReadOnly = True
        Me.NotesTextBox.ReadOnly = True
        Me.Cellular_Phone_NumberTextBox.ReadOnly = True
        Me.Telephone_NumberTextBox.ReadOnly = True
        Me.E_mail_addressTextBox.ReadOnly = True
    End Sub


    Private Sub RadioButton6_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton6.CheckedChanged
        My.Computer.Audio.Play(My.Resources.Windows_User_Account_Control, AudioPlayMode.WaitToComplete)
        WebBrowser1.Navigate("http://firstvitaplus.net/index.html")


        GroupBox1.Visible = False
        GroupBox2.Visible = False
        GroupBox3.Visible = True
        GroupBox4.Visible = False
        GroupBox6.Visible = False




        Me.Dealers_NumberTextBox.ReadOnly = True
        Me.Nick_NameTextBox.ReadOnly = True
        Me.Last_NameTextBox.ReadOnly = True
        Me.First_NameTextBox.ReadOnly = True
        Me.Middle_NameTextBox.ReadOnly = True
        Me.NationalityTextBox.ReadOnly = True
        Me.AdressTextBox.ReadOnly = True
        Me.Civi_StatusTextBox.ReadOnly = True
        Me.SexTextBox.ReadOnly = True
        Me.NotesTextBox.ReadOnly = True
        Me.Cellular_Phone_NumberTextBox.ReadOnly = True
        Me.Telephone_NumberTextBox.ReadOnly = True
        Me.E_mail_addressTextBox.ReadOnly = True
    End Sub


    Private Sub RadioButton8_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton8.CheckedChanged
        My.Computer.Audio.Play(My.Resources.Windows_User_Account_Control, AudioPlayMode.WaitToComplete)
        Me.Hide()
        Form4.Show()
    End Sub


    Private Sub RadioButton9_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton9.CheckedChanged


        GroupBox6.Show()










        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = C:\Users\pirate\Documents\Visual Studio 2012\Projects\FirstVitaPlusI\VitaPlus 1.mdb"




        con.ConnectionString = dbProvider & dbSource


        con.Open()
        sql = " SELECT *FROM Vita"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Vita")
        MsgBox("Database is now open")


        con.Close()


        MsgBox("Database is now Closed")


        Dim vita As New DataTable




    End Sub
    Private Sub showitems()
        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter


        con.Open()
        da = New OleDbDataAdapter("Select * from vita", con)
        da.Fill(dt)


        con.Close()


    End Sub


    Private Sub NavigateRecords()


        TextBox3.Text = ds.Tables("Vita").Rows(inc).Item(0)
        TextBox4.Text = ds.Tables("Vita").Rows(inc).Item(1)
        TextBox5.Text = ds.Tables("Vita").Rows(inc).Item(2)
        TextBox6.Text = ds.Tables("Vita").Rows(inc).Item(3)
        TextBox7.Text = ds.Tables("Vita").Rows(inc).Item(4)
        TextBox8.Text = ds.Tables("Vita").Rows(inc).Item(5)
        TextBox9.Text = ds.Tables("Vita").Rows(inc).Item(6)
        TextBox10.Text = ds.Tables("Vita").Rows(inc).Item(7)
        TextBox11.Text = ds.Tables("Vita").Rows(inc).Item(8)
        TextBox12.Text = ds.Tables("Vita").Rows(inc).Item(9)
        TextBox13.Text = ds.Tables("Vita").Rows(inc).Item(10)
        TextBox14.Text = ds.Tables("Vita").Rows(inc).Item(11)






    End Sub


    Private Sub GroupBox6_Enter(sender As Object, e As EventArgs) Handles GroupBox6.Enter
        MaxRows = ds.Tables("Vita").Rows.Count
        inc = -1
    End Sub


    Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click
        If inc <> MaxRows - 1 Then


            inc = inc + 1


            NavigateRecords()


        Else


            MsgBox("No More Rows")


        End If
    End Sub


    Private Sub Button2_Click_1(sender As Object, e As EventArgs) Handles Button2.Click
        If inc > 0 Then


            inc = inc - 1


            NavigateRecords()


        Else


            MsgBox("First Record")


        End If
    End Sub


    Private Sub Button3_Click_1(sender As Object, e As EventArgs) Handles Button3.Click
        If inc <> MaxRows - 1 Then


            inc = MaxRows - 1


            NavigateRecords()


        Else
            MsgBox("You're already in the last record")
        End If
    End Sub


    Private Sub Button4_Click_1(sender As Object, e As EventArgs) Handles Button4.Click
        If inc <> 0 Then


            inc = 0


            NavigateRecords()


        Else
            MsgBox("You're already in te fires record")
        End If


    End Sub


    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)


        ds.Tables("Vita").Rows(inc).Item(0) = TextBox3.Text
        ds.Tables("Vita").Rows(inc).Item(1) = TextBox4.Text
        ds.Tables("Vita").Rows(inc).Item(2) = TextBox3.Text
        ds.Tables("Vita").Rows(inc).Item(3) = TextBox4.Text
        ds.Tables("Vita").Rows(inc).Item(4) = TextBox3.Text
        ds.Tables("Vita").Rows(inc).Item(5) = TextBox4.Text
        ds.Tables("Vita").Rows(inc).Item(6) = TextBox3.Text
        ds.Tables("Vita").Rows(inc).Item(7) = TextBox4.Text
        ds.Tables("Vita").Rows(inc).Item(8) = TextBox3.Text
        ds.Tables("Vita").Rows(inc).Item(9) = TextBox4.Text
        ds.Tables("Vita").Rows(inc).Item(10) = TextBox3.Text
        'ds.Tables("Vita").Rows(inc).Item(11) = TextBox4.Text
        ds.Tables("Vita").Rows(inc).Item(12) = TextBox4.Text


        da.Update(ds, "Vita")>>>>>>>>the program stops here....it says Syntax error in UPDATE statement.


        MsgBox("Data updated"):err:
    End Sub
End Class
 
Last edited by a moderator:
Sort by date Sort by votes
Hi,

Sorry, but I am not downloading anything. If you are confused then I respectfully suggest that you have not fully understood the Tutorial you are working through.

I have just had a quick look at the tutorial since I have never actually gone through it myself, and the first thing that is talked about in the lesson,"Navigate a Database with VB .NET", is "MOVING" your variables:-

As quoted in the tutorial:-
At the moment, all our code is in the Button we added to the form. We're going to delete this button, so we need to move it out of there. The variable declarations can be moved right to the top of the coding window. That way, any button can see the variables. So move your variables declarations to the top, as in the image below (don't forget to add the Dim inc As Integer line):
Click to expand...

If you had fully understood what the tutorial was teaching you, you would not be in the position that you are in since, as I have said previously, you have cluttered your Form with numerous variables with the same name which is causing your coding to return unexpected errors.

The best suggestion I can give you now is to "Start Again" and keep doing it until it sinks in and you get it right.

Good Luck.

Cheers,

Ian
 
Upvote 0 Downvote
IanRyder said:
Hi,

Sorry, but I am not downloading anything. If you are confused then I respectfully suggest that you have not fully understood the Tutorial you are working through.

I have just had a quick look at the tutorial since I have never actually gone through it myself, and the first thing that is talked about in the lesson,"Navigate a Database with VB .NET", is "MOVING" your variables:-

As quoted in the tutorial:-


If you had fully understood what the tutorial was teaching you, you would not be in the position that you are in since, as I have said previously, you have cluttered your Form with numerous variables with the same name which is causing your coding to return unexpected errors.

The best suggestion I can give you now is to "Start Again" and keep doing it until it sinks in and you get it right.

Good Luck.

Cheers,

Ian
Click to expand...




Hi I followed what you said.I coded it in another form.It all works except the commit button. When I click the commit button nothing happens and there's is no error code that appears.

Here is my new code:
VB.NET: 
Public Class Form3





    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 Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load




        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = C:\Users\pirate\Documents\Visual Studio 2008\Projects\FirstVitaPlus\FirstVitaPlus.mdb"


        con.ConnectionString = dbProvider & dbSource


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






        con.Close()






        MaxRows = ds.Tables("FirstVitaPlus").Rows.Count
        inc = -1
    End Sub


    Private Sub NavigateRecords()








        txtDealer.Text = ds.Tables("FirstVitaPlus").Rows(inc).Item(0)
        txtFirstName.Text = ds.Tables("FirstVitaPlus").Rows(inc).Item(1)
        txtSurname.Text = ds.Tables("FirstVitaPlus").Rows(inc).Item(2)
        txtMiddle.Text = ds.Tables("FirstVitaPlus").Rows(inc).Item(3)
        txtCellphone1.Text = ds.Tables("FirstVitaPlus").Rows(inc).Item(4)
        txtCellphone2.Text = ds.Tables("FirstVitaPlus").Rows(inc).Item(5)
        txtTelephone.Text = ds.Tables("FirstVitaPlus").Rows(inc).Item(6)
        txtAddress.Text = ds.Tables("FirstVitaPlus").Rows(inc).Item(7)
        txtNationality.Text = ds.Tables("FirstVitaPlus").Rows(inc).Item(8)
        txtStatus.Text = ds.Tables("FirstVitaPlus").Rows(inc).Item(9)
        txtGender.Text = ds.Tables("FirstVitaPlus").Rows(inc).Item(10)
        txtEmail.Text = ds.Tables("FirstVitaPlus").Rows(inc).Item(11)
        txtNotes.Text = ds.Tables("FirstVitaPlus").Rows(inc).Item(12)






    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 Rows")


        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("FirstVitaPlus").Rows(inc).Item(0) = txtDealer.Text
        ds.Tables("FirstVitaPlus").Rows(inc).Item(1) = txtFirstName.Text


        ds.Tables("FirstVitaPlus").Rows(inc).Item(2) = txtSurname.Text
        ds.Tables("FirstVitaPlus").Rows(inc).Item(3) = txtMiddle.Text


        ds.Tables("FirstVitaPlus").Rows(inc).Item(4) = txtCellphone1.Text
        ds.Tables("FirstVitaPlus").Rows(inc).Item(5) = txtCellphone2.Text


        ds.Tables("FirstVitaPlus").Rows(inc).Item(6) = txtTelephone.Text
        ds.Tables("FirstVitaPlus").Rows(inc).Item(7) = txtAddress.Text


        ds.Tables("FirstVitaPlus").Rows(inc).Item(8) = txtNationality.Text
        ds.Tables("FirstVitaPlus").Rows(inc).Item(9) = txtStatus.Text


        ds.Tables("FirstVitaPlus").Rows(inc).Item(10) = txtGender.Text
        ds.Tables("FirstVitaPlus").Rows(inc).Item(11) = txtEmail.Text


        ds.Tables("FirstVitaPlus").Rows(inc).Item(12) = txtNotes.Text
        




        da.Update(ds, "FirstVitaPlus")


        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


        txtDealer.Clear()
        txtFirstName.Clear()
        txtSurname.Clear()
        txtMiddle.Clear()
        txtCellphone1.Clear()
        txtCellphone2.Clear()
        txtTelephone.Clear()
        txtAddress.Clear()
        txtNationality.Clear()
        txtStatus.Clear()
        txtGender.Clear()
        txtEmail.Clear()
        txtNotes.Clear()


    End Sub


    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.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("FirstVitaPlus").NewRow()


            dsNewRow.Item("Dealer's_Number") = txtDealer.Text
            dsNewRow.Item("FirstName") = txtFirstName.Text
            dsNewRow.Item("Surname") = txtSurname.Text
            dsNewRow.Item("MiddleName") = txtMiddle.Text
            dsNewRow.Item("Cellphone1") = txtCellphone1.Text
            dsNewRow.Item("Cellphone2") = txtCellphone2.Text
            dsNewRow.Item("Telephone") = txtTelephone.Text
            dsNewRow.Item("Address") = txtAddress.Text
            dsNewRow.Item("Nationality") = txtNationality.Text
            dsNewRow.Item("Status") = txtStatus.Text
            dsNewRow.Item("Gender") = txtGender.Text
            dsNewRow.Item("Email") = txtEmail.Text
            dsNewRow.Item("Notes") = txtNotes.Text




            ds.Tables("FirstVitaPlus").Rows.Add(dsNewRow)


            da.Update(ds, "FirstVitaPlus")


            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)


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


        inc = 0
        da.Update(ds, "FirstVitaPlus")
        NavigateRecords()
    End Sub
End Class
 
Upvote 0 Downvote
Hi,

I have not really looked at this, but I guess it is to do with this statement:-

VB.NET: 
If inc <> -1

If I remember right, you are using the "inc" variable to determine which Row you are on in the DataTable. In the case of adding a NEW row to the DataTable why are you interested in where you are in the DataTable? From what I can see just get rid of the IF statement.

Hope that helps.

Cheers,

Ian
 
Upvote 0 Downvote

Similar threads

manny cash
  • Question
Here is my code database and table I am so confuse
Replies
6
Views
708
jdelano
J
jcardana
  • Question
Answered Problem with DragDrop and double clicking
Replies
2
Views
152
jcardana
jcardana
N
  • Locked
  • Question
Question Estou criando um ComboBox Personalizado
Replies
1
Views
304
JohnH
JohnH
A
  • Question
Question Is this code thread-safe?
Replies
5
Views
967
jmcilhinney
jmcilhinney
1icax
  • Question
keypress when the form is hidden/ minimize?
Replies
4
Views
799
1icax
1icax

Share this page

Latest posts

Back
Top