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(DsCust omers1)
'Bind customer list box
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.Name")
lstCustomer.DataBindings.A dd(pbndTem p)
'Bind individual field (radio button selection)
pbndTemp = New Binding("Checked", DsCustomers1, "tblCustomers.Individual")
radIndividual.DataBindings .Add(pbndT emp)
'Bind business field (radio button selection)
pbndTemp = New Binding("Checked", DsCustomers1, "tblCustomers.Business")
radBusiness.DataBindings.A dd(pbndTem p)
'Bind the customer ID field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.CustomerID")
lblCustomerNum.DataBinding s.Add(pbnd Temp)
'Bind company name field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.CompanyName" )
txtCompany.DataBindings.Ad d(pbndTemp )
'Bind company contact name field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.ContactName" )
txtContact.DataBindings.Ad d(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(pbndTe mp)
'Bind customer address name field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.Address")
txtAddress.DataBindings.Ad d(pbndTemp )
'Bind customer city field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.City")
txtCity.DataBindings.Add(p bndTemp)
'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(pb ndTemp)
'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(pb ndTemp)
'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(pbndTe mp)
'Bind tax exempt ID field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.TaxExemptID" )
txtTaxExemptID.DataBinding s.Add(pbnd Temp)
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(DsCustom ers1, "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(DsCustom ers1, "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(DsCustom ers1, "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(DsCustom ers1, "tblCustomers").Position = Me.DsCustomers1.tblCustome rs.Rows.Co unt - 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(DsCustom ers1, "tblCustomers").AddNew()
pdsInsertedRows = DsCustomers1.GetChanges(Da taRowState .Added)
End Sub
Private Sub btnCustomerSave_Click(ByVa l 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(DsCustom ers1, "tblCustomers").EndCurrent Edit()
'method returns a Dataset containing the record or records that were added or changed
pdsInsertedRows = DsCustomers1.GetChanges(Da taRowState .Added)
pdsModifiedRows = DsCustomers1.GetChanges(Da taRowState .Modified)
'tests whether a row exists in the dataset.
If Not pdsInsertedRows Is Nothing Then
odbdaCustomers.Update(pdsI nsertedRow s)
End If
If Not pdsModifiedRows Is Nothing Then
odbdaCustomers.Update(pdsM odifiedRow s)
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
#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(DsCust
'Bind customer list box
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.Name")
lstCustomer.DataBindings.A
'Bind individual field (radio button selection)
pbndTemp = New Binding("Checked", DsCustomers1, "tblCustomers.Individual")
radIndividual.DataBindings
'Bind business field (radio button selection)
pbndTemp = New Binding("Checked", DsCustomers1, "tblCustomers.Business")
radBusiness.DataBindings.A
'Bind the customer ID field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.CustomerID")
lblCustomerNum.DataBinding
'Bind company name field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.CompanyName"
txtCompany.DataBindings.Ad
'Bind company contact name field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.ContactName"
txtContact.DataBindings.Ad
'Bind customer first name field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.FName")
txtFName.DataBindings.Add(
'Bind customer last name field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.LName")
txtLName.DataBindings.Add(
'Bind Customer first and last name as alias "Name"
'pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.FName, tblCustomers.LName AS tblCustomers.Name")
' lstCustomers.DataBindings.
'Bind customer address name field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.Address")
txtAddress.DataBindings.Ad
'Bind customer city field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.City")
txtCity.DataBindings.Add(p
'Bind customer state field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.State")
cboState.DataBindings.Add(
'Bind customer zip code field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.ZipCode")
txtZip.DataBindings.Add(pb
'Bind customer first telephone number field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.Phone1")
txtPhone1.DataBindings.Add
'Bind customer second telephone number field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.Phone2")
txtPhone2.DataBindings.Add
'Bind customer fax number field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.Fax")
txtFax.DataBindings.Add(pb
'Bind customer email address field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.Email")
txtEmail.DataBindings.Add(
'Bind tax exempt field
pbndTemp = New Binding("Checked", DsCustomers1, "tblCustomers.TaxExempt")
chkTaxExempt.DataBindings.
'Bind tax exempt ID field
pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.TaxExemptID"
txtTaxExemptID.DataBinding
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(DsCustom
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(DsCustom
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(DsCustom
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(DsCustom
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(DsCustom
pdsInsertedRows = DsCustomers1.GetChanges(Da
End Sub
Private Sub btnCustomerSave_Click(ByVa
'allows records to be added or changed and saved back to the database
Me.BindingContext(DsCustom
'method returns a Dataset containing the record or records that were added or changed
pdsInsertedRows = DsCustomers1.GetChanges(Da
pdsModifiedRows = DsCustomers1.GetChanges(Da
'tests whether a row exists in the dataset.
If Not pdsInsertedRows Is Nothing Then
odbdaCustomers.Update(pdsI
End If
If Not pdsModifiedRows Is Nothing Then
odbdaCustomers.Update(pdsM
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
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.Inser tCommand = Me.OleDbInsertCommand1
Specifically does your Adapter have the appropriate InsertCommand object set?
'from the example i have created
Me.OleDbDataAdapter1.Inser
ASKER
Is this what you are referring to? This was automatically generated for me.....
Me.odbdaCustomers.InsertCo mmand = Me.OleDbInsertCommand1
Me.odbdaCustomers.InsertCo
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.
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?
ASKER
Error:
An unhandled exception of type 'System.Data.NoNullAllowed Exception' 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.EndCurrentEdi t()
If bNewRow Then
cboCustomers.SelectedIndex = bmbCustomers.Count - 1
bNewRow = False
End If
End If
Me.SetMaintenanceButtons(T rue)
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.
An unhandled exception of type 'System.Data.NoNullAllowed
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.EndCurrentEdi
If bNewRow Then
cboCustomers.SelectedIndex
bNewRow = False
End If
End If
Me.SetMaintenanceButtons(T
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.
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.
ASKER
Ok, backed up and took notes. Back to the drawing board. I will try that next. Thank you for your help.
ASKER
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.
ASKER
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?
ASKER
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!!!!
Thank you SO much for everyone's help. Now I know how to work with autonumbers!!!!
ASKER