Need help adding new rows to an Access Database 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



PublicClass frmAddCustomer

Inherits CIS4600blahPracticeForms.frmMain

Dim bmadd As BindingManagerBase

Dim mintpositionhere AsInteger

Dim mblnADd AsBoolean

PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'Closes this form

Me.Close()

EndSub

PrivateSub frmAddCustomer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load

txtLogo.Visible = False

picLogo.Visible = False

bmadd = Me.BindingContext(DsAddCustomer2, "Customers")

daAddcustomer.Fill(DsAddCustomer2)

Dim mvalue AsNew valueClass

bmadd.Position = mvalue.position

Dim strvalue AsString

EndSub

PrivateSub 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 = TrueThen

'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

EndTry

EndIf

Else

MessageBox.Show("Error saving the file", "Adding or Editing Customer Database")

EndIf

EndSub

PrivateSub refreshPage()

'Reloads the dataset in case of changes

bmadd = Me.BindingContext(DsAddCustomer2, "Customers")

daAddcustomer.Fill(DsAddCustomer2)

EndSub

PrivateSub 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

EndSub

EndClass






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.

 

[jmcilhinney]

The card_num column of tblCustomers is, I'm guessing, the primary key for the table, or at least has been specified as not to be allowed duplicate values. You must already have a record in your table with a card_num of 99.

 

[LandDestroyer]

that's the weird thing, i didn't. i just found the windows form wizard and i'm trying to use that, but still wish i knew why this code wasn't working.
chris g

 

[jmcilhinney]

If you are using data binding perhaps entering a new value in txtCustomerID automatically creates a new row. I don't know whether this is the case or not but you might like to check.

 

[LandDestroyer]

nopey. guess i'll do it a different way or change my functionality.

thanks,
chris g

 

[jmcilhinney]

If you'd like me to take another look, perhaps supply a more concise code listing. Your original is more difficult to read than is necessary because of the length and all the commented-out code. Try using

 tags by using the "#" button in the advanced editor when posting.  This makes code easier to read.

 

[Paszt]

I would suggest changing the functionality and consider using dataBindings. The Data Form Wizard is definately a good starting point to learning ADO.

One thing to realize is that the Update function of a dataAdapter works for inserted rows as well as updated and deleted rows.
Summary of the DataAdapter.Update Function:
Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified System.Data.DataSet from a System.Data.DataTable.
That being said, you must specify the INSERT, UPDATE, and DELETE statements for the dataAdapter; that can be done using the Data Adapter Configuration Wizard.

Here is a link to a simple app that may be of some use: sharing sqlDataSet throughout project?

My guess as to the problem with your code is as jmcilhinney stated, a duplicate value for the key field. I can't see in your code where you set the value. But if you use the DataForm Wizard, it will create a dataSet schema which will assist with autoincrementing the key fields.