Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Jun 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Variable in SQL Where Clause?? help

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

    Code:
    <% 
    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!!

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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:

    Code:
    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!
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    New to the CF scene
    Join Date
    Jun 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!
    Last edited by BabbleFish; 06-26-2007 at 03:10 PM.

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    I'm going to use the one I sent you, as I don't have the updated version

    Code:
    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), 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.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #5
    New to the CF scene
    Join Date
    Jun 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Code:
    <% 
    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!!!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •