Troubleshooting - ERROR [42S00] [QODBC] Insert value must be a simple value

Problem Description 1:

We have a customer trying to import a credit memo from our database into QuickBooks using a vb.net program. They are getting the following error: ERROR [42S00] [QODBC] Insert value must be a simple value. We have other customers that can import credit memos, and I have compared the imported data and cannot find any difference in the data format. Can you give me an idea of what to look for?

Solutions 1:

You are getting the error " [QODBC] Insert value must be a simple value" because you are not passing the value in the correct format. The VB code generates this error. It is not a QODBC error.

Please verify your insert statement & try again. Please refer below-mentioned link to a sample VB code:

Examples of How to Use QODBC via Visual Basic

Please refer to the below-mentioned article for creating a credit memo:

How to create Credit Memos

Problem Description 2:

When I go to insert a Customer, I receive an error saying [QODBC] Insert value must be a simple value. I need to know what I am doing wrong to insert a new customer.

Please refer below code which I am using:

Public Sub InsertNewCustomers()

Dim name As String

Dim firstName As String

Dim lastName As String

Dim companyName As String

Dim contact As String

Dim billAddressAddr1 As String

Dim billAddressAddr2 As String

Dim billAddressAddr3 As String

Dim billAddressCity As String

Dim billAddressState As String

Dim address postal code As String

Dim phone As String

Dim fax As String

Dim email As String

'Testing to Insert, remove before going LIVE!!!*************************************************************************************************

name = "ABC XYZ"

firstName = "ABC"

lastName = "XYZ"

companyName = "Test Company"

contact = "Jerry"

billAddressAddr1 = "503 Test Club"

billAddressAddr2 = ""

billAddressAddr3 = ""

billAddressCity = "Test City"

billAddressState = "OK"

addressPostalCode = "11644"

phone = "111-111-1111"

fax = ""

email = ""

'*********************************************************************************************************************

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

conn = New ADODB.Connection

conn.ConnectionString = "DSN=Quickbooks Data;OLE DB Services=-2;"

conn.Open()

Try

' Create the new record.

rs = conn.Execute( _

"INSERT INTO customer(name, firstname, lastname, companyName, contact, BillAddressAddr1, BillAddressAddr2, BillAddressAddr3, BillAddressCity, BillAddressState, BillAddressPostalCode, Phone, Fax, Email)VALUES(name, firstname, lastname, companyName, contact, billAddressAddr1, billAddressAddr2, billAddressAddr3, billAddressCity, billAddressState, billAddressPostalCode, phone, fax, email)")

LogEntry("New Customer Added to QB")

Catch e As an Exception

MsgBox(e.ToString)

End Try

' Close the database.

rs.Close()

rs = Nothing

conn.Close()

conn = Nothing

'*********************************************************************************************************************

End Sub

Solutions 2:

You are getting the error " [QODBC] Insert value must be a simple value" because you are not passing the value in the correct format. The VBA code generates this error. It is not a QODBC error.

There is a problem with your Insert statement. You cannot pass the value directly. Your insert statement should be like as below.

INSERT INTO customer(Name,FirstName,LastName,CompanyName,Contact,BillAddressAddr1,BillAddressAddr2,BillAddressAddr3,BillAddressCity,BillAddressState,BillAddressPostalCode,Phone,Fax,Email) " & _

" VALUES( '" + name + "','" + firstName + "','" + lastName + "','" + companyName + "','" + contact + "','" + billAddressAddr1 + "','" + billAddressAddr2 + "','" + billAddressAddr3 + "','" + billAddressCity + "','" + billAddressState + "','" + billAddressPostalCode + "','" + phone + "','" + fax + "','" + email + "')

Please refer to the updated code & try to insert the record with this code.

Public Sub InsertNewCustomers()

Dim name As String

Dim firstName As String

Dim lastName As String

Dim companyName As String

Dim contact As String

Dim billAddressAddr1 As String

Dim billAddressAddr2 As String

Dim billAddressAddr3 As String

Dim billAddressCity As String

Dim billAddressState As String

Dim addressPostalCode As String

Dim phone As String

Dim fax As String

Dim email As String

'Testing to Insert, remove before going LIVE!!!*************************************************************************************************

name = "ABC XYZ"

firstName = "ABC"

lastName = "XYZ"

companyName = "Test Company"

contact = "Jerry"

billAddressAddr1 = "503 Test Club"

billAddressAddr2 = ""

billAddressAddr3 = ""

billAddressCity = "Test City"

billAddressState = "OK"

addressPostalCode = "11644"

phone = "111-111-1111"

fax = ""

email = ""

'*********************************************************************************************************************

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

conn = New ADODB.Connection

conn.ConnectionString = "DSN=Quickbooks Data;OLE DB Services=-2;"

conn.Open()

Try

' Create the new record.

rs = conn.Execute( _

sSQL = "INSERT INTO customer(Name,FirstName,LastName,CompanyName,Contact,BillAddressAddr1,BillAddressAddr2,BillAddressAddr3,BillAddressCity,BillAddressState,BillAddressPostalCode,Phone,Fax,Email) " & _

" VALUES( '" + name + "','" + firstName + "','" + lastName + "','" + companyName + "','" + contact + "','" + billAddressAddr1 + "','" + billAddressAddr2 + "','" + billAddressAddr3 + "','" + billAddressCity + "','" + billAddressState + "','" + billAddressPostalCode + "','" + phone + "','" + fax + "','" + email + "')"

Set conn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

conn.Open sConnectString

' Create a new record.

rs = conn.Execute(sSQL)

sMsg = sMsg & "Record Added!!!"

MsgBox msg

' Close the database.

Set rs = Nothing

Set conn = Nothing

'*********************************************************************************************************************

End Sub