search for SQL 'date' field not retieving data


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: search for SQL 'date' field not retieving data

  1. #1
    Aaron Longnion Guest

    search for SQL 'date' field not retieving data


    Using ASP, SQL Server 7, and html text forms for a search.

    1.) the ASP page works perfectly for the first five forms, and the "EXP_DT"
    field is used in one of those forms. The info. from EXP_DT field is displayed
    perfectly
    there, but not for the final form, which is supposed to be displayed from
    the following code:

    'Go to the sixth form
    ElseIf Request("EXP_DT") <> "" Then

    'Create a recordset object instance, and excecute the SQL statement
    Set rsEXP_DT = Server.CreateObject("ADODB.Recordset")
    rsEXP_DT.Open strEXP_DTSQL, connSearch

    'Determine whether there are any matching records in rsEXP_DT
    If rsEXP_DT.EOF then
    'No records were returned for the characters entered by the user
    Response.Write "There are no people whose License expires on " & strEXP_DT
    Else
    'There are one or more EXP_DTs that match the user's input, so display
    those numbers and corresponding
    'fields
    Response.Write "<B>Information for people whose License expires on " &
    strEXP_DT & ":</B><BR><BR>"
    Do While Not rsEXP_DT.EOF
    Response.Write "<B>Name: </B>" & rsEXP_DT("Last") & ", " &_
    rsEXP_DT("First")& " " & rsEXP_DT("Mid") & "<br>" &_
    "<B>UID: </B>" & "<A HREF=""details.asp?uid=" _
    & rsEXP_DT("UID") _
    & """>" _
    & rsEXP_DT("UID") & "<br>" _
    & "</A>" & "<B>EXP_DT: </B>" & rsUID("EXP_DT") & "<BR><HR><BR><br>"

    'check everyone in the field
    rsEXP_DT.MoveNext
    Loop
    End If

    'Closing out the recordset
    rsEXP_DT.Close
    Set rsEXP_DT = Nothing

    2.) However, when I actually enter some numbers, or dates (yes, I typed them
    correctly), then the response from the ASP page is always : " There are
    no people
    whose License expires on (whatever date or number I type into the form)".
    The code is copied and pasted from the previous code, and I never get any
    errors when
    I run the searches. The text box on my html page is named correctly (as
    "EXP_DT"), and I've checked for typsos about 40 times. What am I missing?
    The entire
    code follows:

    <%@LANGUAGE="VBSCRIPT"%> <%

    set connSearch = Server.CreateObject("ADODB.Connection")
    connSearch.ConnectionString = "dsn=TFSCSQL;uid=tfsc;pwd=tfsc;"
    connSearch.Open

    'Read in the characters the user entered into text fields
    strLastName = Trim(Request("LastName"))
    strUID = Trim(Request("UID"))
    strCRED_NO = Trim(Request("CRED_NO"))
    strBUSINESS = Trim(Request("BUSINESS"))
    strPROFESSION = Trim(Request("PROFESSION"))
    strEXP_DT = Trim(Request("EXP_DT"))

    strLastSQL = "SELECT UID, Last, First, Mid, CRED# FROM dbo.T_PEOPLE_CRED
    WHERE Last LIKE '" & strLastName & "%' ORDER BY UID"

    strUIDSQL = "SELECT UID, Last, First, Mid, BUSINESS, SSN, Add1, City, ST,
    Zip, Ph1, Fax, DOB, SEX, PROFESSION, CRED#, STATUS, EXP_DT
    FROM dbo.T_PEOPLE_CRED WHERE UID LIKE '" & strUID & "%' ORDER BY UID"

    strCRED_NOSQL = "SELECT UID, Last, First, Mid, CRED# FROM dbo.T_PEOPLE_CRED
    WHERE CRED# LIKE '" & strCRED_NO & "%' ORDER BY
    Last"

    strBUSINESSSQL = "SELECT UID, Last, First, Mid, BUSINESS FROM dbo.T_PEOPLE_CRED
    WHERE BUSINESS LIKE '" & strBUSINESS & "%'
    ORDER BY Last"

    strPROFSQL = "SELECT UID, Last, First, Mid, PROFESSION FROM dbo.T_PEOPLE_CRED
    WHERE PROFESSION LIKE '" & strPROFESSION & "%'
    ORDER BY Last"

    strEXP_DTSQL = "SELECT UID, Last, First, Mid, EXP_DT FROM dbo.T_PEOPLE_CRED
    WHERE EXP_DT LIKE '" & strEXP_DT & "%' ORDER BY
    Last"


    'Check to see if LastName text field was filled, if not go to next form
    If Request("LastName") <> "" Then

    'Create a recordset object instance, and excecute the SQL statement
    Set RSName = Server.CreateObject("ADODB.Recordset")
    RSName.Open strLastSQL, connSearch

    'Determine whether there are any matching records in RSName
    If RSName.EOF then
    'No records were returned for the characters entered by the user
    Response.Write "There are no last names that begin with " &_
    UCase(strLastName)
    Else
    'There are one or more last names that match the user's input, so display
    those last names and corresponding
    'fields
    Response.Write "<B>Information for people with last names that begin with
    " &_
    UCase(strLastName) & ":</B><BR><BR>"
    Do While Not RSName.EOF
    Response.Write "<B>Name: </B>" & RSName("Last") & ", " &_
    UCase(RSName("First")) & " " & RSName("Mid") & "<br>" &_
    "<B>UID: </B>" & "<A HREF=""details.asp?uid=" _
    & RSName("UID") _
    & """>" _
    & RSName("UID") & "<br>" _
    & "</A>" & "<B>CRED#: </B>" & RSName("CRED#") & "<BR><HR><BR><br>"

    'move to the next person in the list
    RSName.MoveNext
    Loop
    End If

    'Closing out the recordset
    RSName.Close
    Set RSName = Nothing

    'Go to the next form
    ElseIf Request("UID") <> "" Then

    'Create a recordset object instance, and excecute the SQL statement
    Set rsUID = Server.CreateObject("ADODB.Recordset")
    rsUID.Open strUIDSQL, connSearch

    'Determine whether there are any matching records in rsUID
    If rsUID.EOF then
    'No records were returned for the characters entered by the user
    Response.Write "There are no UIDs that begin with " &_
    UCase(strUID)
    Else
    'There are one or more UIDs that match the user's input, so display those
    UIDs and corresponding
    'fields
    Response.Write "<B>Associated information for people with UIDs that begin
    with " &_
    UCase(strUID) & ":</B><BR><BR>"
    Do While Not rsUID.EOF
    Response.Write "<B>UID: </B>" & rsUID("UID") & "<BR>" &_
    "<B>Name: </B>" & rsUID("First") & " " &_
    rsUID("Mid") & " " &_
    rsUID("Last") & "<br>" &_
    "<B>Business: </B>" & rsUID("BUSINESS") & "<br>" &_
    "<B>S.S.N.: </B>" & rsUID("SSN") & "<BR>" &_
    "<B>Address: </B>" & rsUID("Add1") & "<br>" &_
    "<B>City, ST, ZIP: </B>" & rsUID("City") & ", " &_
    rsUID("ST") & " " &_
    rsUID("Zip") & "<BR>" &_
    "<B>Phone/Fax: </B>" & rsUID("Ph1") & "/ (Fax)" &_
    rsUID("Fax") & "<BR>" &_
    "<B>D.O.B.: </B>" & rsUID("DOB") & "<BR>" &_
    "<B>Gender: </B>" & rsUID("SEX") & "<BR>" &_
    "<B>Profession: </B>" & rsUID("PROFESSION") & "<BR>" &_
    "<B>Status: </B>" & rsUID("STATUS") & "<BR>" &_
    "<B>Exp. Date: </B>" & rsUID("EXP_DT") & "<BR>" &_
    "<B>CRED#: </B>" & rsUID("CRED#") & "<BR><br><HR><BR>"

    'move to the next customer
    rsUID.MoveNext
    Loop
    End If

    'Clean up ADO objects
    rsUID.Close
    Set rsUID = Nothing

    'Go to the third form
    ElseIf Request("CRED_NO") <> "" Then

    'Create a recordset object instance, and excecute the SQL statement
    Set rsCRED_NO = Server.CreateObject("ADODB.Recordset")
    rsCRED_NO.Open strCRED_NOSQL, connSearch

    'Determine whether there are any matching records in rsCRED_NO
    If rsCRED_NO.EOF then
    'No records were returned for the characters entered by the user
    Response.Write "There are no CRED#s that begin with " &_
    UCase(strCRED_NO)
    Else
    'There are one or more CRED#s that match the user's input, so display
    those numbers and corresponding 'fields
    Response.Write "<B>Information for people with CRED#s that begin with
    " &_
    UCase(strCRED_NO) & ":</B><BR><BR>"
    Do While Not rsCRED_NO.EOF
    Response.Write "<B>Name: </B>" & rsCRED_NO("Last") & ", " &_
    UCase(rsCRED_NO("First")) & " " & rsCRED_NO("Mid") & "<br>"
    &_
    "<B>UID: </B>" & "<A HREF=""details.asp?uid=" _
    & rsCRED_NO("UID") _
    & """>" _
    & rsCRED_NO("UID") & "<br>" _
    & "</A>" & "<B>CRED#: </B>" & rsCRED_NO("CRED#") & "<BR><HR><BR><br>"

    'move to the next person in the list
    rsCRED_NO.MoveNext
    Loop
    End If

    'Closing out the recordset
    rsCRED_NO.Close
    Set rsCRED_NO = Nothing

    'Go to forth field
    ElseIf Request("BUSINESS") <> "" Then

    'Create a recordset object instance, and excecute the SQL statement
    Set rsBUSINESS = Server.CreateObject("ADODB.Recordset")
    rsBUSINESS.Open strBUSINESSSQL, connSearch

    'Determine whether there are any matching records in rsBUSINESS
    If rsBUSINESS.EOF then
    'No records were returned for the characters entered by the user
    Response.Write "There are no Business names that begin with " &_
    UCase(strBUSINESS)
    Else
    'There are one or more BUSINESSs that match the user's input, so display
    those numbers and corresponding 'fields
    Response.Write "<B>Information for people whose Business Names begin with
    " &_
    UCase(strBUSINESS) & ":</B><BR><BR>"
    Do While Not rsBUSINESS.EOF
    Response.Write "<B>Name: </B>" & rsBUSINESS("Last") & ", " &_
    UCase(rsBUSINESS("First")) & " " & rsBUSINESS("Mid") & "<br>"
    &_
    "<B>UID: </B>" & "<A HREF=""details.asp?uid=" _
    & rsBUSINESS("UID") _
    & """>" _
    & rsBUSINESS("UID") & "<br>" _
    & "</A>" & "<B>BUSINESS: </B>" & rsBUSINESS("BUSINESS") &
    "<BR><HR><BR><br>"

    'move to the next person in the list
    rsBUSINESS.MoveNext
    Loop
    End If

    'Closing out the recordset
    rsBUSINESS.Close
    Set rsBUSINESS = Nothing

    'Go to the fifth field
    ElseIf Request("PROFESSION") <> "" Then

    'Create a recordset object instance, and excecute the SQL statement
    Set rsPROFESSION = Server.CreateObject("ADODB.Recordset")
    rsPROFESSION.Open strPROFSQL, connSearch

    'Determine whether there are any matching records in rsPROFESSION
    If rsPROFESSION.EOF then
    'No records were returned for the characters entered by the user
    Response.Write "There are no people with Professions that begin with "
    &_
    UCase(strPROFESSION)
    Else
    'There are one or more PROFESSIONs that match the user's input, so
    display those numbers and corresponding 'fields
    Response.Write "<B>Information for people whose Profession names begin
    with " &_
    UCase(strPROFESSION) & ":</B><BR><BR>"
    Do While Not rsPROFESSION.EOF
    Response.Write "<B>Name: </B>" & rsPROFESSION("Last") & ", " &_
    UCase(rsPROFESSION("First")) & " " & rsPROFESSION("Mid") & "<br>"
    &_
    "<B>UID: </B>" & "<A HREF=""details.asp?uid=" _
    & rsPROFESSION("UID") _
    & """>" _
    & rsPROFESSION("UID") & "<br>" _
    & "</A>" & "<B>PROFESSION: </B>" & rsPROFESSION("PROFESSION")
    & "<BR><HR><BR><br>"

    'move to the next person in the list
    rsPROFESSION.MoveNext
    Loop
    End If

    'Closing out the recordset
    rsPROFESSION.Close
    Set rsPROFESSION = Nothing

    'Go to the sixth field
    ElseIf Request("EXP_DT") <> "" Then

    'Create a recordset object instance, and excecute the SQL statement
    Set rsEXP_DT = Server.CreateObject("ADODB.Recordset")
    rsEXP_DT.Open strEXP_DTSQL, connSearch

    'Determine whether there are any matching records in rsEXP_DT
    If rsEXP_DT.EOF then
    'No records were returned for the characters entered by the user
    Response.Write "There are no people whose License expires on " & strEXP_DT
    Else
    'There are one or more EXP_DTs that match the user's input, so display
    those numbers and corresponding 'fields
    Response.Write "<B>Information for people whose License expires on " &
    strEXP_DT & ":</B><BR><BR>"
    Do While Not rsEXP_DT.EOF
    Response.Write "<B>Name: </B>" & rsEXP_DT("Last") & ", " &_
    rsEXP_DT("First")& " " & rsEXP_DT("Mid") & "<br>" &_
    "<B>UID: </B>" & "<A HREF=""details.asp?uid=" _
    & rsEXP_DT("UID") _
    & """>" _
    & rsEXP_DT("UID") & "<br>" _
    & "</A>" & "<B>EXP_DT: </B>" & rsUID("EXP_DT") & "<BR><HR><BR><br>"

    'check everyone in the field
    rsEXP_DT.MoveNext
    Loop
    End If

    'Closing out the recordset
    rsEXP_DT.Close
    Set rsEXP_DT = Nothing

    'Closing larger If statement
    End If


    connSearch.Close
    Set connSearch = Nothing
    %>
    <html>
    <head>
    <title>Results of the Search</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body bgcolor="#FFFFFF"><base target="main">

    </body>
    </html>



    Share on Google+

  2. #2
    Scott Mitchell Guest

    Re: search for SQL 'date' field not retieving data


    Sorry, but I am here to answer questions on ASP design. To get help with
    your SQL query problems, let me suggest two sites:

    * The Database Forum on ASPMessageboard.com
    http://www.aspmessageboard.com/forum/databases.asp

    * A Database-related ListServ on ASPLists.com
    http://www.asplists.com/asplists/database.asp

    Happy Programming!


    Scott Mitchell
    mitchell@4guysfromrolla.com
    http://www.4GuysFromRolla.com
    http://www.ASPMessageBoard.com
    http://www.ASPFAQs.com

    * When you think ASP, think 4GuysFromRolla.com!


    Share on Google+

  3. #3
    Glen Kunene Guest

    Re: search for SQL 'date' field not retieving data


    You also may find the help you need right here in one of the DevX forums:

    * SQL development
    http://news.devx.com/cgi-bin/dnewswe...enterprise.sql




    "Scott Mitchell" <mitchell@4guysfromrolla.com> wrote:
    >
    >Sorry, but I am here to answer questions on ASP design. To get help with
    >your SQL query problems, let me suggest two sites:
    >
    > * The Database Forum on ASPMessageboard.com
    > http://www.aspmessageboard.com/forum/databases.asp
    >
    > * A Database-related ListServ on ASPLists.com
    > http://www.asplists.com/asplists/database.asp
    >
    >Happy Programming!
    >
    >
    > Scott Mitchell
    > mitchell@4guysfromrolla.com
    > http://www.4GuysFromRolla.com
    > http://www.ASPMessageBoard.com
    > http://www.ASPFAQs.com
    >
    >* When you think ASP, think 4GuysFromRolla.com!
    >
    >


    Share on Google+

Similar Threads

  1. Replies: 0
    Last Post: 05-24-2002, 07:21 AM
  2. Replies: 2
    Last Post: 01-19-2001, 08:31 PM
  3. date comparisons in SQL
    By Blair in forum VB Classic
    Replies: 19
    Last Post: 11-13-2000, 03:12 PM
  4. Errors Updading Date field to Null in Acces 97
    By Michael Z. in forum VB Classic
    Replies: 0
    Last Post: 05-17-2000, 05:16 PM
  5. multiple keyword search to display to data grid control
    By Will Storer in forum VB Classic
    Replies: 1
    Last Post: 04-19-2000, 05:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center