Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Location
    denver, CO
    Posts
    4

    Unanswered: Query help: need syntax for WHERE variable=column_name

    Can anyone help? I need to create an INSERT query that uses several items selected out of a multi-select drop down list. These items are stored in an array but the user choices are dynamic and are never the same. I need to put a "yes" or "no" into the database column field when an array item matches a column name.

    Here is the pseudocode, and I need the correct syntax. Any help is appreciated.

    <%
    'Variable declarations
    Dim fname, lname
    Dim ColorSelectionsArray(20)

    'Connection and form request code-I'm good with this
    blah, blah, blah

    'SQL queries, good with this one
    SQL = "INSERT INTO persontable (first, last) VALUES(' & fname & ', ' &
    lname & ')"

    'This is where i'm stuck
    for i = 1 to ColorSelectionsArray.count
    {
    if(ColorSelectionsArray(1) == colortable_column_name
    INSERT INTO colortable (red, blue, yellow, orange) VALUES ("yes")

    else
    INSERT INTO colortable (red, blue, yellow, orange) VALUES ("no")
    }
    %>

    Here are what I want my tables to look like when code is done:

    persontable
    personid | fname | lname |
    1 fred jones

    colortable
    personid | red | blue | yellow | orange |
    1 no yes yes no

    This second table is related to first table and both are being filled with information coming from one form submission.

    Thanks for any help.

  2. #2
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    'SQL queries, good with this one
    SQL = "INSERT INTO persontable (first, last) VALUES(' & fname & ', ' &
    lname & ')"
    SQL = "INSERT INTO persontable (first, last) VALUES('" & fname & "', '" & lname & "')"
    Note the use of double and single quotes.

    For the second part. There are several ways to tackle that.
    1) do you have to use an array? Are the individual response available on the forms?
    "INSERT INTO colortable (red, blue, yellow, orange) VALUES ('" & me.RedControl & "', '" & me.BlueControl & "', '" & me.YellowControl & "', '" & me.OrangeControl & "');"

    2) If you have to use an array, then loop through and concatanate the values together and then build a similar string as above.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  3. #3
    Join Date
    Dec 2003
    Location
    denver, CO
    Posts
    4
    I'm not sure if I have to use an array, but the values are coming from a multi-select list box. Any ideas there?

    Thanks so much for responding

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't know if an array would work for you here. I would iterate through the list box and concatenate the values to a string, including the SQL specific qualifiers. Something like:
    Code:
    Dim i As Integer
    Dim s As String
    
    for i = 0 to (lbYourListBox.ListCount - 1)
       if lbYourListBox.Selected(i) Then
           s = s & lbYourListBox.Column(0, i) 
       End If
    Next i
    Then you'll need to take that a step further and parse the sql qualifiers into the string:
    Code:
    Dim i As Integer
    Dim s As String
    Dim chk As Boolean
    
    for i = 0 to (lbYourListBox.ListCount - 1)
       if lbYourListBox.Selected(i) Then
           if chk = True Then 
              s = s & "', '" & lbYourListBox.Column(0, i) 
           else 
              s = "('" & lbYourListBox.Column(0, i) 
           chk = True
       End If
    Next i
    
    s = s & "')"
    .. anyways.. I think you get the idea. Of course that's just one way to do it.
    Last edited by Teddy; 12-22-03 at 15:10.

  5. #5
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    I'm sorry, I don't know how to refer to the elements selected in a list box. I would guess that if you cycle through a list box until some sort of end of file indicator, you could use that to build a string.

    Debugging tip.
    Sometimes I make a message box and print my string as I build it. Then I now exactly how it looks after all the single and double quotes and & signs get interpreted.

    Sorry I can't help you with the exact syntax, but that second line of code in Blue should be correct.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by jpshay
    I'm sorry, I don't know how to refer to the elements selected in a list box. I would guess that if you cycle through a list box until some sort of end of file indicator, you could use that to build a string.

    Debugging tip.
    Sometimes I make a message box and print my string as I build it. Then I now exactly how it looks after all the single and double quotes and & signs get interpreted.

    Sorry I can't help you with the exact syntax, but that second line of code in Blue should be correct.
    Actually.. that example is how to refer to the elements of the list box. That code first gathers the total number of items in the list box. Then it sets i to that number - 1. Then it iterates i times through the listbox rows to evaluate if they have been selected, if they have, then they are appended to the string s along with pertinent sql syntax. The boolean in the second example is to control sql syntax. If it is the first item being added to the string, it will add a (. Otherwise it will comma deliminate. Then the closing paranthesis is added after all iteration has completed.

    Does that make sense?

  7. #7
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    I didn't realize we were posting at the same time, I hadn't seen your answer.

    Teddy has a helpful answer. Just remember that when concatanating your answer to include additional single quotes around text.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by jpshay
    I didn't realize we were posting at the same time, I hadn't seen your answer.

    Teddy has a helpful answer. Just remember that when concatanating your answer to include additional single quotes around text.
    Good catch. I altered my original examples to reflect necessary string qualifyers.

Posting Permissions

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