search for SQL 'date' field not retieving data

[Aaron Longnion]

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>

[Scott Mitchell]

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!

[Glen Kunene]

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!
>
>