Link to home
Start Free TrialLog in
Avatar of jboehlen
jboehlen

asked on

can't add new record using ado.net

I'm a beginner programmer and am trying to build a customer database. I want to choose a customer name from a listbox, which will then display their info in  bound textboxes. I am using vb.net, ado.net, Access, data adapter and a dataset. My problem  is I cannot add a new record to enter a new customer from my vb program to populate the database. I want my client to be able to add new customers from my Win form, and not have to enter through the Access end of it. As of right now my code will not clear the form, nor add a new record, but rather overwrites the very first record. I've been stuck on this for a month now, asked in other forums but never got a response, and I've searched the web to no avail. I'm at my wits end with this problem. Can someone please tell me what I'm doing wrong? I have included my code. Please bypass the garbage as I was testing various methods. Also, I know there are other "glitches" within my application but I need to solve this major adding problem first before I tackle the other smaller ones. Any help would be greatly appreciated. Since I'm a new member I can't offer many points (didn't even know what those were about) but please note this is a huge obstacle for me right now and I can't proceed until I figure this out. Thanks!

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add the date upon loading
        mdatCurrent = System.DateTime.Today 'current date
        lblDate1.Text = Today.ToShortDateString 'displays current date in the date label
odbdaCustomers.Fill(DsCustomers1)

        'Bind customer list box
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.Name")
        lstCustomer.DataBindings.Add(pbndTemp)


        'Bind individual field (radio button selection)
        pbndTemp = New Binding("Checked", DsCustomers1, "tblCustomers.Individual")
        radIndividual.DataBindings.Add(pbndTemp)

        'Bind business field (radio button selection)
        pbndTemp = New Binding("Checked", DsCustomers1, "tblCustomers.Business")
        radBusiness.DataBindings.Add(pbndTemp)

        'Bind the customer ID field
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.CustomerID")
        lblCustomerNum.DataBindings.Add(pbndTemp)

        'Bind company name field
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.CompanyName")
        txtCompany.DataBindings.Add(pbndTemp)

        'Bind company contact name field
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.ContactName")
        txtContact.DataBindings.Add(pbndTemp)

        'Bind customer first name field
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.FName")
        txtFName.DataBindings.Add(pbndTemp)

        'Bind customer last name field
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.LName")
        txtLName.DataBindings.Add(pbndTemp)

        'Bind Customer first and last name as alias "Name"
        'pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.FName, tblCustomers.LName AS tblCustomers.Name")
        ' lstCustomers.DataBindings.Add(pbndTemp)

        'Bind customer address name field
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.Address")
        txtAddress.DataBindings.Add(pbndTemp)

        'Bind customer city field
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.City")
        txtCity.DataBindings.Add(pbndTemp)

        'Bind customer state field
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.State")
        cboState.DataBindings.Add(pbndTemp)

        'Bind customer zip code field
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.ZipCode")
        txtZip.DataBindings.Add(pbndTemp)

        'Bind customer first telephone number field
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.Phone1")
        txtPhone1.DataBindings.Add(pbndTemp)

        'Bind customer second telephone number field
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.Phone2")
        txtPhone2.DataBindings.Add(pbndTemp)

        'Bind customer fax number field
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.Fax")
        txtFax.DataBindings.Add(pbndTemp)

        'Bind customer email address field
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.Email")
        txtEmail.DataBindings.Add(pbndTemp)

        'Bind tax exempt field
        pbndTemp = New Binding("Checked", DsCustomers1, "tblCustomers.TaxExempt")
        chkTaxExempt.DataBindings.Add(pbndTemp)

        'Bind tax exempt ID field
        pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.TaxExemptID")
        txtTaxExemptID.DataBindings.Add(pbndTemp)    
    End Sub

Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        'Moves to the first record in the customer table
        Me.BindingContext(DsCustomers1, "tblCustomers").Position = 0
    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        'Moves to the previous record in the customer table
        Me.BindingContext(DsCustomers1, "tblCustomers").Position -= 1
    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        'Moves to the next record in the customer table
        Me.BindingContext(DsCustomers1, "tblCustomers").Position += 1
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        'Moves to the last record in the customer table
        Me.BindingContext(DsCustomers1, "tblCustomers").Position = Me.DsCustomers1.tblCustomers.Rows.Count - 1
    End Sub

Private Sub btnAddCust_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddCust.Click
       
        'add a new record to the customer database
        Me.BindingContext(DsCustomers1, "tblCustomers").AddNew()

pdsInsertedRows = DsCustomers1.GetChanges(DataRowState.Added)
End Sub

Private Sub btnCustomerSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCustomerSave.Click
        'allows records to be added or changed and saved back to the database
        Me.BindingContext(DsCustomers1, "tblCustomers").EndCurrentEdit()

        'method returns a Dataset containing the record or records that were added or changed
        pdsInsertedRows = DsCustomers1.GetChanges(DataRowState.Added)
        pdsModifiedRows = DsCustomers1.GetChanges(DataRowState.Modified)

        'tests whether a row exists in the dataset.
        If Not pdsInsertedRows Is Nothing Then
            odbdaCustomers.Update(pdsInsertedRows)
        End If
        If Not pdsModifiedRows Is Nothing Then
            odbdaCustomers.Update(pdsModifiedRows)
        End If
        'advise the dataset changes have been recorded
        DsCustomers1.AcceptChanges()
        lstCustomer.Enabled = True
        'display message box indicating changes have been saved
        MsgBox("Changes to the record have been saved in the database.", MsgBoxStyle.Information)

    End Sub
End Class
Avatar of jboehlen
jboehlen

ASKER

Oops, I didn't realize that I had unlimited points. In that case, this is definitely worth the additional points as I am really stuck on this part of my app.  :)
How are you building your Adapter object?
Specifically does your Adapter have the appropriate InsertCommand object set?

'from the example i have created
Me.OleDbDataAdapter1.InsertCommand = Me.OleDbInsertCommand1  
Is this what you are referring to? This was automatically generated for me.....

 Me.odbdaCustomers.InsertCommand = Me.OleDbInsertCommand1
Did you try enclosing your Save method within a try..catch block to see if any exceptions are being thrown?

The reason I ask is: I was able to use most of the code you provided with my own test database and Dataset etc and had no problems clearing the form or adding new records. This leads me to believe that there may be a problem with OleDbCommand object or the underlying database table.
Whats the error that is giving you?
Error:

An unhandled exception of type 'System.Data.NoNullAllowedException' occurred in system.data.dll

Additional information: Column 'CustomerID' does not allow nulls.

In:
 Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        If ValidData() Then
            bmbCustomers.EndCurrentEdit()
            If bNewRow Then
                cboCustomers.SelectedIndex = bmbCustomers.Count - 1
                bNewRow = False
            End If
        End If
        Me.SetMaintenanceButtons(True)
        btnUpdateDB.Enabled = True
        cboCustomers.Focus()
    End Sub

My problem seems to be that it is not going out and grabbing the KeyValue from my SystemDefaults table for the new autogenerated CustomerID number. It enters 0 into the box, which explains the null error. My form now clears, and I believe it would save if I can get past this null value error.

As for the autonumbers in Access: I tried using that but it's more of a problem than trying to make my own numbering system. I could see where that would work if I was dealing with only 1 table but I have 10 tables, all related, and not necessarily having the same primary key for each table. If I made CustomerID an autonumber in one, I cant' tie it to another table where CustomerID is not an autonumber. Very big headache there, and further away from making the app work than struggling with making my own autonumbers. But I do have that information printed for future reference and I thank everyone who helped me with that part of it.
ASKER CERTIFIED SOLUTION
Avatar of gdexter
gdexter

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you, I didn't know that. I haven't worked with 'real life' Access tables before, strictly a school textbook setting. I will try your suggestion and report back with the results.
Do I need to call that shared routine in somewhere else? I added your code, bound the textbox to the Customer table. The autonumber shows up as the ID in the existing records, I can click on add, the form clears but no number shows up in the ID box. Am I misunderstanding the logic of it? When I try to enter a new customer the "null customerID" error message still shows up. I'm assuming because I'm not telling it to get that part of the code somewhere. I apologize for the stupidity of this question but my mind is boggled at the moment with trying so many things and I can't think straight anymore.
OK lets back up here.

What I am trying to tell you is that you should NEVER ask for the ID (Primary Key) before the row is committed to the database it is a very bad idea. The Event I illustrated fires when a row is committed to the access table after you hit SAVE.

If you must have a Sequenced Number than use that as additonal column to the table and NOT as the Primary Key because if you dont you are going to get yourself in trouble down the road. Trust ME on that one.

1. Make your column CustomerID an Autonumber Primary Key in tblCustomers
2. Add a new column in tblCustomers: Number datatype,  and call it SequenceID (this column will be the one you add the  
    sequenced number to)
3. Bind your text field to SequenceID

Do not relate any other tables to the SequenceID but rather relate them to the CustomerID (Autonumber).
If you need to get the sequence number than you customerID will be the key to that number in any table that is related.

Ok, backed up and took notes. Back to the drawing board.  I will try that next. Thank you for your help.
I think I finally am understanding what you are saying. I will be taking this home with me to work on this afternoon. I will post my progress earlier tonight. I really appreciate everyone's patience and help.
I'm ALMOST there!!! Yahoo!  I actually have my small test program working!! But when I copied the test code over to my bigger application, it bombed on me. I get that same "customerID null" error. It will not automatically add the next autonumber from the table. For the life of me I cannot figure out why it will not work except that in my test app I am using a combo box to select customers, and in my bigger app I'm using a listbox. The listbox still uses SelectedIndexChanged, just like the combo right? I even stepped through each app side by side to compare values in the output window and each were the same. Does a listbox work any differently than a combo box? I should just be able to substitute cbo with lst right? or wrong?
I'VE GOT IT WORKING!!! I've tested the add, delete and update buttons and all work as they should!!! I had to rebuild a new app from scratch, copied my testing code over, and then it worked. I have no idea why as I mentioned above, my other real app wouldn't work. But hey, I got one running so I'm happy:)

Thank you SO much for everyone's help. Now I know how to work with autonumbers!!!!