Question Updating records

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:
Hi,

You are going about this all wrong. Firstly, you have created a Strongly Typed Dataset and then you basically ignore what you have created and start to create and use a Weakly Typed Dataset at runtime. This is just totally unnecessary.

In addition to this you have totally missed the principals of Binding information to your controls from a DataTable.

Due to these issues, I do not feel that it is worth spending any time trying to work out where your problem is when what you basically need to do is spend a lot more time studying how a Strongly Typed DataSet works and put those principals into practice.

To start with, have a look at these two tutorials:-

Add a Strongly Typed DataSet to a Project
How to Connect Database in Visual Basic .Net - YouTube


Binding Information to TextBox Controls:-
Data Binding to Textbox Controls in VB Net 2008 HD - YouTube

These should help you better understand how a Strongly Typed Dataset works and how to bind information to controls.

Hope that helps.

Cheers,

Ian

BTW, When posting code, please use the advanced button and encase your code in Code Tags for readability.
 
Hi,

Ah, since you are following the Tutorial in Home and Learn I will go along with this one. (Do also listen to the videos posted since these will give additional valuable information)

From what I can see you are creating your CommandBuilder based on the "da" DataAdapter which has been initialised in the Form Load event with this statement:-

VB.NET:
sql = " SELECT *FROM Vita"

This is an invalid SQL Select statement and should say:-

VB.NET:
sql = "SELECT * FROM Vita"

That should sort it. If not, then I suggest that you create a new Form and start the exercise again since, you have cluttered your Form 2 with numerous examples using the same variable names which become really confusing to debug when you get an error like this.

I would then do each exercise in a separate Form to keep things clean and easy to understand.

Hope that helps.

Cheers,

Ian
 
I followed what you said. I did the exercise in separate form..

now i got this error:
da.Update(ds, "Vita") >>>>>Syntax error in INSERT INTO statement.


thank you for your replies:smug:
 
Last edited:
Hi,

Based on what you had before this could be some sort of data conversion error, since you were assigning various fields to TextBox values earlier, but there is no way for me to really tell since you forgot to post the code you are now working with.

Post the code you are working with now and we should be able to guide you further.

Cheers,

Ian
 
Hi,

Based on what you had before this could be some sort of data conversion error, since you were assigning various fields to TextBox values earlier, but there is no way for me to really tell since you forgot to post the code you are now working with.

Post the code you are working with now and we should be able to guide you further.

Cheers,

Ian


Here's what I coded in the other form:

Imports System.Data.OleDb
Public Class Form7






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









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




con.Close()






Dim vita As New DataTable




'ito ung code sa isa


MaxRows = ds.Tables("Vita").Rows.Count
inc = -1


showitems()






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 Button2_Click(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 Button4_Click(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 Button3_Click(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 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 btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
btnCommit.Enabled = True
btnAdd.Enabled = False
btnUpdate.Enabled = False
btnDelete.Enabled = False


TextBox3.Clear()
TextBox4.Clear()
TextBox5.Clear()
TextBox6.Clear()
TextBox7.Clear()
TextBox8.Clear()
TextBox9.Clear()
TextBox10.Clear()
TextBox11.Clear()
TextBox12.Clear()
TextBox13.Clear()
TextBox14.Clear()
End Sub


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


Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow


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


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




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


da.Update(ds, "Vita")


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 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) = TextBox5.Text
ds.Tables("Vita").Rows(inc).Item(3) = TextBox6.Text
ds.Tables("Vita").Rows(inc).Item(4) = TextBox7.Text
ds.Tables("Vita").Rows(inc).Item(5) = TextBox8.Text
ds.Tables("Vita").Rows(inc).Item(6) = TextBox9.Text
ds.Tables("Vita").Rows(inc).Item(7) = TextBox10.Text
ds.Tables("Vita").Rows(inc).Item(8) = TextBox11.Text
ds.Tables("Vita").Rows(inc).Item(9) = TextBox12.Text
ds.Tables("Vita").Rows(inc).Item(10) = TextBox13.Text
ds.Tables("Vita").Rows(inc).Item(11) = TextBox14.Text
'ds.Tables("Vita").Rows(inc).Item(10) = TextBox13.Text






da.Update(ds, "Vita")


MsgBox("Data updated")
End Sub


Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
btnCommit.Enabled = False
btnAdd.Enabled = True
btnUpdate.Enabled = True
btnDelete.Enabled = True


inc = 0
NavigateRecords()
End Sub


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


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


inc = 0
da.Update(ds, "Vita")>>>>>>"error here"
NavigateRecords()
End Sub
End Class


Syntax error (missing operator) in query expression '(((? = 1 AND Nick Name IS NULL) OR (Nick Name = ?)) AND (Dealers_Number = ?) AND ((? = 1 AND Last_Name IS NULL) OR (Last_Name = ?)) AND ((? = 1 AND First_Name IS NULL) OR (First_Name = ?)) AND ((? = 1 AND Middle_Name IS NULL) OR (Middle_Name = ?)) AND ((?'.


i'm so sorry if i wasted your time but i'm new in using vb.net.....
thank's man :smug:
 
Last edited:
Hi,

Sorry, but I am not going to try and read all that again. Please limit this to what you are working on and "put your CODE in code tags, as I have already explained and as JohnH has previously sorted" and then we will have a look at this for you.

Cheers,

Ian
 
Hi,

Sorry, but I am not going to try and read all that again. Please limit this to what you are working on and "put your CODE in code tags, as I have already explained and as JohnH has previously sorted" and then we will have a look at this for you.

Cheers,

Ian

oh i'm sorry here's the code:
VB.NET:
Imports System.Data.OleDbPublic Class Form7






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








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




        con.Close()






        Dim vita As New DataTable




        'ito ung code sa isa


        MaxRows = ds.Tables("Vita").Rows.Count
        inc = -1


        showitems()






    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 Button2_Click(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 Button4_Click(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 Button3_Click(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 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 btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        btnCommit.Enabled = True
        btnAdd.Enabled = False
        btnUpdate.Enabled = False
        btnDelete.Enabled = False


        TextBox3.Clear()
        TextBox4.Clear()
        TextBox5.Clear()
        TextBox6.Clear()
        TextBox7.Clear()
        TextBox8.Clear()
        TextBox9.Clear()
        TextBox10.Clear()
        TextBox11.Clear()
        TextBox12.Clear()
        TextBox13.Clear()
        TextBox14.Clear()
    End Sub


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


            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsNewRow As DataRow


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


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




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


            da.Update(ds, "Vita")


            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 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) = TextBox5.Text
        ds.Tables("Vita").Rows(inc).Item(3) = TextBox6.Text
        ds.Tables("Vita").Rows(inc).Item(4) = TextBox7.Text
        ds.Tables("Vita").Rows(inc).Item(5) = TextBox8.Text
        ds.Tables("Vita").Rows(inc).Item(6) = TextBox9.Text
        ds.Tables("Vita").Rows(inc).Item(7) = TextBox10.Text
        ds.Tables("Vita").Rows(inc).Item(8) = TextBox11.Text
        ds.Tables("Vita").Rows(inc).Item(9) = TextBox12.Text
        ds.Tables("Vita").Rows(inc).Item(10) = TextBox13.Text
        ds.Tables("Vita").Rows(inc).Item(11) = TextBox14.Text
        'ds.Tables("Vita").Rows(inc).Item(10) = TextBox13.Text






        da.Update(ds, "Vita")


        MsgBox("Data updated")
    End Sub


    Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
        btnCommit.Enabled = False
        btnAdd.Enabled = True
        btnUpdate.Enabled = True
        btnDelete.Enabled = True


        inc = 0
        NavigateRecords()
    End Sub


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


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


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

On the basis that you now get an error on the Insert statement, I have two comments for you:-

1) Your SQL Select statement is still invalid, being:-

VB.NET:
sql = " SELECT *FROM Vita"

2) If that does not fix things then you need to get a better understanding of what the error is that you are getting. Try adding a Try/Catch block to your btnCommit button to catch the error coming from the Database. i.e:-

VB.NET:
If inc <> -1 Then
  Dim cb As New OleDb.OleDbCommandBuilder(da)
  Dim dsNewRow As DataRow
 
  Try
    dsNewRow = ds.Tables("Vita").NewRow()
    dsNewRow.Item(0) = TextBox3.Text
    dsNewRow.Item(1) = TextBox4.Text
    dsNewRow.Item(2) = TextBox5.Text
    dsNewRow.Item(3) = TextBox6.Text
    dsNewRow.Item(4) = TextBox7.Text
    dsNewRow.Item(5) = TextBox8.Text
    dsNewRow.Item(6) = TextBox9.Text
    dsNewRow.Item(7) = TextBox10.Text
    dsNewRow.Item(8) = TextBox11.Text
    dsNewRow.Item(9) = TextBox12.Text
    dsNewRow.Item(10) = TextBox13.Text
    dsNewRow.Item(11) = TextBox14.Text
 
    ds.Tables("Vita").Rows.Add(dsNewRow)
    da.Update(ds, "Vita")
  Catch dbError As OleDbException
    MsgBox(dbError.Message)
  End Try
End If

Give that a go and I hope it gets you closer to what your issues are.

Cheers,

Ian
 
Hi,

On the basis that you now get an error on the Insert statement, I have two comments for you:-

1) Your SQL Select statement is still invalid, being:-

VB.NET:
sql = " SELECT *FROM Vita"

2) If that does not fix things then you need to get a better understanding of what the error is that you are getting. Try adding a Try/Catch block to your btnCommit button to catch the error coming from the Database. i.e:-

VB.NET:
If inc <> -1 Then
  Dim cb As New OleDb.OleDbCommandBuilder(da)
  Dim dsNewRow As DataRow
 
  Try
    dsNewRow = ds.Tables("Vita").NewRow()
    dsNewRow.Item(0) = TextBox3.Text
    dsNewRow.Item(1) = TextBox4.Text
    dsNewRow.Item(2) = TextBox5.Text
    dsNewRow.Item(3) = TextBox6.Text
    dsNewRow.Item(4) = TextBox7.Text
    dsNewRow.Item(5) = TextBox8.Text
    dsNewRow.Item(6) = TextBox9.Text
    dsNewRow.Item(7) = TextBox10.Text
    dsNewRow.Item(8) = TextBox11.Text
    dsNewRow.Item(9) = TextBox12.Text
    dsNewRow.Item(10) = TextBox13.Text
    dsNewRow.Item(11) = TextBox14.Text
 
    ds.Tables("Vita").Rows.Add(dsNewRow)
    da.Update(ds, "Vita")
  Catch dbError As OleDbException
    MsgBox(dbError.Message)
  End Try
End If

Give that a go and I hope it gets you closer to what your issues are.

Cheers,

Ian


Hi, I created new form and followed another tutorial.I combined the two tutorials. The add, delete and save now works but the problem now is the navigation button...
the problem appears in this lines of code:
MaxRows = ds.Tables("Vita").Rows.Count
inc = -1

it says: Object reference not set to an instance of an object.




VB.NET:
Imports System.Data.OleDbPublic Class Form9




    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




    Dim con As New OleDbConnection
    Private Sub Form8_Load(sender As Object, e As EventArgs) Handles MyBase.Load
 
        con.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=C:\Users\pirate\Documents\Visual Studio 2012\Projects\FirstVitaPlusI\db1.mdb"
        con.Open()




        'show the data from here up to....
        Dim ds As New DataSet
        Dim dt As New DataTable
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter


        da = New OleDbDataAdapter
        da = New OleDbDataAdapter("select * from Vita", con)
        da.Fill(dt)


        DataGridView1.DataSource = dt.DefaultView


        con.Close()
        '.......here
        MaxRows = ds.Tables("Vita").Rows.Count
        inc = -1
        showitems()
     


    End Sub


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


        con.Open()


        da = New OleDbDataAdapter
        da = New OleDbDataAdapter("select * from Vita", con)
        da.Fill(dt)




        DataGridView1.DataSource = dt.DefaultView


        con.Close()


        'txtNick.Text = dt.Rows(0).Item(0)
        'txtDealer.Text = dt.Rows(0).Item(1)
    End Sub








    Private Sub btnadd_Click(sender As Object, e As EventArgs) Handles btnadd.Click
        txtNick.Text = ""
        txtDealer.Clear()


    End Sub


    Private Sub btnsave_Click(sender As Object, e As EventArgs) Handles btnsave.Click
        con.Open()


        Dim ds As New DataSet
        Dim dt As New DataTable
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter




        da = New OleDbDataAdapter
        da = New OleDbDataAdapter("select * from Vita", con)


        da.Fill(dt)


        Dim newrow As DataRow = dt.NewRow
        With newrow
            .Item(0) = txtNick.Text
            .Item(1) = txtDealer.Text


        End With


        dt.Rows.Add(newrow)


        Dim cb As New OleDbCommandBuilder(da)
        da.Update(dt)


        con.Close()


        showitems()


    End Sub


    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged


        Dim ds As New DataSet
        Dim dt As New DataTable
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter




        da = New OleDbDataAdapter
        da = New OleDbDataAdapter("select * from Vita where NickName like '%" & TextBox1.Text & "%'", con)
        da.Fill(dt)


        DataGridView1.DataSource = dt.DefaultView


        con.Close()


    End Sub


    Private Sub btndelete_Click(sender As Object, e As EventArgs) Handles btndelete.Click
        Dim ds As New DataSet
        Dim dt As New DataTable
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter


        con.Open()


        da = New OleDbDataAdapter
        da = New OleDbDataAdapter("select * from Vita where NickName like '%" & TextBox1.Text & "%'", con)
        da.Fill(dt)


        dt.Rows(0).BeginEdit()
        dt.Rows(0).Delete()
        dt.Rows(0).EndEdit()




        Dim cb As New OleDbCommandBuilder(da)
        da.Update(dt)


        DataGridView1.DataSource = dt.DefaultView


        con.Close()


    End Sub






    Private Sub NavigateRecords()


        txtNick.Text = ds.Tables("Vita").Rows(inc).Item(0)
        txtDealer.Text = ds.Tables("Vita").Rows(inc).Item(1)








    End Sub


    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click>>>>this is for previous button
        If inc > 0 Then


            inc = inc - 1


            NavigateRecords()


        Else


            MsgBox("First Record")


        End If
    End Sub


    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click>>>>this is for first button
        If inc <> 0 Then


            inc = 0


            NavigateRecords()


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


    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click>>>this is for last button
        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(sender As Object, e As EventArgs) Handles Button4.Click>>>>this is for next button
        If inc <> MaxRows - 1 Then


            inc = inc + 1


            NavigateRecords()


        Else


            MsgBox("No More Rows")


        End If
    End Sub
End Class
 
Hi,

VB.NET:
Dim ds As New DataSet
Dim dt As New DataTable
ds.Tables.Add(dt)
Dim da As New OleDbDataAdapter
 
da = New OleDbDataAdapter
da = New OleDbDataAdapter("select * from Vita", con)
da.Fill(dt)
 
DataGridView1.DataSource = dt.DefaultView
 
con.Close()
'.......here
MaxRows = ds.Tables("Vita").Rows.Count

You are getting this error since you DO NOT have a table named Vita in your Dataset. What you are currently doing with the above code is:-

1) Create a Dataset
2) Create a Table
3) Add the UN-NAMED table to the DataSet
4) Create a DataAdapter
5) Fill the UN-NAMED DataTable using the DataAdapter
6) Assign the DataSource of the DataGridView
7) Try and get the Rows of a Table called Vita, which does not exist, in the DataSet. Hence the error!

So, to fix this you would give your table a Name when adding it to the Dataset. i.e:-

VB.NET:
Dim ds As New DataSet
Dim da As New OleDbDataAdapter("select * from Vita", con)
 
da.Fill(ds, "Vita")
DataGridView1.DataSource = ds.Tables(0).DefaultView
 
MaxRows = ds.Tables("Vita").Rows.Count

Hope that helps.

Cheers,

Ian
 
Hi,

VB.NET:
Dim ds As New DataSet
Dim dt As New DataTable
ds.Tables.Add(dt)
Dim da As New OleDbDataAdapter
 
da = New OleDbDataAdapter
da = New OleDbDataAdapter("select * from Vita", con)
da.Fill(dt)
 
DataGridView1.DataSource = dt.DefaultView
 
con.Close()
'.......here
MaxRows = ds.Tables("Vita").Rows.Count

You are getting this error since you DO NOT have a table named Vita in your Dataset. What you are currently doing with the above code is:-

1) Create a Dataset
2) Create a Table
3) Add the UN-NAMED table to the DataSet
4) Create a DataAdapter
5) Fill the UN-NAMED DataTable using the DataAdapter
6) Assign the DataSource of the DataGridView
7) Try and get the Rows of a Table called Vita, which does not exist, in the DataSet. Hence the error!

So, to fix this you would give your table a Name when adding it to the Dataset. i.e:-

VB.NET:
Dim ds As New DataSet
Dim da As New OleDbDataAdapter("select * from Vita", con)
 
da.Fill(ds, "Vita")
DataGridView1.DataSource = ds.Tables(0).DefaultView
 
MaxRows = ds.Tables("Vita").Rows.Count

Hope that helps.

Cheers,

Ian


hi the add, edit and save button is now working.Thank you for your help about that but now I have an error in navigation buttons
here is the part where I get the error
VB.NET:
Private Sub NavigateRecords()


        txtNick.Text = ds.Tables("Vita").Rows(inc).Item(0)
        txtDealer.Text = ds.Tables("Vita").Rows(inc).Item(1)








    End Sub

error: Object reference not set to an instance of an object.
 
Hi,

The Navigate Records routine would be sorted if you followed my last post and your SQL query actually returned records. As it is, all you have done is re-post your issue as though you have totally ignored what I have said.

So, what did you do with my last comment? Did you try this and did it fix the issue you were having? If that is sorted are you sure records have been returned from the Fill method of the DataAdapter.

If you are still having trouble then rather than giving "titbits" of information, please expand in detail what you have now done and explain exactly where in the code you are now getting errors?

Cheers,

Ian
 
Also, I forgot to mention. Get rid of all the:-

VB.NET:
Dim ds As New DataSet

Declarations in your various Subs. You should only be working with the ONE Dataset in your project which is declared at the Class level.

Cheers,

Ian
 
Back
Top