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