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:

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

I don't see that you are actually executing the command.

As Mark pointed ou you never executed the command. After adding your parameters you should execute insertcommand.ExecuteNoQuery like this,



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

dim intRows as integer = insertcommand.ExecuteNonQuery() '(ExecuteNonQuery returns an int containing the number of rows affected)

IF intRows <= 0 Then

Return False

Else

Return True

End If

Catch Ex As OledbException ... etc



Hope this helps.

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

With the above code I think you are not properly executing the code: A simple code to insert...

Private Sub cmddata_Click(Index As Integer)
    On Error GoTo cmddata_Click_Err
   
        Dim strSQL            As String
        Dim lngAffected     As Long
        Dim mrs                As New ADODB.Recordset
        
        ' Control Array to INSERT, UPDATE AND DELETE
        Select Case Index
            '   INSERT
            Case 0
                If cmdData(0).Caption = "Insert" Then
                        strSQL = "INSERT INTO Customers VALUES(" & _
                        "'" & txtData(1).Text & "'," & _
                        "'" & txtData(2).Text & "'," & _
                        "'" & txtData(3).Text & "'," & _
                        "'" & txtData(4).Text & "'," & _
                        "'" & txtData(5).Text & "'," & _
                        "'" & txtData(6).Text & "'," & _
                        "'" & txtData(7).Text & "'," & _
                        "'" & txtData(8).Text & "'," & _
                        "'" & txtData(9).Text & "'," & _
                        "'" & txtData(10).Text & "'," & _
                        "'" & txtData(11).Text & "')"
   
                Else
                    'UPDATE
                    strSQL = "UPDATE tbldocdetails " & _
                        "SET Lastname= '" & txtData(2).Text & "'" & _
                        " ,Firstname= '" & txtData(3).Text & "'" & _
                        " ,MiddleName= '" & txtData(4).Text & "'" & _
                        " ,Address1= '" & txtData(5).Text & "'" & _
                        " ,Address2= '" & txtData(6).Text & "'" & _
                        " ,City= '" & txtData(7).Text & "'" & _
                        " ,State= '" & txtData(8).Text & "'" & _
                        " ,Address = '" & txtData(9).Text & "'" & _
                        " ,Phone1= '" & txtData(10).Text & "'" & _
                        " ,Phone2= '" & txtData(11).Text & "'" & _
                        " ,Phone2= '" & txtData(12).Text & "'" & _
                        " WHERE Custnumber= '" & txtData(1).Text & "'"

                End If
   
                gcnn1.Execute strSQL, lngAffected (gcnn1 is a global connection)
                If lngAffected = 0 Then
                    MsgBox "Another user has been updated the record.", vbCritical
                End If
                txtData(0).Locked = True
                txtData(0).BackColor = Me.BackColor
                cmdData(0).Caption = "Update"
                cmdData(1).Enabled = False
                cmdData(2).Enabled = False
               
               
            '   Add
            Case 1
   
                Call frmCustomer_Form_Clear
   
                txtData(2).SetFocus
                txtData(1).Locked = False
                txtData(1).BackColor = &H8000000C
                cmdData(0).Caption = "Insert"
                                                       
                mrs.Open "Select max(DoctorID) from Customer", gcnn1
                txtData(1) = Format(Val(mrs.Fields(0)) + 1, "000")
                mrs.Close
       
            '   Delete
            Case 2
                If vbNo = MsgBox("Are you sure to delete this?", vbQuestion + vbYesNo) Then Exit Sub
                
                strSQL = "DELETE * FROM Customer" & _
                    " WHERE CustomerNo= '" & txtData(1).Text & "'"
       
                gcnn1.Execute strSQL
               
                Call frmCustomer_Form_Clear
               
        End Select
       
        Call frmCustomer_InitListView
       
        Exit Sub
cmddata_Click_Err:
    If Err = -2147217900 Then
        MsgBox "Customer No (" & txtData(0).Text & ") already exists", vbCritical
    Else
        MsgBox Error, vbCritical
    End If
End Sub

sangay

sangay - you are posting a VB6 solution while the OP is working in .NET.

So then why is the OP in this forum??? This is the VB6.0 Database Programming section.... Should not this thread be moved to where the OP could get more knowledgable help???