Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Feb 2006
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to use an ASP String or an array with a SQL WHERE statment

    Hi,

    I have an ASP array that is dynamically created so it can be different everytime. For this example lets say it looks like this.

    Code:
    <%
     
    thisArray=Array("Mike","Jack","Nick","Sally") 
     
    %>

    How can I use this array in the "WHERE" field in a SQL statement. For example what I am trying to do would look something like the following SQL statment:

    Code:
    <%
    selectSQL = "SELECT * FROM PeopleDatabase WHERE namesField=thisArray
    %>
    I know normally you would manually put the fields in like the example below but I can't do that. I need to pull them from an array.

    Code:
    <%
     
    selectSQL = "SELECT * FROM PeopleDatabase WHERE namesField='Mike' Or 'Jack' Or 'Nick'
     
    %>
    Thanks for the help

  • #2
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Hi,
    You could use the IN clause in your sql statement, loop through each name and add it to the list.
    Try this:
    Code:
    <%@ Language=VBScript %>
    <html>
    <head>
    </head>
    <%
    	Dim thisArray 
    	Dim strSql
    	Dim i
    
    	thisArray = Array("Mike","Jack","Nick","Sally") 
    	strSql = "SELECT * FROM PeopleDatabase WHERE namesField IN(" 
    
    	' add each name to the IN clause
    	For i = 0 To UBound(thisArray) - 1
    		strSql = strSql & "'" & thisArray(i) & "', "
    	Next
    	' grab last element in the array
    	strSql = strSql & "'" & thisArray(UBound(thisArray)) & "')"
    	
    	Response.Write(strSql)
    
    %>
    </body>
    </html>
    Good luck;

  • #3
    Regular Coder
    Join Date
    May 2005
    Location
    Michigan, USA
    Posts
    566
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You loop through the values of the array and put them inside an IN clause.
    Note: I do not test code. I just write it off the top of my head. There might be bugs in it! But if any thing I gave you the overall theory of what you need to accomplish. Also there are plenty of other ways to accomplish this same thing. I just gave one example of it. Other ways might be faster and more efficient.

  • #4
    New Coder
    Join Date
    Feb 2006
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Excellent! Thanks that does exactly what I was asking.

    Although I don't understand why it appears to only loop through the first item in the array? Your code works fine the SQL statement displays properly.

    But I have a response.write that normally gives me "an email column in my DB" for each item. When I use the IN Clause it is only giving me the first item in the array which is Mike?

    If you know why this is happening let me know. I think it's something with my loop statement that conflicts with the IN clause. I only looked at it for an hour but if you see anything thing wrong let me know thanks. Here is all my code

    Code:
    <HTML>
    <HEAD><TITLE>Boat Test Dealer Database Report</TITLE></HEAD>
    <BODY>
    <H1>Names and emails </H1>
    <%
    
    
    Dim thisArray 
    	Dim strSql
    	Dim i
    	
    Dim conn, selectSQL, RecSet
    	Dim con, sql_insert, data_source
    
    
    '''''''''''''''''''''''''''''''''''''''' PULL Start '''''''''''''''''''''''''''''''''''''''''''''''''
    
    thisArray = Array("Mike","Jack","Nick","Sally") 
    
    
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open "Provider=SQLOLEDB; Data Source = sqlserver; Initial Catalog = Test; User Id = user; Password=PW"
    
    	
    	selectSQL = "SELECT * FROM PeopleDatabase WHERE namesField IN(" 
    
    	' add each name to the IN clause
    	For i = 0 To UBound(thisArray) - 1
    		selectSQL = selectSQL & "'" & thisArray(i) & "', "
    	Next
    	' grab last element in the array
    	selectSQL = selectSQL & "'" & thisArray(UBound(thisArray)) & "')"
    	
    	'Response.Write(selectSQL)
    
    Set RecSet = conn.Execute (selectSQL)
    
    If NOT RecSet.EOF THEN
    DO UNTIL RecSet.EOF
    
    	response.write RecSet("email") & "<br>" ''' **** Shouldn't this write for each item in the array? *****
    RecSet.MoveNext
    
    Loop
    End If
    
    RecSet.Close
    conn.Close
    Set RecSet = Nothing
    Set conn = Nothing
    
      
    '''''''''''''''''''''''''''''''''''''''' PULL END '''''''''''''''''''''''''''''''''''''''''''''''''
    %>
    
    <br>
    
    </body>
    </html>
    My output is:
    Mike@aol.com

    I am trying to get it to be:
    Mike@aol.com
    Jake@aol.com
    Nike@aol.com
    Sally@aol.com

    Thanks again

  • #5
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Response.Write the sql statement and paste it in query analyzer and see if you get the correct output.

    Good luck;

  • #6
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,068
    Thanks
    0
    Thanked 256 Times in 252 Posts
    You don't need to loop the array. You can use the Join method.
    Code:
    thisArray = Array("Mike","Jack","Nick","Sally") 
    strSql = "SELECT * FROM PeopleDatabase WHERE namesField IN ('" & Join(thisArray, "','") & "')"
    response.write strSql
    Glenn
    ____________________________________

    My Blog
    Tower of Hanoi Android app (FREE!)
    Tower of Hanoi Leaderboard
    Samegame Facebook App
    vBulletin Plugins
    ____________________________________

  • #7
    New Coder
    Join Date
    Feb 2006
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I found the problem!! It was every programmers enemy. The space that no one sees.

    I noticed my output string was showing this:

    SELECT * FROM PeopleDatabase WHERE namesField IN('Mike', ' Jack', ' Nick', ' Sally')

    Thats why only the first one was showing up because the rest had spaces before the name. So I got it to now look like this:

    SELECT * FROM PeopleDatabase WHERE namesField IN('Mike', 'Jack', 'Nick', 'Sally')

    And it works like a beut. I used the LTrim() Function to get the spaces out. I changed the code to look like this:

    Code:
    For i = 0 To UBound(thisArray) - 1
    		selectSQL = selectSQL & "'" & LTrim(thisArray(i)) & "', "
    			
    	Next
    	' grab last element in the array
    	selectSQL = selectSQL & "'" & LTrim(thisArray(UBound(thisArray))) & "')"
    	response.write selectSQL
    I like the Join method but couldn't easily figure out how to get the spaces out on it. Thanks you saved me a lot of time

  • #8
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,068
    Thanks
    0
    Thanked 256 Times in 252 Posts
    I like the Join method but couldn't easily figure out how to get the spaces out on it.
    How are you populating the array? You should have trimmed the elements as you populate the array.
    Glenn
    ____________________________________

    My Blog
    Tower of Hanoi Android app (FREE!)
    Tower of Hanoi Leaderboard
    Samegame Facebook App
    vBulletin Plugins
    ____________________________________

  • #9
    New Coder
    Join Date
    Feb 2006
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think I tried but it didn't work. I am populating the array using SPLIT from a string list that oringaly looks like (MYstringlist). So basicly this is what I am doing:

    Code:
    MYstringlist = "Mike,Jack,Nick,Sally" 
    
    thisArray=Split(MYstringlist,",")  '' Turns the comma seperated string into an array
    I think I tried doing it in the Split but maybe I'll give it another look. Thanks

  • #10
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,068
    Thanks
    0
    Thanked 256 Times in 252 Posts
    You don't have to turn the comma-delimited string into an array in the first place if the names are already in a string. You just need to trim each name.
    Code:
    MYstringlist = "  Mikes , Jack , Nicks , Sally " 
    Set regex = New RegExp 
    regex.Global = True 
    regex.Pattern = "\s*,\s*"
    MYstringlist = replace(trim(MYstringlist), "'", "''") 'escape single quotes in names to avoid error in sql
    MYstringlist = regex.replace(MYstringlist, "','")
    strSql = "SELECT * FROM PeopleDatabase WHERE namesField IN ('" & MYstringlist & "')"
    response.write strSql
    Glenn
    ____________________________________

    My Blog
    Tower of Hanoi Android app (FREE!)
    Tower of Hanoi Leaderboard
    Samegame Facebook App
    vBulletin Plugins
    ____________________________________


  •  

    Posting Permissions

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