Need help writing SQL Query in ASP

by: sybePosted on 2009-10-07 at 08:24:12ID: 25516539

> I have an array with "," (comma) separated values.

My thoughts are that either you have an arry of values, or you have a comma-separated string with values.

Anyway, you could use something like
SELECT * FROM <tablename> WHERE <column> IN ('value1', 'value2')

To construct that in ASP from an array with string-values it would be something like this:

sSQL = "SELECT * FROM <tablename> WHERE <column> IN ('" & Join(yourArray, "','") & "')"

by: technofilePosted on 2009-10-07 at 08:30:44ID: 25516597

You need to run a loop for each value like so

demoArray = ("abc", "xyz")
 
strSQL = "SELECT DISTINCT demoName, Link,  FROM D_MainDemo WHERE "
dim i as integer
if demoArray.Length > 0 then
strSQL = strSQL & " demoName = '" & demoArry(i).ToString & "' "
end if
for i = 1 to demoArray.Length - 1
strSQL = strSQL & " OR demoName = '" & demoArry(i).ToString & "' "
next
strSQL = strSQL & " ORDER BY demoName"

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

Select all Open in new window

by: rgarimellaPosted on 2009-10-07 at 08:40:06ID: 25516694

Technofile,

Is demoArray.length is an ASP command because i am getting a object required error

by: sybePosted on 2009-10-07 at 08:44:02ID: 25516732

As I pointed out, no loop needed. The IN() construction is quite a bit shorter than having numerous "OR" statements.

by: technofilePosted on 2009-10-07 at 08:50:06ID: 25516789

well it depends on what version of asp you are using.
But sybe method should work
try the code below

sSQL = "SELECT Distinct demoName, Link FROM D_MainDemo WHERE demoName IN ('" & Join(demoArray, "','") & "')"

1:

Select all Open in new window

by: technofilePosted on 2009-10-07 at 08:52:40ID: 25516812

the .length would be needed to be done in codebehind it is actually vb.net code but the solution above should work

by: rgarimellaPosted on 2009-10-07 at 08:53:04ID: 25516819

sybe,

for some reason the code you provide only display correctly when there is 1 value in the array,

for multiple values it fails

by: technofilePosted on 2009-10-07 at 08:53:30ID: 25516825

oops i fogrot to change the sSQL to strSQL but I am sure you figured that out

by: rgarimellaPosted on 2009-10-07 at 08:58:08ID: 25516875

Here is the code that I am using

it displays the first value only

		
strSQL =  "SELECT demoName, Link, width, height, Description FROM D_MainDemo WHERE demoName IN ('" & Join(demoArray, "','") & "')"
		
		
		set objRS = GetDBConnection().execute(strSQL)
			DO WHILE NOT objRS.EOF
				vLink = objRS("Link")
				Response.Write(vLink)
			
			objRS.MoveNext
            LOOP

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

Select all Open in new window

by: technofilePosted on 2009-10-07 at 08:58:33ID: 25516881

Do you have an arry or a string that is like "xyz,abc,afs"

by: rgarimellaPosted on 2009-10-07 at 09:04:59ID: 25516934

it is a string like "xyz,abc,afs"

by: technofilePosted on 2009-10-07 at 09:13:25ID: 25517020

oh LOL then you need to do something like

strSQL = "SELECT Distinct demoName, Link FROM D_MainDemo WHERE demoName IN ('" & Join(Split(demoArray, ","), "','") & "')"

1:

Select all Open in new window

by: rgarimellaPosted on 2009-10-07 at 09:17:17ID: 25517059

I am getting a Type mismatch error with the latest split code

by: technofilePosted on 2009-10-07 at 09:27:29ID: 25517186

can you give the exact code that defines your demoarry because we are going around in circles

by: technofilePosted on 2009-10-07 at 09:32:35ID: 25517239

Try this and tell me where the error comes in if at all

splitArray=split(demoArray, ",")
strSQL = "SELECT Distinct demoName, Link FROM D_MainDemo WHERE demoName IN ('" & Join(splitArray, "','") & "')"

1:
2:
3:

Select all Open in new window

by: rgarimellaPosted on 2009-10-07 at 10:13:26ID: 25517642

Same problem, anyway thanks for your help

by: technofilePosted on 2009-10-07 at 11:12:36ID: 25518200

Can you show the code that defines the demoArray?
Does it error out on the line that defines splitArray or the Join?

by: rgarimellaPosted on 2009-10-07 at 11:45:31ID: 25518509

Ok, here is the entire code. I am dynamically populating a Combo select box. The values you select come into the demoArray. See code

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
<%
sub demoNameSub()
    if not isObject("objRS") then
        set objRS=Server.CreateObject("ADODB.RecordSet")
    end if
    
	
    strSQL = "SELECT demoName, link FROM D_mainDemo ORDER BY demoName"
    'objRS.Open strSQL,objConnection,1,3
	
	set objRS = GetDBConnection().execute(strSQL)
	
	'Response.Write("<option>Select Demos</option>" & VBCRLF )
    do while not objRS.EOF
		
		vdemoName= objRS("demoName")
		vlink = objRS("link")
		
		Response.Write("<option"  & ">" & vdemoName & "</option>" & VBCRLF )		 
        objRS.MoveNext
    loop
	'Response.Write("<option>Select All</option>" & VBCRLF )
    objRS.Close
    set objRS=Nothing
	
end sub
 
 
%>
<FORM action="demos.asp" method=POST id="form1" name="form1" >
 
<table>
	
	<tr>
    	<td class="selectClass">
        	<SELECT name="demo" style="width:265px; height:400px;" multiple>
    			<%call demoNameSub%>
                
			</SELECT><br>
		</td>
    </tr>
 
</table>
 
<p><INPUT type="submit" name="Submit" value="Submit Demo" onClick="submitDemo();"></p>
 
</FORM>
</div>
<%
	IF Request.Form("submit") = "Submit Demo" THEN
        
		vdemoName = Request.Form("demo") ' Get the currently selected value for the Question Pulldown 
		
		demoArray = Split(vdemoName,",")
 
		demoValues = join(demoArray, ",")
		
 
		
		Response.Write("Demo values Selected > " & demoValues & "<BR>")
		
		strSQL =  "SELECT demoName, Link, width, height, Description FROM D_MainDemo WHERE demoName IN ('" & Join(demoArray, "','") & "')"
	
		
		
		'strSQL = "SELECT Distinct demoName, Link FROM D_MainDemo WHERE demoName IN ('" & Join(Split(vdemoName, ","), "','") & "')"
		
		set objRS1 = GetDBConnection().execute(strSQL)
		
		IF objRS1.EOF THEN
            Response.Write("No URL/Demo")
        ELSE
			DO WHILE NOT objRS1.EOF
				vLink = objRS1("Link")
				vDemoName = objRS1("demoName")
				Response.Write(vLink) & "<BR>"
				'Response.Write(vDemoName)
			
			objRS1.MoveNext
            LOOP
			objRS1.Close
    		set objRS=Nothing
		END IF
	END IF
 
GetDBConnection().close
'set objConnection = Nothing
%>
</html>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:

Select all Open in new window

by: technofilePosted on 2009-10-07 at 12:28:52ID: 25518999

well i can not run your code without all the rest of it. You are obviously having a problem with what you are trying to feed into the split command dynamically. Try replacing your dynamic with the code below
I am sure that will work, if it does try to do some response.write with your vDemoName variable and post what it spits out so we can get to the bottom of what is going on.

demoArray = "xyz,abc,def"
splitArray=split(demoArray, ",")
strSQL = "SELECT Distinct demoName, Link FROM D_MainDemo WHERE demoName IN ('" & Join(splitArray, "','") & "')"

1:
2:
3:
4:

Select all Open in new window

by: technofilePosted on 2009-10-16 at 11:07:27ID: 25591849

Where you able to get it fixed? If not I would suggest moving your Response.Write up to before the split command and print out vdemoarray so we know exactly what is being fed into your split. Once we get the vdemoarry set correctly the code I wrote will work. There must be some character messing up the string or something. Let me know what comes out.

by: rgarimellaPosted on 2009-10-16 at 12:24:35ID: 25592493

Thx, let me get back to you

by: rgarimellaPosted on 2009-10-19 at 08:28:59ID: 25606074

Let me start a new question so as not to confuse anyone

Question

Need help writing SQL Query in ASP

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Active Server Pages (ASP)

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press