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. 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 for the AddCustomer method:

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

        Dim connection As OleDbConnection = PaulMeadeInsuranceDB.GetConnection
            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)
        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
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)


Catch ex As Exception
Throw ex
End Try
End Sub

Edited by __avd: Added [code] tags. Encase your code in: [code] and [/code] tags.

8 Years
Discussion Span
Last Post by nick447923

Hi Nick
You can try this

Public Function GetConnectionString() As String
Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= D:\tes.mdb" 'you can change the address
End Function

Public Sub InsertData(ByVal SQL As String)
Dim conn As New OleDb.OleDbConnection
Dim comm As New OleDb.OleDbCommand

conn = New OleDb.OleDbConnection(GetConnectionString())

comm = New OleDb.OleDbCommand(SQL, conn)
End Sub

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

Dim insertStatement As String = "INSERT INTO Customers (CustNumber, Lastname"
insertStatement &= ", Firstname, MidName, Address1, Address2, City, State, ZIP, "
insertStatement &= "Phone1, Phone2, PolicyNumber) "
insertStatement &= "VALUES ( '" & txtCustNumber.Text & "', '" & txtLastname.Text & "'"
insertStatement &= ", '" & txtFirstName.Text & "', '" & txtMiddleName.Text & "'"
insertStatement &= ", '" & txtAddress1.Text & "', '" & txtAddress2.Text & "'"
insertStatement &= ", '" & txtCity.Text & "', '" & txtState.Text & "', '" & txtZIP.Text & "'"
insertStatement &= ", '" & txtPhone1.Text & "', '" & txtPhone2.Text & "', '" & txtPolicyNumber.Text & "')"


End Sub

Edited by __avd: Added [code] tags. Encase your code in: [code] and [/code] tags.

Votes + Comments
Bad practice.

ExecuteNonQuery() method is required,

 insertcommand.Parameters.AddWithValue("@PolicyNumber",   customer.Policynumber)

Yes the command.executenonquery addition made it work! Thanks guys I really appreciate your time. Now I can move on.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.