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. 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
        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

[tede_saputra]

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())

conn.Open()
comm = New OleDb.OleDbCommand(SQL, conn)
comm.ExecuteNonQuery()
comm.Dispose()
conn.Dispose()
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 & "')"

InsertData(insertStatement)

End Sub

[kvprajapati]

ExecuteNonQuery() method is required,

...
 insertcommand.Parameters.AddWithValue("@PolicyNumber",   customer.Policynumber)
 connection.Open()
 insertcommand.ExecuteNonQuery()
 connection.Close()
 ....

[nick447923]

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