Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    27

    Unanswered: Not printing to screen

    Based on the SQl statement below, I want to print out the results of the recordset. However, nothing is printing to the screen even though there are definitely results for the query. If I remove the following line (MEMBER.TODAYS_DATE Between " & viewDate1 & " And " & viewDate2 & "") from the SQL statement, the results print out.
    Can anyone help me please???


    Code:
    <!--#INCLUDE FILE="Connect2DB.asp"-->
    <% 
    
    dim newMemSQL 		
    dim rsNewMem 	
    
    Dim viewDate1, viewDate2
    viewDate1 = Request.Form("date1")
    viewDate2 = Request.Form("date2")
    
    Dim status
    status = "Approved"
     
    newMemSQL = "SELECT MEMBER_ID, TITLE, FIRST_NAME, M_INITIAL, SURNAME, COMMENTS FROM MEMBER Where MEMBER.APP_STATUS = '" & status & "' And MEMBER.TODAYS_DATE Between " & viewDate1 & " And " & viewDate2 & ""
    
    Set rsNewMem= Server.CreateObject("ADODB.Recordset")
    rsNewMem.CursorType = 2
    rsNewMem.LockType = 3
    rsNewMem.Open newMemSQL, adoCon
    
    Do While NOT rsNewMem.EOF 
           Response.Write (rsNewMem(0) & " ") 
           Response.Write (rsNewMem(1) & " ") 
           Response.Write (rsNewMem(2) & " ") 
           Response.Write (rsNewMem(3) & " ") 
           Response.Write (rsNewMem(4) & "</br>") 
           Response.write("<b>Comment: </b>") 
           Response.Write (rsNewMem(5) & " ") 
           Response.Write("<BR><HR>") 
    
           'Move to the next record in the recordset 
           rsNewMem.MoveNext 
    Loop   
    
    rsNewMem.close 
    set rsNewMem = nothing 
    adoCon.close 
    %>

  2. #2
    Join Date
    Jan 2004
    Location
    Bermuda
    Posts
    40
    lklegend:

    Try putting pound signs "#" in the Select string around the Date values.

    RobbieD
    Sunny Times...

  3. #3
    Join Date
    Jan 2004
    Posts
    27
    Cheers Robbie but no good I'm afraid - tried a few variations with #,' & " but to no avail. Any other suggestions??
    Thanks

    Originally posted by robbied111
    lklegend:

    Try putting pound signs "#" in the Select string around the Date values.

    RobbieD

  4. #4
    Join Date
    Jan 2004
    Location
    Bermuda
    Posts
    40
    OK lklegend...

    Make sure date1 is LESS than/Before Date2.

    Robbie D
    Sunny Times...

  5. #5
    Join Date
    Jan 2004
    Posts
    27
    Ya, the dates are right also! It must be somthing to do with the tags around 'viewDate1' and 'viewDate2' I think as it works when this part of the SQL statement is removed.....I just don't know what it is!

    Originally posted by robbied111
    OK lklegend...

    Make sure date1 is LESS than/Before Date2.

    Robbie D

  6. #6
    Join Date
    Jan 2004
    Location
    Bermuda
    Posts
    40

    Ooops

    I have to change my resolution!!! I didn't see the quotes!

    The Dates need single-double-ampersand -> ' " & (without the spaces)

    ...MEMBER.TODAYS_DATE Between '" & viewDate1 & "' And '" & viewDate2 & "'"


    Sorry - I ran you around there a bit!
    Sunny Times...

  7. #7
    Join Date
    Jan 2004
    Posts
    27

    Re: Ooops

    Robbie,

    Sorry to be the bearer of bad news but I'm afraid doesn't work either!
    I did as you suggested:
    Code:
    newMemSQL = "SELECT MEMBER_ID, TITLE, FIRST_NAME, M_INITIAL, SURNAME, COMMENTS FROM MEMBER Where MEMBER.APP_STATUS = '" & status & "' And MEMBER.TODAYS_DATE Between '"&viewDate1&"' And '"&viewDate2&"'"
    However, I got the following error:
    Data type mismatch in criteria expression.

    Any other suggestions.....
    Cheers*

Posting Permissions

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