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'
%>
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>
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.
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>
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
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
Location: Los Angeles, CA Original Location: Philippines
Posts: 10,241
Thanks: 0
Thanked 112 Times in 111 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
Before you post, read our: 
