SQL query and ANY (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

xterradane -> SQL query and ANY (12/13/2001 12:39:29)

I am trying to create a query (Acess db) that will do the following:
1. Say you have two drop down boxes, one is state the other job type.
2. On either of them the person can choose ANY/ALL rather than picking just one.

I know how to do a query if they just pick a single item, but I was thinking something of using If Thens for the different scenerios, can this be done? I tried it last night, but did not have any luck.. Don't have the code with me know, but if you want to look at it I can post it tongiht.


 



xterradane -> RE: SQL query and ANY (12/13/2001 12:46:20)

PS, How would I do it if I set it up that they could select multiple state or jobs with a limit of course to 3 or 4 selections.

Gail

 



rdouglass -> RE: SQL query and ANY (12/13/2001 13:27:50)

I'm not positive, but I believe you use the value of "%" as a wildcard in Access queries. Try setting the value of the "ANY/ALL" menu selection to "%" (without the quotes).

 

Edited by - rdouglass on 12/13/2001 13:42:12



rdouglass -> RE: SQL query and ANY (12/13/2001 13:40:14)

As to your second question, I'm not as good as others so I resort to the 'brute force' method. I build my queries into a variable (using If-Then's to create my query AND's) then call the variable when I'm ready for my query. Something like:

mySQL = ""
If Request("myField1") > "" then
mySQL = mySQL & "(myField1 = '::myField1::')"
end if

If Request("myField2") > "" then

'Check for previous criteria in mySQL
'If there is and need to add more criteria
'to mySQL, add " AND "
If mySQL > " " then
mySQL = mySQL & " AND "
End If

mySQL = mySQL & "(myField2 = '::myField2::')"
end if

etc.

Then use something like:

"SELECT * FROM Main WHERE (" & mySQL & ")"

for your query.

If no one has given you any better pointers, let me know and I'll show you an example of one I've done before.

 



xterradane -> RE: SQL query and ANY (12/13/2001 17:33:03)

Here is the code I am using, it is returning either the wrong records or says there are no records to return if I have any chosen:
Dim strJobType, strLocation
strJobType = Request("JobType")
strState = Request("State")

Dim rsJob

If Request("JobType") <> "Any" Then
strSQL ="SELECT * FROM Job WHERE DateExpires>=Date() AND State='" & strState & "' ORDER BY DatePosted DESC;"
End If

If Request("strState") <> "Any" Then
strSQL= "SELECT * FROM Job WHERE DateExpires>=Date() AND JobType='" & strJobType & "' ORDER BY DatePosted DESC;"
Else
strSQL= "SELECT * FROM Job WHERE DateExpires>=Date() AND JobType='" & strJobType & "' AND State='" & strState & "' ORDER BY DatePosted DESC;"
End If
Set rsJob = Server.CreateObject("ADODB.Recordset")
rsJob.Open strSQL, objConn


 



Spooky -> RE: SQL query and ANY (12/13/2001 22:24:19)

Try strSQL= "SELECT * FROM Job WHERE DateExpires>=#"&Date()&"# AND JobType....

Also, to check your string, do this :

End If

response.write strSQL
response.end
Set rsJob = Server.CreateObject("ADODB.Recordset")

§þððk¥
Database / DRW Q & A
VP-ASP Shopping cart



davebukouricz -> RE: SQL query and ANY (12/13/2001 22:35:39)

or
strSQL= "SELECT * FROM Job WHERE DateExpires>=Date() AND JobType LIKE '" & strJobType & "' ORDER BY DatePosted DESC;"
passing % as the variable from the dropdown "all" value
May just work


***********************
ASP Rocks!
Dave



xterradane -> RE: SQL query and ANY (12/14/2001 12:34:46)

I am an idiot, I figured this out I needed to use an equals sign instead of <>. I just was half asleep when I was doing this one... Thanks for all your help and input...

Dim strJobType, strLocation
strJobType = Request("JobType")
strState = Request("State")

Dim rsJob

If Request("JobType") = "Any" Then
strSQL ="SELECT * FROM Job WHERE DateExpires>=Date() AND State='" & strState & "' ORDER BY DatePosted DESC;"
End If

If Request("strState") = "Any" Then
strSQL= "SELECT * FROM Job WHERE DateExpires>=Date() AND JobType='" & strJobType & "' ORDER BY DatePosted DESC;"
Else
strSQL= "SELECT * FROM Job WHERE DateExpires>=Date() AND JobType='" & strJobType & "' AND State='" & strState & "' ORDER BY DatePosted DESC;"
End If
Set rsJob = Server.CreateObject("ADODB.Recordset")
rsJob.Open strSQL, objConn

 



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.2070313