Error caused by parameter value with comma (VB6)

Topic

New Reply

The following code is the issue:

CN.Execute (“INSERT INTO tblHistory ” _
& “(UserName, TermID, ActionDate, ActionDone, Msg) ” _
& “VALUES (‘” & OSInfo.UserName & “‘, ” _
& “‘” & OSInfo.ComputerName & “‘, ” _
& “‘” & Now() & “‘, ” _
& “‘Page Sent to: ” & sWhereClause & “‘, ” _
& “‘” & TDBMessage.Text & “‘)”)

The issue is sWhereClause, when it consists of multiple users, the values are separated by commas. The SQL statement reads this as part of the Insert statement and returns an error that the number of fields don’t match.

Is there a way I package that whole & “‘Page Sent to: ” & sWhereClause & “‘, ” so it inserts into one field?

This is a Debug.Print of that line containing the values it resolved to:

‘Page Sent to: Shea, Michael’ , ‘Sprague, Chris’

Reply | Quote

Replies

Your code looks OK. All values are surrounded in single quotes; this should prevent the commas within a value to be interpreted as separators. Is there a chance that one of the values you try to insert contains a single (or even double) quote? For instance, a name like O’Shea would cause problems.

Try assembling the SQL statement into a string variable first, and displaying it before executing it:

Dim strSQL As String
strSQL = “INSERT INTO tblHistory “_
…
& “‘” & TDBMessage.Text & “‘)”
MsgBox strSQL
CN.Execute strSQL

and see if there are quotes at unexpected places in the result, or if the string ends unexpectedly.

Reply | Quote

Hi Hans, thanks for your reply.

I gave it a try and also used string variable to replace that piece in the code. It still errored.

The problem is that the variable, sWhereClause, is something I parsed to use in a query. There it works great. But when trying to log the distribution the syntax is an issue. I just didn’t want to re-invent the wheel, but it appears I am going to have to.

Here is the piece of code that I’m using to build the Where clause, if it helps at all:

‘ Build WHERE clause
For iCounter = 0 To lstTo.ListCount – 1

iProgCount = iProgCount + 1

ProgBar.Value = iProgCount

If Len(sWhereClause) > 0 Then
sWhereClause = lstTo.List(iCounter) & “‘ , ‘” & sWhereClause
Else
sWhereClause = lstTo.List(iCounter)
End If

iProgCount = iProgCount + 1 ‘Increment progression bar

ProgBar.Value = iProgCount

Next

Reply | Quote

That’s helpful. You’re putting single quotes into sWhereClause; they cause the problem. Try using

sWhereClause = lstTo.List(iCounter) & " , " & sWhereClause

instead of

sWhereClause = lstTo.List(iCounter) & "' , '" & sWhereClause

Reply | Quote

Well, that did work for persisting the information to the log file but blew the Where syntax for the SQL. So I just created another variable called sWhereLog and assigned it the value from your modifications.

Do you know how I would represent special characters in code when trying to build an SQL string like this in the future? Actually, I have another issue with a combo box. Users can enter data that populates this combo box and when they put in an apostrophe, it kills the Where clause (much like you alluded to in your initial message).

Reply | Quote

I’d suggest you use the validate event of the combobox to disallow any apostrophes right at the start. That way you won’t have to deal with them later.

Reply | Quote

I was hoping there was a way to make that SQL friendly, but I’m sure that is the best way. I have a string validator class already built that I’ll throw into the project.

Reply | Quote