Searching Datetime from ASP

13 views
Skip to first unread message

Matt

unread,
Mar 1, 2003, 2:08:18 PM3/1/03
to
Bob:
The Response.Write statement shows that everything is being transfered and
pulled in correctly using Request.Form.

Using SQL Profiler, the clid and dob are always null.

When I actually run the SP from the page, it always returns with "no
records" if the SP has @variable is null. If I change it to @variable = ''
the fname, lname and ssn searches work correctly.

ALTER PROCEDURE usp_search
@fname varchar (25),
@lname varchar (30),
@clid int,
@dob datetime,
@ssn varchar (11)

AS
BEGIN
Select strlnamecl, strfnamecl, intclid, datdob, strssn from tblcontacts
WHERE (strlnamecl like @lname+'%' or @lname is null) and
(strfnamecl like @fname+'%' or @fname is null) and
(intclid = coalesce(@clid,intclid)) and
(datdob = coalesce(@dob,datdob)) and
(strssn = @ssn or @ssn is null)
ORDER BY strlnamecl, strfnamecl, intclid
END
RETURN

Thanks,
Matt

"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:uCCW4E$3CHA...@TK2MSFTNGP11.phx.gbl...
> Hmm, I just noticed: about 20 posts ago, we strayed away from the topic of
> sqlserver programming to asp programming. This discussion should be
> continued over at .inetserver.asp.db.
>
> (More below)
>
> "Matt" <mes...@austin.rr.com> wrote in message
> news:8tT7a.47315$O41.1...@twister.austin.rr.com...
> > rows. This is all seems to be working fine now, but there is still a
> > problem. Searching by fname, lname and ssn works fine, but if you
search
> > by
> > dob or clid it returns all records instead of just the matching records.
> > I
> > noticed that you had told me to put code in here:
> > "If len(dDOB) = 0 then
> > dDOB = Null
> > else
> > 'put some code here to put dDOB in yyyymmdd format"
>
> I take that back. You need to convert it to yyyy-mm-dd format in order for
> ado to recognize it as a datetime. Here's a quick routine to do that:
>
> function CDateToISO(pDate)
> dim sDate
> if isdate(pDate) then
> sDate = year(pDate)
> if Month(pDate) < 10 then
> sDate = sDate & "-0" & Month(pDate)
> else
> sDate = sDate & "-" & Month(pDate)
> end if
> if Day(pDate) < 10 then
> sDate = sDate & "-0" & Day(pDate)
> else
> sDate = sDate & "-" & Day(pDate)
> end if
> CDateToISO = sDate
> else
> response.write pDate & " does not seem to be a date"
> CDateToISO = pDate
> end if
> end function
>
> > I also don't
> > understand why the clid search is returning everything.
>
> Perhaps the clid value is not being passed as you thought it was. You can
> use SQL Profiler to see the actual call to the database. You can use
> Response.Write lClid to verify that you picked up the value from the form
> variable correctly.
>
> Again, when called from QA, this procedure works correctly, correct? That
> makes it an ASP issue and any further questions on this topic should be
> brought to the .inetserver.asp.db newsgroup so we stop boring all thes
> sqlserver programmers. :-)
>
> Bob Barrows
>
>
>
>
>


Bob Barrows

unread,
Mar 1, 2003, 3:08:07 PM3/1/03
to
The only thing this can mean is that you are not using the correct variable
in the CreateParameter statement again. Here are the relevant statements
from the page I used to successfully test this. Does it match what you have?

dDOB = Request.form("dDOB")
'dDOB = "4/17/1983"


If len(dDOB) = 0 then
dDOB = Null
else

dDOB = CDateToISO(dDOB)
end if
Response.write dDOB
<snip>
set param = .createparameter("@dob", adDBTimeStamp, adParamInput, 0, dDOB)
.parameters.append param

Bob Barrows


"Matt" <mes...@austin.rr.com> wrote in message

news:Cy78a.49028$O41.1...@twister.austin.rr.com...

Matt

unread,
Mar 1, 2003, 5:59:25 PM3/1/03
to
You're correct on that, the form obects are actually just DOB and CLID and
they are now being pulled correctly according to the Profiler. I was able
to change the clid to be a VarChar in the parameter setting and after that
the clid search works. ie:
set param = cmd.CreateParameter("@clid",adVarChar,adParamInput,8,lclid)
.parameters.append param

The Profiler shows the dob correctly, but the dob search always returns "no
record". I'll paste it all hopefully for the last time.

*** Stored Procedure****


ALTER PROCEDURE usp_search
@fname varchar (25),
@lname varchar (30),
@clid int,
@dob datetime,
@ssn varchar (11)

AS


BEGIN
Select strlnamecl, strfnamecl, intclid, datdob, strssn from tblcontacts
WHERE (strlnamecl like @lname+'%' or @lname is null) and
(strfnamecl like @fname+'%' or @fname is null) and
(intclid = coalesce(@clid,intclid)) and
(datdob = coalesce(@dob,datdob)) and
(strssn = @ssn or @ssn is null)
ORDER BY strlnamecl, strfnamecl, intclid
END
RETURN

***client_results.asp***
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="../../../Connections/PIMA.asp" -->
<!--#include file="../../../Connections/adovbs.inc" -->
<%
Dim arResults, cn, cmd, rs
Dim sFname, sLname, lclid, dDOB, sSSN
%>
<%
set cn = Server.CreateObject("ADODB.Connection")
cn.Open = "Provider=SQLOLEDB;" & _
"Data Source=SERVER;" & _
"Initial Catalog=PIMA;" & _
"User Id=sa;" & _
"Password="
%>
<%
sFname = Request.form("fname")
If len(sFname) = 0 then sFname = vbNullString
sLname = Request.form("lname")
If len(sLname) = 0 then sLname = vbNullString
lclid = Request.form("clid")
If len(lclid) = 0 then lclid = vbNullString
dDOB = Request.form("DOB")


If len(dDOB) = 0 then
dDOB = Null

Else
dDOB = CDateToISO(dDOB)
End IF
sSSN = Request.Form("ssn")
If len(sSSN) = 0 then sSSN = vbNullString
%>
<%
set cmd = Server.CreateObject("ADODB.Command")
With cmd
cmd.CommandType = adcmdstoredproc
cmd.CommandText = "usp_search"
cmd.ActiveConnection = cn
set param =
cmd.CreateParameter("RETURN_VALUE",adInteger,adParamReturnValue,0)
.parameters.append param
set param = cmd.CreateParameter("@fname",adVarChar,adParamInput,25,sfname)
.parameters.append param
set param = cmd.CreateParameter("@lname",adVarChar,adParamInput,25,slname)
.parameters.append param
set param = cmd.CreateParameter("@clid",adVarChar,adParamInput,8,lclid)
.parameters.append param
set param = cmd.CreateParameter("@dob",adDBTimeStamp,adParamInput,0,dDOB)
.parameters.append param
set param = cmd.CreateParameter("@ssn",adVarChar,adParamInput,25,sSSN)
.parameters.append param
End With
set rs = Server.Createobject("adodb.recordset")
rs.open cmd

dim bResultsReturned
If NOT rs.EOF THEN
bResultsReturned = true
arResults = rs.GetRows
Else
bResultsReturned = false
Response.Write "no records"
End If
rs.Close
set rs = nothing
cn.close
set cn = nothing
If bResultsReturned = true then
for introws = 0 to UBound(arResults,2)
next
end if

function CDateToISO(pDate)
dim sDate
if isdate(pDate) then
sDate = year(pDate)
if Month(pDate) < 10 then
sDate = sDate & "-0" & Month(pDate)
else
sDate = sDate & "-" & Month(pDate)
end if
if Day(pDate) < 10 then
sDate = sDate & "-0" & Day(pDate)
else
sDate = sDate & "-" & Day(pDate)
end if
CDateToISO = sDate
else
response.write pDate & " does not seem to be a date"
CDateToISO = pDate
end if
end function

Response.Write (Request.Form("dob"))
Response.Write (Request.Form("clid"))
Response.Write (Request.Form("fname"))
Response.Write (Request.Form("lname"))
Response.Write (Request.Form("ssn"))
%>
<html>
<head>
<title>PIMA Search Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Search Returned for a Contact
<p>
<%
IF (NOT introws = "") then

Response.Write ("<table width='100%' border='1' cellspacing='1'
cellpadding='1'>")
Response.Write ("<tr><th class='table_heading' height='17'
bgcolor='#99CCFF' colspan='2'>Select the Contact</th><th
bgcolor='#99CCFF'>Edit</th><th bgcolor='#99CCFF'>Add an Instance</th></tr>")

For i = 0 to UBound(arResults,2)
Response.Write ("<tr valign='top'>")
Response.Write ("<td height='36' width='75%' colspan='2'> <p>")
Response.Write ("<a
href='/Group/Standard/View/ClientView2.asp?Action=Search&SearchWhat=intCLID&
intCLID="&arResults(2,i)&"&SubmitForm=Go' style='text-decoration: none'><b
class='sub_heading'>"&arResults(0,i)&",
"&arResults(1,i)&"&nbsp;&nbsp;"&arResults(2,i)&"&nbsp;&nbsp;"&arResults(3,i)
&"&nbsp;&nbsp;"&arResults(4,i)&"</b><td><a
href=../Update/ClientUpdate.asp?intCLID="&arResults(2,i)&">Edit</td><td><a
href=../../../Instance_add.asp?intCLID="&arResults(2,i)&">Add an
Instance</a><br>")
Response.Write ("</p></td></tr>")
Next

Response.Write ("</table>")
Else
Response.Write ("<b class='sub_heading'>No matching Records.</b>")
END IF
%>
</p>
<tr>
<td>

Thanks,
Matt


"Bob Barrows" <reb_...@yahoo.com> wrote in message

news:O4s4E5C4...@TK2MSFTNGP11.phx.gbl...

Bob Barrows

unread,
Mar 1, 2003, 6:19:09 PM3/1/03
to
If you copy the statement from Profiler, paste it into QA and run it, are
the correct results returned? I haven't a clue about the data in your table.

Bob


"Matt" <mes...@austin.rr.com> wrote in message

news:hXa8a.51107$O41.1...@twister.austin.rr.com...

Matt

unread,
Mar 1, 2003, 6:35:48 PM3/1/03
to
No, actually it doesn't return anything. I can select and then use it in a
where clause and it pulls the correct data.

Matt

"Bob Barrows" <reb_...@yahoo.com> wrote in message

news:e6v30jE4...@TK2MSFTNGP12.phx.gbl...

Bob Barrows

unread,
Mar 2, 2003, 9:07:09 AM3/2/03
to
I did not follow that. You can select the stored procedure call from
Profiler and " use it in a where clause "?

I think it's time for you to post a CREATE TABLE statement (relevant columns
only please) and some sample data insert statements so we will both be on
the same page. As I've said, my code works fine with the test table and data
I've been using, so something must be different with the table and/or data
you have.

Bob

"Matt" <mes...@austin.rr.com> wrote in message

news:otb8a.51292$O41.1...@twister.austin.rr.com...

Matt

unread,
Mar 2, 2003, 11:17:33 AM3/2/03
to
Bob:
I had mistakenly put in vbNullString for the clid instead of just Null, like
below.

lclid = Request.form("clid")

If len(lclid) = 0 then lclid = Null

When I made that change it seems to be working great! Thanks again for all
of your help. If you're ever in Austin I owe you a drink or 2.

Thanks again,
Matt

"Bob Barrows" <reb_...@yahoo.com> wrote in message

news:uWbnBUM4...@TK2MSFTNGP12.phx.gbl...

Reply all
Reply to author
Forward
0 new messages