none
Adding a new row does nothing RRS feed

  • Question

  • Hello everyone, 

    I'm using VB 2010 Express to browse and modify a Database saved in Access 2000 .mdb format. 

    I can browse the database, delete and update records in the table. But when I try to add a new record to the database at the push of a button, the program simply doesn't work. It doesn't give an error or anything. It just doesn't do anything. I'm using DataAdapter and DataSet of name ( RegisteredCarsDataSet )

    The Database is called RegisteredCars.

    This is the code I'm using:

    Public Class Form2

    Dim MaxRows As Integer
        Dim inc As Integer
        Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim dbProvider As String
        Dim dbSource As String
        Dim sql As String
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow

    at Form Loader:

            

     dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"

            dbSource = " Data Source = C:\Users\<File Path>
            con.ConnectionString = dbProvider & dbSource

            con.Open()

            sql = "SELECT * FROM Details"   ''sql code to open table
            da = New OleDb.OleDbDataAdapter(sql, con)    
            da.Fill(ds, "RegisteredCarsDataSet")

            con.Close() 

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

    To add record :

    At the push of a button:

                                                   

     If inc <> -1 Then


                Dim cb As New OleDb.OleDbCommandBuilder(da)


                cb.QuotePrefix = "["

                cb.QuoteSuffix = "]"

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

                dsNewRow.Item("FirstN") = TextBox1.Text
                dsNewRow.Item("Surname") = TextBox2.Text
                dsNewRow.Item("MobileNumber") = TextBox3.Text
                dsNewRow.Item("LicensePlateNumber") = TextBox4.Text
                dsNewRow.Item("LicensePlateType") = ComboBox1.Text
                dsNewRow.Item("Emirate") = ComboBox2.Text

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


                da.Update(ds, "RegisteredCarsDataSet")

               'I'm pretty sure these lines don't get executed because a message doesn't get displayed

                MsgBox("New Record added to the Database")

                Button4.Enabled = False
                Button3.Enabled = True
                Button5.Enabled = True
                Button10.Enabled = True

            End If

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


        End Sub

     

    • Moved by Ego Jiang Thursday, March 21, 2013 6:55 AM
    Wednesday, March 20, 2013 7:05 PM

Answers

  • Yes than first remove that part and see what happens. Bring back your program to the essentials. 

    Most programmers do that by temporally put a comment before the code. 

    Keep in mind that if it is running and there are still so called outcommented lines in the code, it is seen in this age with all kind of source safe options as laziness of the programmer.


    Success
    Cor

    Friday, March 22, 2013 9:15 AM
  • Use the debugger and put a breakpoint on the if ... see what the value of inc is then. Sorry, I should have mentioned that in my previous reply, but I guess I thought that was obvious.

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Friday, March 22, 2013 2:28 PM

All replies

  • Hello,

    Here is an example where all data objects where done via a Data Wizard which works by adding a new DataRow slightly different than the syntax you use yet should not make a difference. The caveat is the row will not displayed in say a DataGridView as the BindingSource is not updated. If you were to add a row in the DataGridView you would see the new row. If the setup used a BindingNavigator then when pressing save the data would be saved to the backend database yet if you have a primary key displayed it will show up as a negative value until you open/close the app or reload the DataGridView or use a completely different method that you take full control of adding the new record via OleDb where you would do the insert than using the same connection select @Identity to get the new key via ExecuteScalar method of OleDbCommand.

    So keeping with what you have currently you might simply try reloading the data.

    Dim ds As New DataSet
    Using cn As New OleDb.OleDbConnection With
        {
            .ConnectionString = My.Settings.Database1ConnectionString
        }
        cn.Open()
        Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM Customer", cn)
        da.Fill(ds, "Customer")
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        da.InsertCommand = cb.GetInsertCommand
        ds.Tables(0).Rows.Add(New Object() {Nothing, "", "AAAAA"})
        da.Update(ds, "Customer")
    End Using

    The INSERT command built looks like the following (w/o the indentation)

    INSERT INTO Customer 
    (
       CustomerID, 
       CompanyName, 
       ContactName, 
       ContactTitle, 
       Address, 
       City, 
       PostalCode, 
       Country, 
       Phone, 
       Fax, 
       Region
    ) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)


    Enjoy life

    Wednesday, March 20, 2013 8:32 PM
  • Kevin,

    This one is not needed, somehow it came on Internet and some use it. 

    But the commandbuilder with the Dataadapter works dynamically, that code does more hurt than solve.

    da.InsertCommand = cb.GetInsertCommand


    Success
    Cor

    Thursday, March 21, 2013 7:56 AM
  • Understood, never been much for this style of connecting to databases.

    Enjoy life

    Thursday, March 21, 2013 11:12 AM
  • You guys didn't really read the OP's post too well. He's got an If inc <> -1 ... and in that If, he's got the code to add a new row and update it to the database. He's also got a MessageBox within that If. He says it never displays the MessageBox.

    My guess is that inc is -1 and that If is never executed! The question then becomes why is inc -1? The only code I see is where you've set inc = -1 ... never see it added to anywhere.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Thursday, March 21, 2013 2:32 PM
  • Bonnie,

    To be honest, mostly I don't read much messages until an OP replies on a question or I should see that an answer is incomplete or does not answer in any way the question of the OP. 

    Of course you are right, but that should every programmer first do. 

    Remove all humbug from his code which can cause the problem.

    (I know many newbies think that it is better to leave all code and add even more humbug).

    :-)


    Success
    Cor

    Thursday, March 21, 2013 2:58 PM
  • Yes you are correct, I saw it but paid it no mind yet should had

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.


    Thursday, March 21, 2013 3:02 PM
  • We'll just have to sit back now and wait until the OP replies.

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Thursday, March 21, 2013 3:41 PM
  • I'm using inc to navigate through the database, it is the row number. It is not really used in adding a new row, it is just there to make sure that the user has navigated the database , and thus the increment inc is not equal to -1 ( where the counter starts). I just put it there because it used to throw an error for some reason. But that was before I I adjusted the code further. I guess I can remove it and try.   
    Friday, March 22, 2013 6:41 AM
  • Yes than first remove that part and see what happens. Bring back your program to the essentials. 

    Most programmers do that by temporally put a comment before the code. 

    Keep in mind that if it is running and there are still so called outcommented lines in the code, it is seen in this age with all kind of source safe options as laziness of the programmer.


    Success
    Cor

    Friday, March 22, 2013 9:15 AM
  • Use the debugger and put a breakpoint on the if ... see what the value of inc is then. Sorry, I should have mentioned that in my previous reply, but I guess I thought that was obvious.

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Friday, March 22, 2013 2:28 PM
  • Thanks a lot. it seems these was a slight problem with inc where it wasn't counting the rows as i intended it to. I removed the entire If statement and that took care of it. I adjusted it and now it is working perefctly.

    Thank you :)

     
    Sunday, March 24, 2013 3:47 PM
  • Glad we could help!


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, March 24, 2013 4:31 PM