Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error caused by parameter value with comma (VB6)

    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'

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error caused by parameter value with comma (VB6)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error caused by parameter value with comma (VB6)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error caused by parameter value with comma (VB6)

    That's helpful. You're putting single quotes into sWhereClause; they cause the problem. Try using
    <pre>sWhereClause = lstTo.List(iCounter) & " , " & sWhereClause</pre>

    instead of
    <pre>sWhereClause = lstTo.List(iCounter) & "' , '" & sWhereClause</pre>


  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error caused by parameter value with comma (VB6)

    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).

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error caused by parameter value with comma (VB6)

    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.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error caused by parameter value with comma (VB6)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •