A1VBCode Forums / Classic Visual Basic (VB 6 or earlier) / Database Programming / Problem with Insert Statement / Latest Posts

RE: Problem with Insert Statement

vb5prgrmr — Mon, 08 Feb 2010 14:25:21 GMT

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

RE: Problem with Insert Statement

Mark — Mon, 08 Feb 2010 07:51:37 GMT

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

RE: Problem with Insert Statement

sangay — Mon, 08 Feb 2010 01:49:55 GMT

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

RE: Problem with Insert Statement

zimvbcoder — Thu, 28 Jan 2010 06:06:12 GMT

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.

RE: Problem with Insert Statement

Mark — Tue, 05 Jan 2010 19:46:06 GMT

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

Problem with Insert Statement

nick447923 — Tue, 05 Jan 2010 17:34:23 GMT

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

CustomerDB.AddCustomer(record1C)

Catch ex As Exception
Throw ex
End Try
End Sub
<code>