More Date Confusion

Discussion in 'ASP General' started by Dooza, Jan 11, 2008.

  1. Dooza

    Dooza Guest

    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
     
    Dooza, Jan 11, 2008
    #1
    1. Advertising

  2. 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:
    >
    > <%

    <snip - TMI!!!>
    >
    > 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.
     
    Bob Barrows [MVP], Jan 11, 2008
    #2
    1. Advertising

  3. 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.
     
    Bob Barrows [MVP], Jan 11, 2008
    #3
  4. Dooza

    Dooza Guest

    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:
    >>
    >> <%

    > <snip - TMI!!!>
    >> 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
    >
    >
    >
     
    Dooza, Jan 11, 2008
    #4
  5. 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.
     
    Bob Barrows [MVP], Jan 11, 2008
    #5
  6. Dooza

    Dooza Guest

    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
     
    Dooza, Jan 11, 2008
    #6
  7. 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.
     
    Bob Barrows [MVP], Jan 11, 2008
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt
    Replies:
    1
    Views:
    631
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,333
    Michael Borgwardt
    May 30, 2004
  3. Michael
    Replies:
    4
    Views:
    456
    Matt Hammond
    Jun 26, 2006
  4. Robert Klemme

    With a Ruby Yell: more, more more!

    Robert Klemme, Sep 28, 2005, in forum: Ruby
    Replies:
    5
    Views:
    230
    Jeff Wood
    Sep 29, 2005
  5. Greg Hauptmann
    Replies:
    2
    Views:
    107
    Greg Hauptmann
    Dec 19, 2008
Loading...

Share This Page