...

View Full Version : Using Variable in SQL Where Clause?? help



BabbleFish
06-26-2007, 05:42 AM
I having troubling using a variable declared in my asp page as part of my SQL statement. I am attempting to pull results from a database table based on two criteria into a drop down menu.

Criteria 1: Date
Criteria 2: Call type ( ex. Care, Techsupport, etc.)



<%
Dim oRs
Dim SQL1
Dim ID1
Dim ID2
Dim dsql

ID1=request.querystring("calld")
ID2=request.querystring("plob")
dsql=FormatDateTime(ID1,2)


Set oRs = Server.CreateObject("ADODB.Recordset")
SQL1 = "SELECT RCAT_CALL_REASONS.RCAT_code, RCAT_CALL_REASONS.definition, RCAT_CALL_REASONS.start_date, RCAT_CALL_REASONS.end_date, RCAT_CALL_REASONS.TechSupport_Specific FROM RCAT_CALL_REASONS WHERE RCAT_CALL_REASONS.start_date < '# & dsql & #'" AND RCAT_CALL_REASONS.end_date > '# & dsql #' AND RCAT_CALL_REASONNS.TechSupport_Specific = IIf('" & ID2 & "'="Technical Services",-1,0) Or (RCAT_CALL_REASONS.TechSupport_Specific)=IIf('" & dsql & "'="Technical Services",0,0))) ORDER BY RCAT_CALL_REASONS.RCAT_code;

oRs.Open SQL1, Connection
Do Until oRs.EOF %>
<option value="<%=oRs("RCAT_code")%>"><%=oRs("RCAT_code")%></option>
<%oRs.MoveNext
Loop

oRs.Close
Set oRS = Nothing
%>


The connection is managed early in the page so it is not included here.

The drop down list will populate without the WHERE clause but returns an empty drop down list when added.

Any suggestions or insight into why would be greatly appreciated!!

Daemonspyre
06-26-2007, 01:29 PM
Hi Babblefish--

Couple of things I see (and they may be a cut/copy and paste issue):

1) No trailing " on the end of your SQL code.

2) Extra " inside your WHERE. '# & dsql & #' " AND
------------------------------------------^

3) RCAT_CALL_REASONNS is misspelled at ... AND RCAT_CALL_REASONNS.TechSupport_Specific = IIf...
-------------------------------------------------------------------^

4) Try using Aliases. Would make your SQL statement 1/2 the length.

Here's the revised SQL statement:


SQL1 = "SELECT rcr.RCAT_code, rcr.definition, rcr.start_date, rcr.end_date, rcr.TechSupport_Specific FROM RCAT_CALL_REASONS AS 'rcr' WHERE rcr.start_date < '# & dsql & #' AND rcr.end_date > '# & dsql #' AND rcr.TechSupport_Specific = IIf('" & ID2 & "'='Technical Services',-1,0) OR (rcr.TechSupport_Specific)=IIf('" & dsql & "'='Technical Services',0,0))) ORDER BY rcr.RCAT_code"

Try doing a response.write(SQL1) response.flush and see what you get. More than likely there is a variable that is not being passed and it is screwing up either the SQL string OR not matching any of your requirements.

Also, try substituting some variables and putting it into your SQL server and see what it returns.

HTH!

BabbleFish
06-26-2007, 03:07 PM
Daemonspyre,
Thank you for your response. I have corrected the missing double quotes and typo's in my script.
Also, the where statement will work if I hard code some of the requirements.....Where start_date < #07-01-2007# AND end_date >= #07-01-2007#

The where statement is still breaking my page. The scripting appears to stop during the drop down list creation process. Nothing after this section of code is processed.

I'm not sure how to use the response.write(SQL1) flush.response method....can you walk me through it?

Is there an easier way to create a dynamic drop down list base on two criteria that is passed from another page, with javascript. The drop down list could have a hundred plus options listed and I dread having to hard code an array.

Example:

Criteria 1 = date of call
Criteria 2 = type of call recorded

Database has 4 fields:
1. Listing for dropw down list
2. start date
3. end date
4. call type

Create drop down list with options that match following conditions

If (start date < Criteria 1 AND end date > criteria 1) AND Criteria 2="Tech Support" then
List all items where (start date < Criteria 1 and end date > criteria 1) and Call type=techsupport
Else
List all items where (start date < Criteria 1 and end date > criteria 1) and Call type<>techsupport

Thanks again!

Daemonspyre
06-26-2007, 04:48 PM
I'm going to use the one I sent you, as I don't have the updated version :)



SQL1 = "SELECT rcr.RCAT_code, rcr.definition, rcr.start_date, rcr.end_date, rcr.TechSupport_Specific FROM RCAT_CALL_REASONS AS 'rcr' WHERE rcr.start_date < '# & dsql & #' AND rcr.end_date > '# & dsql #' AND rcr.TechSupport_Specific = IIf('" & ID2 & "'='Technical Services',-1,0) OR (rcr.TechSupport_Specific)=IIf('" & dsql & "'='Technical Services',0,0))) ORDER BY rcr.RCAT_code"

response.write SQL1
response.flush
schtopp


Running the above WILL BREAK YOUR PAGE! However, that's exactly what we want. We want to see what is being passed to your SQL server.

If anything in your SQL statement is wrong, this is the way that we can see it so that we can fix it.

One last edit ( and I just now saw it ) - Too many parentheses in your SQL. Try adding one before the rcr.Tech_Support_Specific or remove one before the ORDER BY.

LAST THING (I swear)... If you are using IE (shame on you, use Firefox (http://www.getfirefox.com)), go to TOOLS > INTERNET OPTIONS > ADVANCED tab > UNcheck Show Friendly URLs and Show Friendly HTTP Error messages. That will remove the ASP 500 error page and actually tell you what's wrong.

Let's make sure the SQL string is correct before we concern ourselves with the dropdown options.

BabbleFish
06-26-2007, 06:41 PM
Daemonspyre

It appears to be working now.

The problem had to do with the IIF functions in the WHERE clause. I'm don't know why, maybe there are issues with passing this function through an ADODB connection to an Access DB. There could have been some formating issues with my dsql and fsql variables (both dates).

I'll be sure to use the response.write() flush.response feature in the future!!!

Here is an example of my solution below.




<%
Dim oRs
Dim SQL1

If dsql >= fsql and ID2<>"Simple Tech Support" OR ID2<>"Complex Tech Support" Then
SQL1 = "Select * From RCAT_CALL_REASONS Where start_date >= #2007-05-01# AND end_date > #2007-07-01# AND TechSupport_Specific = 0 ORDER BY RCAT_code"
End If

If dsql >= fsql and ID2="Simple Tech Support" OR ID2="Complex Tech Support" Then
SQL1 = "Select * From RCAT_CALL_REASONS Where start_date >= #2007-05-01# AND end_date > #2007-07-01# ORDER BY RCAT_code"
End If

If dsql < fsql Then
SQL1 = "Select * From RCAT_CALL_REASONS Where start_date = #2007-05-01# AND end_date >= #2007-07-01# AND TechSupport_Specific = 0 ORDER BY RCAT_code"
End If

Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open SQL1, Connection
Do Until oRs.EOF %>
<option value="<%=oRs("RCAT_code")%>"><%=oRs("RCAT_code")%></option>
<%oRs.MoveNext
Loop

oRs.Close
Set oRS = Nothing
%>


Thank you for all of your help!!!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum