Question Problem with Insert Command

[nick447923]

I am a beginning programmer and am having a problem with adding a record to a customer table in a Microsoft Access database. I have worked for hours reading my textbook and troubleshooting to no avail. The copy and paste below from VS is a little messy. When I execute this code below the record is not added and I do not get an error of any type. When I go into debug mode, all of the parameters are correct and everything looks good. If I open up Access I can manually add the record but using my program nothing happens. All datatypes are string in VS and text in Access.

First an instance of the Customer Class is created and this seems to be successful. But when I take that customer object and run the AddCustomer method of the customer class it is not successful in that the database does not reflect that the record was ever added.


Here is the code from class CustomerDB:


Public Shared Function AddCustomer(ByVal customer As Customer) As Boolean


Dim connection As OleDbConnection = PaulMeadeInsuranceDB.GetConnection
Try
Dim insertStatement As String _
= "INSERT INTO Customers (CustNumber, Lastname, Firstname, MidName, Address1, Address2," _
& " City, State, ZIP, Phone1, Phone2, PolicyNumber) VALUES" _
& " (@CustNumber, @Lastname, @Firstname, @MidName, @Address1, @Address2, @City, @State," _
& " @ZIP, @Phone1, @Phone2, @PolicyNumber)"

Dim insertcommand As New OleDbCommand(insertStatement, connection)
insertcommand.Parameters.AddWithValue("@CustNumber", customer.Custnumber)
insertcommand.Parameters.AddWithValue("@Lastname", customer.Lastname)
insertcommand.Parameters.AddWithValue("@Firstname", customer.FirstName)
insertcommand.Parameters.AddWithValue("@Midname", customer.MiddleName)
insertcommand.Parameters.AddWithValue("@Address1", customer.Address1)
insertcommand.Parameters.AddWithValue("@Address2", customer.Address2)
insertcommand.Parameters.AddWithValue("@City", customer.City)
insertcommand.Parameters.AddWithValue("@State", customer.State)
insertcommand.Parameters.AddWithValue("@ZIP", customer.Zip)
insertcommand.Parameters.AddWithValue("@Phone1",
customer.Phone1)
insertcommand.Parameters.AddWithValue("@Phone2", customer.Phone2)
insertcommand.Parameters.AddWithValue("@PolicyNumber", customer.Policynumber)
Return AddCustomer
Catch ex As OleDbException
MessageBox.Show(ex.Message, ex.GetType.ToString)
Finally
connection.Close()
End Try

Here is the code from the actual program:

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
Try
Dim record1C As New Customer(txtCustNumber.Text, txtLastname.Text, txtFirstName.Text, _
txtMiddleName.Text, txtAddress1.Text, txtAddress2.Text, txtCity.Text, _
txtState.Text, txtZIP.Text, txtPhone1.Text, txtPhone2.Text, txtPolicyNumber.Text)


CustomerDB.AddCustomer(record1C)


Catch ex As Exception
Throw ex
End Try
End Sub

 

[anthony.selby]

Whats your database layout like ?

 

[nick447923]

Database Layout

While trying to figure out this problem I removed all of the table relationships so its pretty straightforward. I have a Customer table, Policy table, PaymentsDue table, and a Customer Transaction table.

Does this answer your question?

 

[nick447923]

What was wrong

What I was missing was a connection.open at the beginning of the select statement and a insertcommand.ExecuteNonQuery(). It works now!1 Thanks for you guys' time.