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 I'm trying to make my program add a new row/customer to the customer database. It hasn't been working and I don't know anyone that knows how to do this. I've got a lot of commented out code, but toward the bottom is what I'm 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 customer's 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 I'm 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 don't know if it'd work even after fixing that. 99 is the value I'm entering in the text box.

Edited April 26, 2005 by LandDestroyer

[Robby]

I didn't look at your code but from the error message I'd say you cannot insert Card_num 99 if it already exists in the table.

[LandDestroyer]

that's what you'd think, but i already checked, it's not in there. it doesn't make sense.

chris g

[Robby]

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