More Date Confusion

I have been trying to follow the recent posts about the date format. I
am building a single page report, which displays 4 columns from the
database. There are 3 filters, one is location, and the other 2 are
dates, a from and a to. When the page first loads it will show no
records, but when submitted to itself it will filter the records returned.

I am using ASP with VBScript. My database is SQL 2000 server.

I have a Stored Procedure:
ALTER PROCEDURE [dbo].[OutstandingPurchaseOrderReport]
-- Add the parameters for the stored procedure here
@Location varchar(7),
@From datetime,
@To datetime
AS
BEGIN
SELECT TOP 100 PERCENT dbo.purchaseorder.poh_order AS PONumber,
dbo.purchaseorder.poh_vendor + ' ' + dbo.vendor.vnd_name AS Vendor,
dbo.purchaseorder.poh_buyer AS Buyer,
dbo.purchaseorder.poh_orderdate AS OrderDate
FROM dbo.plannerbuyer RIGHT OUTER JOIN
dbo.purchaseorder INNER JOIN
dbo.vendor ON dbo.purchaseorder.poh_vendor =
dbo.vendor.vnd_vendor ON
dbo.plannerbuyer.plb_plannerbuyer =
dbo.purchaseorder.poh_buyer
WHERE (dbo.purchaseorder.poh_orderstatus = 'Open') AND
(dbo.plannerbuyer.plb_phonearea = CASE WHEN
@Location = 'Leeds' THEN '113' WHEN @Location = 'Wycombe' THEN '1494'
WHEN @Location = 'Both' THEN '%' END) AND
dbo.purchaseorder.poh_orderdate BETWEEN @From AND @To
ORDER BY OrderDate, PONumber
END

It takes 3 inputs from an ASP page, location, from date and to date. As
the last 2 are using datetime as the format, and the columns are
datatime, I assumed (from reading posts here) that I should be using the
ISO standard for date. Is this assumption correct?

To test my SP I did this:
EXEC OutstandingPurchaseOrderReport 'Wycombe','01/01/2001','01/01/2005'

My results:
1230002864 RAP107 Rapid Electronics Limited ANDYB 2004-02-12 00:00:00.000
1230008306 HIG1 High End Systems Inc CALLYB 2004-09-16 00:00:00.000

I also tried this EXEC OutstandingPurchaseOrderReport
'Wycombe','2004-02-12 00:00:00.000','2004-02-12 00:00:00.000'

And got exactly the same results. I am using Server Management Studio
Express, I don't know if that makes any difference.

Now on to the page itself:

<%
Dim ospo__Location
ospo__Location = "Both"
If (Request.Form("Location") <> "") Then
ospo__Location = Request.Form("Location")
End If
%>
<%
Dim ospo__From
ospo__From = "01/01/2000"
If (Request.Form("From") <> "") Then
ospo__From = Request.Form("From")
End If
%>
<%
Dim ospo__To
ospo__To = "01/01/2010"
If (Request.Form("To") <> "") Then
ospo__To = Request.Form("To")
End If
%>
<%
Dim ospo
Dim ospo_cmd
Dim ospo_numRows

Set ospo_cmd = Server.CreateObject ("ADODB.Command")
ospo_cmd.ActiveConnection = MM_aclv4v2_STRING
ospo_cmd.CommandText = "{call dbo.OutstandingPurchaseOrderReport(?,?,?)}"
ospo_cmd.Prepared = true
ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param1", 200, 1,
255, ospo__Location) ' adVarChar
ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param2", 135, 1,
-1, ospo__From) ' adDBTimeStamp
ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param3", 135, 1,
-1, ospo__To) ' adDBTimeStamp

Set ospo = ospo_cmd.Execute
ospo_numRows = 0
%>

When the page first loads it shows no records. I wanted to show all
records first, which is why I gave the dates presets. When I entered the
ISO date I got this error:
Application uses a value of the wrong type for the current operation.

If I manually enter the date on the page like this: 25/12/2004 then I
get the results that I expect.

What I don't understand is why the ISO dates don't work when I enter
them in to the form. Everywhere I read says I need to enter it in ISO.
The plan was to have a pop up date picker that returned the data in ISO,
but if the page won't accept ISO I will use the UK format date.

The page is for one person to use on an intranet. But I want to
understand the date in ASP and SQL, as its really doing my head in.

Thank you in advance to anyone who reads this monster post and can help me!

Steve

Re: More Date Confusion

Dooza wrote:
> I have been trying to follow the recent posts about the date format. I
> am building a single page report, which displays 4 columns from the
> database. There are 3 filters, one is location, and the other 2 are
> dates, a from and a to. When the page first loads it will show no
> records, but when submitted to itself it will filter the records
> returned.
>
> I am using ASP with VBScript. My database is SQL 2000 server.
>
> I have a Stored Procedure:
> ALTER PROCEDURE [dbo].[OutstandingPurchaseOrderReport]
> -- Add the parameters for the stored procedure here
> @Location varchar(7),
> @From datetime,
> @To datetime

This is the only part you need to show us. We don't need to see the rest
>
> It takes 3 inputs from an ASP page, location, from date and to date.
> As the last 2 are using datetime as the format, and the columns are
> datatime, I assumed (from reading posts here) that I should be using
> the ISO standard for date. Is this assumption correct?

Yes. It is guaranteed not to be misinterpreted

>
> To test my SP I did this:
> EXEC OutstandingPurchaseOrderReport
> 'Wycombe','01/01/2001','01/01/2005'

Hardly a fair test ... 01/01 can hardly be misinterpreted, now can it?
>
> My results:
> 1230002864 RAP107 Rapid Electronics Limited ANDYB 2004-02-12
> 00:00:00.000 1230008306 HIG1 High End Systems Inc CALLYB 2004-09-16
> 00:00:00.000
>
> I also tried this EXEC OutstandingPurchaseOrderReport
> 'Wycombe','2004-02-12 00:00:00.000','2004-02-12 00:00:00.000'
>
> And got exactly the same results. I am using Server Management Studio
> Express, I don't know if that makes any difference.

No difference at all.. The locale used in the database can make the
non-ISO format problematical. The default installation of SQL Server
results in the US locale being used.

>
> Now on to the page itself:
>
> <%

>
> Set ospo_cmd = Server.CreateObject ("ADODB.Command")
> ospo_cmd.ActiveConnection = MM_aclv4v2_STRING

Bad, very bad, extremely bad practice (did I say this was a bad
practice?)

Never set an object's ActiveConnection property to a string. Doing so
causes an implicit connection to be created behind the scenes. What's
wrong with that? Well:
1. You have no control over it
2. Doing so can consume extra resources and kill performance because
you may be circumventing connection pooling.

ALWAYS create an explicit connection object:

set cn = createobject("adodb.connection")
cn.open MM_aclv4v2_STRING 'Ugghhh! Macromedia :-b

and use that connection object whenever a connection object is required
in your page (which may be multiple times), like this:

Set ospo_cmd.ActiveConnection = cn

With an explicit connection variable, you can control when it gets
closed (freeing up the connection to be returned to the pool), thus
helping your server and database conserve resources.

> ospo_cmd.CommandText = "{call
> dbo.OutstandingPurchaseOrderReport(?,?,?)}"

The odbc parameter syntax is not needed.Simply do this:
ospo_cmd.CommandText ="dbo.OutstandingPurchaseOrderReport"
ospo_cmd.CommandType = 4 'adCmdStoredProc

> ospo_cmd.Prepared = true

Again, very rarely needed.

> ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param1", 200, 1,
> 255, ospo__Location) ' adVarChar
> ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param2", 135, 1,
> -1, ospo__From) ' adDBTimeStamp
> ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param3", 135, 1,
> -1, ospo__To) ' adDBTimeStamp

Nothing wrong with doing this, but you are going to entirely too much
trouble!
The problem here is: ospo__From and ospo__To are still strings!! You
should convert them to dates before passing them to the procedure.

It appears to me that, unless these values are coming from calendar
controls in your client-side form, you have not been following this
thread at all. Please clarify that you have taken steps to make sure the
user is either selecting a date from a calendar control or that you are
using client-side code to build the dates being passed from selections
made in year,month and day dropdowns. Do not depend on the user
correctly entering a date in a textvox!

If you are using a calendar control, simply replace ospo__From in the
CreateParameter call with CDate(ospo__From)

>
> Set ospo = ospo_cmd.Execute

Again, you are going to too much trouble here. There are no output
parameters and you have no interest in the return parameter, so an
explicit Command object is not needed. Do this instead:
Set ospo = createobject("adodb.recordset")
cn.OutstandingPurchaseOrderReport ospo__Location, _
CDate(ospo__From), CDate(ospo__To), ospo



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: More Date Confusion

Dooza wrote:
> I have been trying to follow the recent posts about the date format. I
> am building a single page report, which displays 4 columns from the
> database. There are 3 filters, one is location, and the other 2 are
> dates, a from and a to. When the page first loads it will show no
> records, but when submitted to itself it will filter the records
> returned.
>
> It takes 3 inputs from an ASP page, location, from date and to date.
> As the last 2 are using datetime as the format, and the columns are
> datatime, I assumed (from reading posts here) that I should be using
> the ISO standard for date. Is this assumption correct?
>

I have to clarifuy this:
If you are using dynamic sql (ugh!!!) to construct a sql statement, then
date literals used in the string should indeed be in ISO format
(yyyymmdd)

If you are passing parameters, as you are doing in your attempt, you
need to pass values that have been explicitly converted to the proper
datatypes.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: More Date Confusion

Hi Bob,
You guessed right, I am using Dreamweaver to construct my code, but am
trying to work out what it does, so your comments are invaluable to me.
I am a visual designer, but I am still learning how it all works.

I am currently trying to implement a date picker, the one I first used
only gave me US format, which wasn't what I wanted, so I am now trying
one with ISO format.

I confirmed in both the SP via SQL Server Management Studio and on the
ASP page that the server is using UK format dates. Is it the local of
the server and my installation that is making it do this?

I did have a play with CDate but it didn't work, once I get the date
picker to work I will use it again.

Steve

Bob Barrows [MVP] wrote:
> Dooza wrote:
>> I have been trying to follow the recent posts about the date format. I
>> am building a single page report, which displays 4 columns from the
>> database. There are 3 filters, one is location, and the other 2 are
>> dates, a from and a to. When the page first loads it will show no
>> records, but when submitted to itself it will filter the records
>> returned.
>>
>> I am using ASP with VBScript. My database is SQL 2000 server.
>>
>> I have a Stored Procedure:
>> ALTER PROCEDURE [dbo].[OutstandingPurchaseOrderReport]
>> -- Add the parameters for the stored procedure here
>> @Location varchar(7),
>> @From datetime,
>> @To datetime
>
> This is the only part you need to show us. We don't need to see the rest
>> It takes 3 inputs from an ASP page, location, from date and to date.
>> As the last 2 are using datetime as the format, and the columns are
>> datatime, I assumed (from reading posts here) that I should be using
>> the ISO standard for date. Is this assumption correct?
>
> Yes. It is guaranteed not to be misinterpreted
>
>> To test my SP I did this:
>> EXEC OutstandingPurchaseOrderReport
>> 'Wycombe','01/01/2001','01/01/2005'
>
> Hardly a fair test ... 01/01 can hardly be misinterpreted, now can it?
>> My results:
>> 1230002864 RAP107 Rapid Electronics Limited ANDYB 2004-02-12
>> 00:00:00.000 1230008306 HIG1 High End Systems Inc CALLYB 2004-09-16
>> 00:00:00.000
>>
>> I also tried this EXEC OutstandingPurchaseOrderReport
>> 'Wycombe','2004-02-12 00:00:00.000','2004-02-12 00:00:00.000'
>>
>> And got exactly the same results. I am using Server Management Studio
>> Express, I don't know if that makes any difference.
>
> No difference at all.. The locale used in the database can make the
> non-ISO format problematical. The default installation of SQL Server
> results in the US locale being used.
>
>> Now on to the page itself:
>>
>> <%
>
>> Set ospo_cmd = Server.CreateObject ("ADODB.Command")
>> ospo_cmd.ActiveConnection = MM_aclv4v2_STRING
>
> Bad, very bad, extremely bad practice (did I say this was a bad
> practice?)
>
> Never set an object's ActiveConnection property to a string. Doing so
> causes an implicit connection to be created behind the scenes. What's
> wrong with that? Well:
> 1. You have no control over it
> 2. Doing so can consume extra resources and kill performance because
> you may be circumventing connection pooling.
>
> ALWAYS create an explicit connection object:
>
> set cn = createobject("adodb.connection")
> cn.open MM_aclv4v2_STRING 'Ugghhh! Macromedia :-b
>
> and use that connection object whenever a connection object is required
> in your page (which may be multiple times), like this:
>
> Set ospo_cmd.ActiveConnection = cn
>
> With an explicit connection variable, you can control when it gets
> closed (freeing up the connection to be returned to the pool), thus
> helping your server and database conserve resources.
>
>> ospo_cmd.CommandText = "{call
>> dbo.OutstandingPurchaseOrderReport(?,?,?)}"
>
> The odbc parameter syntax is not needed.Simply do this:
> ospo_cmd.CommandText ="dbo.OutstandingPurchaseOrderReport"
> ospo_cmd.CommandType = 4 'adCmdStoredProc
>
>> ospo_cmd.Prepared = true
>
> Again, very rarely needed.
>
>> ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param1", 200, 1,
>> 255, ospo__Location) ' adVarChar
>> ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param2", 135, 1,
>> -1, ospo__From) ' adDBTimeStamp
>> ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param3", 135, 1,
>> -1, ospo__To) ' adDBTimeStamp
>
> Nothing wrong with doing this, but you are going to entirely too much
> trouble!
> The problem here is: ospo__From and ospo__To are still strings!! You
> should convert them to dates before passing them to the procedure.
>
> It appears to me that, unless these values are coming from calendar
> controls in your client-side form, you have not been following this
> thread at all. Please clarify that you have taken steps to make sure the
> user is either selecting a date from a calendar control or that you are
> using client-side code to build the dates being passed from selections
> made in year,month and day dropdowns. Do not depend on the user
> correctly entering a date in a textvox!
>
> If you are using a calendar control, simply replace ospo__From in the
> CreateParameter call with CDate(ospo__From)
>
>> Set ospo = ospo_cmd.Execute
>
> Again, you are going to too much trouble here. There are no output
> parameters and you have no interest in the return parameter, so an
> explicit Command object is not needed. Do this instead:
> Set ospo = createobject("adodb.recordset")
> cn.OutstandingPurchaseOrderReport ospo__Location, _
> CDate(ospo__From), CDate(ospo__To), ospo
>
>
>

Re: More Date Confusion

Dooza wrote:
> Hi Bob,
> You guessed right, I am using Dreamweaver to construct my code, but am
> trying to work out what it does, so your comments are invaluable to
> me. I am a visual designer, but I am still learning how it all works.
>
> I am currently trying to implement a date picker, the one I first used
> only gave me US format, which wasn't what I wanted, so I am now trying
> one with ISO format.

You should not care what format the datepicker is providing (something
is puzzling me here: every datepicker I've ever seen has allowed
configurable output formats - doesn't yours?). You can format the dates
any way you want when you display them to the user. What you need to
guarantee is that a consistent format is being used to pass the dates to
the server. The datepicker can do this. Use it. Don't worry about it not
giving you dates in ISO format (see my other message). Since the string
provided by the datepicker will contain a date with a consistent format,
it will be child's play to parse the year month and day values from it
to construct a proper date variable using dateserial()

>
> I confirmed in both the SP via SQL Server Management Studio and on the
> ASP page that the server is using UK format dates. Is it the local of
> the server and my installation that is making it do this?
>

No, it is the fact that you are not passing actual dates to the
procedures (you are passing strings). The strings being passed from the
client need to be converted to dates before being passed to the
procedure. The best practice is to pass year, month and day values to
the server so your code can use dateserial(year,month,day) to create
actual date variables. You could parse the strings currently being
passed to extract the year, month and day values, but this is prone to
errors. Do not depend on user input having any expected format. Make
your application format-independant. Instead of having the user enter a
formatted date, use a calendar control, or 3 dropdowns, or at least, 3
textboxes to allow the user to enter a year, month and day

This is part of the value of using parameters instead of dynamic sql, by
the way.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: More Date Confusion

Bob Barrows [MVP] wrote:
> Dooza wrote:
>> Hi Bob,
>> You guessed right, I am using Dreamweaver to construct my code, but am
>> trying to work out what it does, so your comments are invaluable to
>> me. I am a visual designer, but I am still learning how it all works.
>>
>> I am currently trying to implement a date picker, the one I first used
>> only gave me US format, which wasn't what I wanted, so I am now trying
>> one with ISO format.
>
> You should not care what format the datepicker is providing (something
> is puzzling me here: every datepicker I've ever seen has allowed
> configurable output formats - doesn't yours?). You can format the dates
> any way you want when you display them to the user. What you need to
> guarantee is that a consistent format is being used to pass the dates to
> the server. The datepicker can do this. Use it. Don't worry about it not
> giving you dates in ISO format (see my other message). Since the string
> provided by the datepicker will contain a date with a consistent format,
> it will be child's play to parse the year month and day values from it
> to construct a proper date variable using dateserial()

The first one only output in US format, I spoke to the author, he
couldn't care less as it was an old tutorial. I moved on, and have found
one that allows the date picker to use ISO standard date. Its working
nicely now. I am using CData in the parameters as you suggested.

>> I confirmed in both the SP via SQL Server Management Studio and on the
>> ASP page that the server is using UK format dates. Is it the local of
>> the server and my installation that is making it do this?
>>
>
> No, it is the fact that you are not passing actual dates to the
> procedures (you are passing strings). The strings being passed from the
> client need to be converted to dates before being passed to the
> procedure. The best practice is to pass year, month and day values to
> the server so your code can use dateserial(year,month,day) to create
> actual date variables. You could parse the strings currently being
> passed to extract the year, month and day values, but this is prone to
> errors. Do not depend on user input having any expected format. Make
> your application format-independant. Instead of having the user enter a
> formatted date, use a calendar control, or 3 dropdowns, or at least, 3
> textboxes to allow the user to enter a year, month and day

I had seen DateSerial, and am now using that for the preset variables,
its working nicely now.

The date picker is working fine.

> This is part of the value of using parameters instead of dynamic sql, by
> the way.

I don't use dynamic sql, I know how bad it is, and was never taught to
use it. My SP is just a SELECT statement using CASE in the WHERE...does
that make it dynamic SQL?

Thank you for your comments Bob, whilst I am very dependant on
Dreamweaver, I am learning ASP and from your advice, I am learning how
to use it correctly.

Thank you!

Steve

Re: More Date Confusion

Dooza wrote:
> I don't use dynamic sql, I know how bad it is, and was never taught to
> use it. My SP is just a SELECT statement using CASE in the
> WHERE...does that make it dynamic SQL?

No. Dynamic sql is where you use concatenation to construct a string
containing a sql statement. While it is possible for a stored procedure
to do this:

declare @sql varchar(1000)
set @sql = 'select ...where col1=' + @parmvalue
exec(@sql)

That is not what your procedure is doing.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.