...

View Full Version : How to use an ASP String or an array with a SQL WHERE statment



sherbert99
02-28-2006, 11:01 PM
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.



<%

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:


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


<%

selectSQL = "SELECT * FROM PeopleDatabase WHERE namesField='Mike' Or 'Jack' Or 'Nick'

%>

Thanks for the help

Brandoe85
02-28-2006, 11:14 PM
Hi,
You could use the IN clause in your sql statement, loop through each name and add it to the list.
Try this:


<%@ 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;

SpirtOfGrandeur
02-28-2006, 11:14 PM
You loop through the values of the array and put them inside an IN clause.

sherbert99
03-01-2006, 12:30 AM
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


<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

Brandoe85
03-01-2006, 12:38 AM
Response.Write the sql statement and paste it in query analyzer and see if you get the correct output.

Good luck;

glenngv
03-01-2006, 03:37 AM
You don't need to loop the array. You can use the Join method.

thisArray = Array("Mike","Jack","Nick","Sally")
strSql = "SELECT * FROM PeopleDatabase WHERE namesField IN ('" & Join(thisArray, "','") & "')"
response.write strSql

sherbert99
03-01-2006, 06:06 PM
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:


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

glenngv
03-02-2006, 08:35 AM
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.

sherbert99
03-03-2006, 05:34 PM
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:


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

glenngv
03-07-2006, 10:10 AM
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.

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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum