Solved

can't add new record using ado.net

Posted on 2004-08-26
14
375 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:jboehlen
  • 9
  • 4
14 Comments
 

Author Comment

by:jboehlen
ID: 11908915
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.  :)
0
 
LVL 4

Expert Comment

by:gdexter
ID: 11915392
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  
0
 

Author Comment

by:jboehlen
ID: 11917300
Is this what you are referring to? This was automatically generated for me.....

 Me.odbdaCustomers.InsertCommand = Me.OleDbInsertCommand1
0
 
LVL 4

Expert Comment

by:gdexter
ID: 11934560
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.
0
 
LVL 1

Expert Comment

by:demon090977
ID: 11945612
Whats the error that is giving you?
0
 

Author Comment

by:jboehlen
ID: 11953756
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.
0
 
LVL 4

Accepted Solution

by:
gdexter earned 500 total points
ID: 11954470
->If I made CustomerID an autonumber in one, I cant' tie it to another table where CustomerID is not an autonumber.

Yes you can, the CustomerID should be a foreign Key column for all related tables.

You can use the Row_Updated Event of the DataAdpater to retrieve the generated ID from Access.

 Private Shared Sub odbdaCustomers_RowUpdated(ByVal sender As Object, ByVal e As  
                                                                          System.Data.OleDb.OleDbRowUpdatedEventArgs)
                                                                          Handles odbdaCustomers.RowUpdated

        If (e.StatementType = StatementType.Insert) Then
            Dim cmd As New OleDbCommand("SELECT @@IDENTITY", e.Command.Connection)
           
            '------------------------------------------------------------------------------------------------------------------------
            'this statement will fill the CustomerID column of the newly created row with the ID generated by Access
            'you can then use this value to update related tables if neccessary.
            '------------------------------------------------------------------------------------------------------------------------
            e.Row("CustomerID") = cmd.ExecuteScalar()
            e.Row.AcceptChanges()
        End If

  End Sub


If you do it this way you will not get the following exception:
System.Data.NoNullAllowedException because you are letting access take care of the ID


0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jboehlen
ID: 11955378
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.
0
 

Author Comment

by:jboehlen
ID: 11955695
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.
0
 
LVL 4

Expert Comment

by:gdexter
ID: 11956100
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.

0
 

Author Comment

by:jboehlen
ID: 11956127
Ok, backed up and took notes. Back to the drawing board.  I will try that next. Thank you for your help.
0
 

Author Comment

by:jboehlen
ID: 11956228
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.
0
 

Author Comment

by:jboehlen
ID: 11959282
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?
0
 

Author Comment

by:jboehlen
ID: 11959760
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!!!!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now