Adding a new row to an Access table in vb.net

[LandDestroyer]

Hi. I have an add new or edit current customer form. It is opened via another form, where I select a current customer and click add/edit button. My update works, but Im trying to make my program add a new row/customer to the customer database. It hasnt been working and I dont know anyone that knows how to do this. Ive got a lot of commented out code, but toward the bottom is what Im currently working with. Please let me know how I can get this to work. The Form loads, and the dataset is filled. The position is determined by a value held in a class (so it can load the information for the customer selected on the previous form). When you press add, it is supposed to clear out the text boxes for data entry and turn on a switch to show there has been customer data entered for a new customer. Then the update button is pressed and is supposed to add a new row in the database table with the information from the test fields. I need to have this figured out by 6 pm tommorrow. Help!

Chris Gregory
christopherleegregory@hotmail.com


Imports System
Imports System.Data
Imports System.Data.OleDb


Public Class frmAddCustomer
Inherits CIS4600blahPracticeForms.frmMain
Dim bmadd As BindingManagerBase
Dim mintpositionhere As Integer
Dim mblnADd As Boolean

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Closes this form
Me.Close()
End Sub

Private Sub frmAddCustomer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
txtLogo.Visible = False
picLogo.Visible = False
bmadd = Me.BindingContext(DsAddCustomer2, "Customers")
daAddcustomer.Fill(DsAddCustomer2)
Dim mvalue As New valueClass
bmadd.Position = mvalue.position
Dim strvalue As String
End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Updates or add the current customers information to the database
perhaps use a message box "are you sure"
bmadd.EndCurrentEdit()
If MessageBox.Show("Do you want to save the changes?", "Are you sure?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
If mblnADd = True Then
Me.BindingContext(DsAddCustomer2, "tblEmployees").EndCurrentEdit()
DsAddCustomer2.GetChanges(DataRowState.Added)
Dim connAddNew As OleDb.OleDbConnection
Dim rsCustomers
Dim newCustomerRow As DsAddCustomer.CustomersDataTable = DsAddCustomer2.Customers
Dim customerRow As DsAddCustomer.CustomersRow = newCustomerRow.NewRow
Add data to the row.
customerRow("card_num") = txtCustomerID.Text
customerRow("cust_first") = txtFirst.Text
customerRow("cust_last") = txtLastName.Text
customerRow("cust_addr") = txtStreet.Text
customerRow("cust_city") = txtCity.Text
customerRow("cust_state") = txtState.Text
customerRow("cust_zip") = txtZip.Text
customerRow("cust_phone") = txtPhone.Text
customerRow("cust_creditcardnum") = txtCardNum.Text
Add the new row to the table.
newCustomerRow.Rows.Add(customerRow)
Dim conn As OleDbConnection = OleDbConnection1
Dim myCommand As OleDbCommand

Dim mycommand As OleDbCommand = New OleDbCommand("INSERT INTO Customers (card_num, cust_first, cust_last, cust_addr, cust_city, cust_state, cust_zip, cust_phone, cust_creditcardnum)") & _
"VALUES(@num, @first, @last, @addr, @city, @state, @zip, @phone, @credit)", conn)

myCommand.CommandText = ("INSERT INTO Customers (card_num, cust_first, cust_last, cust_addr, cust_city, cust_state, cust_zip, cust_phone, cust_creditcardnum)" & _
"values(@num, @first, @last, @addr, @city, @state, @zip, @phone, @credit)")
myCommand.Parameters.Add("@num", txtCustomerID.Text)
myCommand.Parameters.Add("@first", txtFirst.Text)
myCommand.Parameters.Add("@last", txtLastName.Text)
myCommand.Parameters.Add("@addr", txtStreet.Text)
myCommand.Parameters.Add("@city", txtCity.Text)
myCommand.Parameters.Add("@state", txtState.Text)
myCommand.Parameters.Add("@zip", txtZip.Text)
myCommand.Parameters.Add("@phone", txtPhone.Text)
myCommand.Parameters.Add("@credit", txtCardNum.Text)

daAddcustomer.InsertCommand = myCommand

Return daAddcustomer

Dim newrow As DataRow = DsAddCustomer2.Customers.NewRow
newrow = DsAddCustomer2.Customers.NewRow
newrow("card_num") = txtCustomerID.Text
newrow("cust_first") = txtFirst.Text
newrow("cust_last") = txtLastName.Text
newrow("cust_addr") = txtStreet.Text
newrow("cust_city") = txtCity.Text
newrow("cust_state") = txtState.Text
newrow("cust_zip") = txtZip.Text
newrow("cust_phone") = txtPhone.Text
newrow("cust_creditcardnum") = txtCardNum.Text
DsAddCustomer2.Customers.Rows.Add(newrow)
bmadd.AddNew()

Dim tblCustomers As DataTable
tblCustomers = DsAddCustomer2.Tables("Customers")
Dim drcurrent As DataRow
drcurrent = tblCustomers.NewRow()
drcurrent("card_num") = txtCustomerID.Text
drcurrent("cust_first") = txtFirst.Text
drcurrent("cust_last") = txtLastName.Text
drcurrent("cust_addr") = txtStreet.Text
drcurrent("cust_city") = txtCity.Text
drcurrent("cust_state") = txtState.Text
drcurrent("cust_zip") = txtZip.Text
drcurrent("cust_phone") = txtPhone.Text
drcurrent("cust_creditnum") = txtCardNum.Text
tblCustomers.Rows.Add(drcurrent)
mblnADd = False
daAddcustomer.Update(DsAddCustomer2, "Customers")
DsAddCustomer2.AcceptChanges()

Else
Try
daAddcustomer.Update(DsAddCustomer2, "Customers")
DsAddCustomer2.AcceptChanges()
Catch
End Try
End If
Else
MessageBox.Show("Error saving the file", "Adding or Editing Customer Database")

End If
End Sub

Private Sub refreshPage()
Reloads the dataset in case of changes
bmadd = Me.BindingContext(DsAddCustomer2, "Customers")
daAddcustomer.Fill(DsAddCustomer2)
End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADd.Click
bmadd.Position = bmadd.Count + 2
txtCustomerID.Text = ""
txtFirst.Text = ""
txtLastName.Text = ""
txtStreet.Text = ""
txtState.Text = ""
txtZip.Text = ""
txtPhone.Text = ""
txtCity.Text = ""
txtCardNum.Text = ""
mblnADd = True
End Sub

End Class




This is the current error Im running into:

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

Additional information: Column card_num is constrained to be unique. Value 99 is already present.

It errors at this line:
tblCustomers.Rows.Add(drcurrent)

Though I dont know if itd work even after fixing that. 99 is the value Im entering in the text box.

 

[Robby]

I didnt look at your code but from the error message Id say you cannot insert Card_num 99 if it already exists in the table.

 

[LandDestroyer]

thats what youd think, but i already checked, its not in there. it doesnt make sense.
chris g

 

[Robby]

Is Card_num an Identity column, is it auto-generated?