Cannot get to format the date field correctly yet

Discussion in 'ASP General' started by Jack, Apr 1, 2005.

  1. Jack

    Jack Guest

    Hi,
    I cannot get the date format correctly in dynamic sql statement, after
    trying various ways of handling it. I need some help with the date format in
    the following dynamic sql statement. Any help is appreciated in advance.
    While running the asp page, I still get an error as
    Error Type:
    Microsoft VBScript runtime (0x800A000D)
    Type mismatch: 'Format'

    DYNAMIC SQL STATEMENT:
    sql = " INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense, "
    sql = sql & "TravelExpense, PersonnelExpense)"
    sql = sql & " VALUES('" & l_ENO & "', " & Format(l_Date,
    "\#mm\/dd\/yyyy\#") & ", " & l_contractedserviceexpense & ", "
    &l_travelexpense & ", " &l_personnelexpense & ")"
     
    Jack, Apr 1, 2005
    #1
    1. Advertising

  2. What does your format function look like? What kind of database are you
    using?

    Take a look at the functions here. http://www.aspfaq.com/show.asp?id=2313

    Ray at work

    "Jack" <> wrote in message
    news:...
    > Hi,
    > I cannot get the date format correctly in dynamic sql statement, after
    > trying various ways of handling it. I need some help with the date format

    in
    > the following dynamic sql statement. Any help is appreciated in advance.
    > While running the asp page, I still get an error as
    > Error Type:
    > Microsoft VBScript runtime (0x800A000D)
    > Type mismatch: 'Format'
    >
    > DYNAMIC SQL STATEMENT:
    > sql = " INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense,

    "
    > sql = sql & "TravelExpense, PersonnelExpense)"
    > sql = sql & " VALUES('" & l_ENO & "', " & Format(l_Date,
    > "\#mm\/dd\/yyyy\#") & ", " & l_contractedserviceexpense & ", "
    > &l_travelexpense & ", " &l_personnelexpense & ")"
    >
    >
     
    Ray Costanzo [MVP], Apr 1, 2005
    #2
    1. Advertising

  3. There is no Format in VBScript, sorry. And please stop using ambiguous
    formats like mm/dd/yyyy. http://www.aspfaq.com/2023

    --
    Please post DDL, sample data and desired results.
    See http://www.aspfaq.com/5006 for info.




    "Jack" <> wrote in message
    news:...
    > Hi,
    > I cannot get the date format correctly in dynamic sql statement, after
    > trying various ways of handling it. I need some help with the date format

    in
    > the following dynamic sql statement. Any help is appreciated in advance.
    > While running the asp page, I still get an error as
    > Error Type:
    > Microsoft VBScript runtime (0x800A000D)
    > Type mismatch: 'Format'
    >
    > DYNAMIC SQL STATEMENT:
    > sql = " INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense,

    "
    > sql = sql & "TravelExpense, PersonnelExpense)"
    > sql = sql & " VALUES('" & l_ENO & "', " & Format(l_Date,
    > "\#mm\/dd\/yyyy\#") & ", " & l_contractedserviceexpense & ", "
    > &l_travelexpense & ", " &l_personnelexpense & ")"
    >
    >
     
    Aaron [SQL Server MVP], Apr 1, 2005
    #3
  4. Jack

    Jack Guest

    Ray,
    I am using access in the backend. My problem is figure out how to manipulate
    the date variable so that a response.write sql throws back date as
    #10/01/2005#. The way I got it now, it does not allow the response.write
    statement to work. At this point I do not care about any date format, rather
    the sql statement to work properly by adding the delimiters to the date.
    Thanks. Regards.

    "Jack" wrote:

    > Hi,
    > I cannot get the date format correctly in dynamic sql statement, after
    > trying various ways of handling it. I need some help with the date format in
    > the following dynamic sql statement. Any help is appreciated in advance.
    > While running the asp page, I still get an error as
    > Error Type:
    > Microsoft VBScript runtime (0x800A000D)
    > Type mismatch: 'Format'
    >
    > DYNAMIC SQL STATEMENT:
    > sql = " INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense, "
    > sql = sql & "TravelExpense, PersonnelExpense)"
    > sql = sql & " VALUES('" & l_ENO & "', " & Format(l_Date,
    > "\#mm\/dd\/yyyy\#") & ", " & l_contractedserviceexpense & ", "
    > &l_travelexpense & ", " &l_personnelexpense & ")"
    >
    >
     
    Jack, Apr 1, 2005
    #4
  5. You shouldn't be using 10/01/2005 as a date format anyway. What is that?
    October 1st? January 10th? Different people (and different locale
    settings) will see it in different ways.

    Did you look at the links that Aaron and I posted?

    See quote from the link Aaron posted:
    For Access, a date should always be delimited and formatted as:

    #YYYY-MM-DD#
    -- some versions will accept 'YYYY-MM-DD'


    And then use the link I posted to get the right function to give you the
    YYYY-MM-DD format.

    Ray at work


    "Jack" <> wrote in message
    news:...
    > Ray,
    > I am using access in the backend. My problem is figure out how to

    manipulate
    > the date variable so that a response.write sql throws back date as
    > #10/01/2005#. The way I got it now, it does not allow the response.write
    > statement to work. At this point I do not care about any date format,

    rather
    > the sql statement to work properly by adding the delimiters to the date.
    > Thanks. Regards.
    >
    > "Jack" wrote:
    >
    > > Hi,
    > > I cannot get the date format correctly in dynamic sql statement, after
    > > trying various ways of handling it. I need some help with the date

    format in
    > > the following dynamic sql statement. Any help is appreciated in advance.
    > > While running the asp page, I still get an error as
    > > Error Type:
    > > Microsoft VBScript runtime (0x800A000D)
    > > Type mismatch: 'Format'
    > >
    > > DYNAMIC SQL STATEMENT:
    > > sql = " INSERT INTO tblExpense (ENO, EntryDate,

    ContractedServiceExpense, "
    > > sql = sql & "TravelExpense, PersonnelExpense)"
    > > sql = sql & " VALUES('" & l_ENO & "', " & Format(l_Date,
    > > "\#mm\/dd\/yyyy\#") & ", " & l_contractedserviceexpense & ", "
    > > &l_travelexpense & ", " &l_personnelexpense & ")"
    > >
    > >
     
    Ray Costanzo [MVP], Apr 1, 2005
    #5
  6. I bet this would work:

    on error resume next
    l_date=cdate(l_Date)
    if err <> 0 then
    response.write l_date & " is not a valid date"
    response.end
    end if
    on error goto 0

    sql = " INSERT INTO tblExpense " & _
    "(ENO, EntryDate, ContractedServiceExpense" & _
    ",TravelExpense, PersonnelExpense) " & _
    " VALUES(?,?,?,?,?)"

    arParms = array(l_ENO, l_Date , _
    l_contractedserviceexpense,l_travelexpense, _
    l_personnelexpense)

    ' assuming conn is your connection variable
    set conn=createobject("adodb.connection")
    conn.open "<ole db connection string>"

    set cmd=createobject("adodb.command")
    cmd.commandtext=sql
    set cmd.activeconnection = conn
    cmd.execute ,arParms,129



    But you just probably want to keep going with the dynamic sql, right? If so,
    open your database in Access, create a new query using the query builder to
    create a query that works. Switch to SQL view to see what you need to make
    your sql string look like.

    I gotta say though, you're sure going to a lot of trouble just to get this
    dynamic sql crutch working ...

    Bob Barrows

    Jack wrote:
    > Ray,
    > I am using access in the backend. My problem is figure out how to
    > manipulate the date variable so that a response.write sql throws back
    > date as #10/01/2005#. The way I got it now, it does not allow the
    > response.write statement to work. At this point I do not care about
    > any date format, rather the sql statement to work properly by adding
    > the delimiters to the date. Thanks. Regards.
    >
    > "Jack" wrote:
    >
    >> Hi,
    >> I cannot get the date format correctly in dynamic sql statement,
    >> after trying various ways of handling it. I need some help with the
    >> date format in the following dynamic sql statement. Any help is
    >> appreciated in advance. While running the asp page, I still get an
    >> error as
    >> Error Type:
    >> Microsoft VBScript runtime (0x800A000D)
    >> Type mismatch: 'Format'
    >>
    >> DYNAMIC SQL STATEMENT:
    >> sql = " INSERT INTO tblExpense (ENO, EntryDate,
    >> ContractedServiceExpense, " sql = sql & "TravelExpense,
    >> PersonnelExpense)" sql = sql & " VALUES('" & l_ENO & "', " &
    >> Format(l_Date, "\#mm\/dd\/yyyy\#") & ", " &
    >> l_contractedserviceexpense & ", " &l_travelexpense & ", "
    >> &l_personnelexpense & ")"


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Apr 1, 2005
    #6
  7. Jack

    Jack Guest

    Thanks Bob for clearly showing how to handle this with your approach.
    Please note the reason I was unable to 'wean off' from dynamic sql was due
    to my inability to grasp your method without a clear example. Now that you
    have shown, and I test it, grasp it, it will sink in. Then, I will definitely
    make the transition. The dynamic sql approach was the only choice left to me
    due to my inability to comprehend your method clearly. Thanks a ton for your
    generous demonstration. Regards.


    "Bob Barrows [MVP]" wrote:

    > I bet this would work:
    >
    > on error resume next
    > l_date=cdate(l_Date)
    > if err <> 0 then
    > response.write l_date & " is not a valid date"
    > response.end
    > end if
    > on error goto 0
    >
    > sql = " INSERT INTO tblExpense " & _
    > "(ENO, EntryDate, ContractedServiceExpense" & _
    > ",TravelExpense, PersonnelExpense) " & _
    > " VALUES(?,?,?,?,?)"
    >
    > arParms = array(l_ENO, l_Date , _
    > l_contractedserviceexpense,l_travelexpense, _
    > l_personnelexpense)
    >
    > ' assuming conn is your connection variable
    > set conn=createobject("adodb.connection")
    > conn.open "<ole db connection string>"
    >
    > set cmd=createobject("adodb.command")
    > cmd.commandtext=sql
    > set cmd.activeconnection = conn
    > cmd.execute ,arParms,129
    >
    >
    >
    > But you just probably want to keep going with the dynamic sql, right? If so,
    > open your database in Access, create a new query using the query builder to
    > create a query that works. Switch to SQL view to see what you need to make
    > your sql string look like.
    >
    > I gotta say though, you're sure going to a lot of trouble just to get this
    > dynamic sql crutch working ...
    >
    > Bob Barrows
    >
    > Jack wrote:
    > > Ray,
    > > I am using access in the backend. My problem is figure out how to
    > > manipulate the date variable so that a response.write sql throws back
    > > date as #10/01/2005#. The way I got it now, it does not allow the
    > > response.write statement to work. At this point I do not care about
    > > any date format, rather the sql statement to work properly by adding
    > > the delimiters to the date. Thanks. Regards.
    > >
    > > "Jack" wrote:
    > >
    > >> Hi,
    > >> I cannot get the date format correctly in dynamic sql statement,
    > >> after trying various ways of handling it. I need some help with the
    > >> date format in the following dynamic sql statement. Any help is
    > >> appreciated in advance. While running the asp page, I still get an
    > >> error as
    > >> Error Type:
    > >> Microsoft VBScript runtime (0x800A000D)
    > >> Type mismatch: 'Format'
    > >>
    > >> DYNAMIC SQL STATEMENT:
    > >> sql = " INSERT INTO tblExpense (ENO, EntryDate,
    > >> ContractedServiceExpense, " sql = sql & "TravelExpense,
    > >> PersonnelExpense)" sql = sql & " VALUES('" & l_ENO & "', " &
    > >> Format(l_Date, "\#mm\/dd\/yyyy\#") & ", " &
    > >> l_contractedserviceexpense & ", " &l_travelexpense & ", "
    > >> &l_personnelexpense & ")"

    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >
     
    Jack, Apr 1, 2005
    #7
  8. Jack

    Jack Guest

    Ray,
    My problem was how to handle the delimiters in the date variable, ie the
    issue was how to concatenate the # delimiter and not the format issue.
    However, I have seen what Aaron and you advised ealier and understood the
    implications. Thanks.
    Regards.

    "Ray Costanzo [MVP]" wrote:

    > You shouldn't be using 10/01/2005 as a date format anyway. What is that?
    > October 1st? January 10th? Different people (and different locale
    > settings) will see it in different ways.
    >
    > Did you look at the links that Aaron and I posted?
    >
    > See quote from the link Aaron posted:
    > For Access, a date should always be delimited and formatted as:
    >
    > #YYYY-MM-DD#
    > -- some versions will accept 'YYYY-MM-DD'
    >
    >
    > And then use the link I posted to get the right function to give you the
    > YYYY-MM-DD format.
    >
    > Ray at work
    >
    >
    > "Jack" <> wrote in message
    > news:...
    > > Ray,
    > > I am using access in the backend. My problem is figure out how to

    > manipulate
    > > the date variable so that a response.write sql throws back date as
    > > #10/01/2005#. The way I got it now, it does not allow the response.write
    > > statement to work. At this point I do not care about any date format,

    > rather
    > > the sql statement to work properly by adding the delimiters to the date.
    > > Thanks. Regards.
    > >
    > > "Jack" wrote:
    > >
    > > > Hi,
    > > > I cannot get the date format correctly in dynamic sql statement, after
    > > > trying various ways of handling it. I need some help with the date

    > format in
    > > > the following dynamic sql statement. Any help is appreciated in advance.
    > > > While running the asp page, I still get an error as
    > > > Error Type:
    > > > Microsoft VBScript runtime (0x800A000D)
    > > > Type mismatch: 'Format'
    > > >
    > > > DYNAMIC SQL STATEMENT:
    > > > sql = " INSERT INTO tblExpense (ENO, EntryDate,

    > ContractedServiceExpense, "
    > > > sql = sql & "TravelExpense, PersonnelExpense)"
    > > > sql = sql & " VALUES('" & l_ENO & "', " & Format(l_Date,
    > > > "\#mm\/dd\/yyyy\#") & ", " & l_contractedserviceexpense & ", "
    > > > &l_travelexpense & ", " &l_personnelexpense & ")"
    > > >
    > > >

    >
    >
    >
     
    Jack, Apr 1, 2005
    #8
  9. Jack

    Jack Guest

    Bob
    After inserting your code and making the necessary changes I got the following
    error coming:
    d (0x800A0E7C)
    Parameter object is improperly defined. Inconsistent or incomplete
    information was provided. Error in line 132. Incidentally line 132 is the
    following
    cmd.execute ,arParms,129
    Any further throughts where could the problem lie?
    "Bob Barrows [MVP]" wrote:

    > I bet this would work:
    >
    > on error resume next
    > l_date=cdate(l_Date)
    > if err <> 0 then
    > response.write l_date & " is not a valid date"
    > response.end
    > end if
    > on error goto 0
    >
    > sql = " INSERT INTO tblExpense " & _
    > "(ENO, EntryDate, ContractedServiceExpense" & _
    > ",TravelExpense, PersonnelExpense) " & _
    > " VALUES(?,?,?,?,?)"
    >
    > arParms = array(l_ENO, l_Date , _
    > l_contractedserviceexpense,l_travelexpense, _
    > l_personnelexpense)
    >
    > ' assuming conn is your connection variable
    > set conn=createobject("adodb.connection")
    > conn.open "<ole db connection string>"
    >
    > set cmd=createobject("adodb.command")
    > cmd.commandtext=sql
    > set cmd.activeconnection = conn
    > cmd.execute ,arParms,129
    >
    >
    >
    > But you just probably want to keep going with the dynamic sql, right? If so,
    > open your database in Access, create a new query using the query builder to
    > create a query that works. Switch to SQL view to see what you need to make
    > your sql string look like.
    >
    > I gotta say though, you're sure going to a lot of trouble just to get this
    > dynamic sql crutch working ...
    >
    > Bob Barrows
    >
    > Jack wrote:
    > > Ray,
    > > I am using access in the backend. My problem is figure out how to
    > > manipulate the date variable so that a response.write sql throws back
    > > date as #10/01/2005#. The way I got it now, it does not allow the
    > > response.write statement to work. At this point I do not care about
    > > any date format, rather the sql statement to work properly by adding
    > > the delimiters to the date. Thanks. Regards.
    > >
    > > "Jack" wrote:
    > >
    > >> Hi,
    > >> I cannot get the date format correctly in dynamic sql statement,
    > >> after trying various ways of handling it. I need some help with the
    > >> date format in the following dynamic sql statement. Any help is
    > >> appreciated in advance. While running the asp page, I still get an
    > >> error as
    > >> Error Type:
    > >> Microsoft VBScript runtime (0x800A000D)
    > >> Type mismatch: 'Format'
    > >>
    > >> DYNAMIC SQL STATEMENT:
    > >> sql = " INSERT INTO tblExpense (ENO, EntryDate,
    > >> ContractedServiceExpense, " sql = sql & "TravelExpense,
    > >> PersonnelExpense)" sql = sql & " VALUES('" & l_ENO & "', " &
    > >> Format(l_Date, "\#mm\/dd\/yyyy\#") & ", " &
    > >> l_contractedserviceexpense & ", " &l_travelexpense & ", "
    > >> &l_personnelexpense & ")"

    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >
     
    Jack, Apr 1, 2005
    #9
  10. Verify that you have valid values for each of your parameters
    (response.write them)

    Use the appropriate type conversion functions to convert the values to the
    proper types (as I did with the date) before creating your array.

    I can't get more specific than that without knowing your table structure
    (specifically, the datatypes of the fields). I can guarantee that the date
    value is not the problem.

    Bob Barrows

    Jack wrote:
    > Bob
    > After inserting your code and making the necessary changes I got the
    > following error coming:
    > d (0x800A0E7C)
    > Parameter object is improperly defined. Inconsistent or incomplete
    > information was provided. Error in line 132. Incidentally line 132 is
    > the following
    > cmd.execute ,arParms,129
    > Any further throughts where could the problem lie?
    > "Bob Barrows [MVP]" wrote:
    >
    >> I bet this would work:
    >>
    >> on error resume next
    >> l_date=cdate(l_Date)
    >> if err <> 0 then
    >> response.write l_date & " is not a valid date"
    >> response.end
    >> end if
    >> on error goto 0
    >>
    >> sql = " INSERT INTO tblExpense " & _
    >> "(ENO, EntryDate, ContractedServiceExpense" & _
    >> ",TravelExpense, PersonnelExpense) " & _
    >> " VALUES(?,?,?,?,?)"
    >>
    >> arParms = array(l_ENO, l_Date , _
    >> l_contractedserviceexpense,l_travelexpense, _
    >> l_personnelexpense)
    >>
    >> ' assuming conn is your connection variable
    >> set conn=createobject("adodb.connection")
    >> conn.open "<ole db connection string>"
    >>
    >> set cmd=createobject("adodb.command")
    >> cmd.commandtext=sql
    >> set cmd.activeconnection = conn
    >> cmd.execute ,arParms,129
    >>
    >>
    >>
    >> But you just probably want to keep going with the dynamic sql,
    >> right? If so, open your database in Access, create a new query using
    >> the query builder to create a query that works. Switch to SQL view
    >> to see what you need to make your sql string look like.
    >>
    >> I gotta say though, you're sure going to a lot of trouble just to
    >> get this dynamic sql crutch working ...
    >>
    >> Bob Barrows
    >>
    >> Jack wrote:
    >>> Ray,
    >>> I am using access in the backend. My problem is figure out how to
    >>> manipulate the date variable so that a response.write sql throws
    >>> back
    >>> date as #10/01/2005#. The way I got it now, it does not allow the
    >>> response.write statement to work. At this point I do not care about
    >>> any date format, rather the sql statement to work properly by adding
    >>> the delimiters to the date. Thanks. Regards.
    >>>
    >>> "Jack" wrote:
    >>>
    >>>> Hi,
    >>>> I cannot get the date format correctly in dynamic sql statement,
    >>>> after trying various ways of handling it. I need some help with the
    >>>> date format in the following dynamic sql statement. Any help is
    >>>> appreciated in advance. While running the asp page, I still get an
    >>>> error as
    >>>> Error Type:
    >>>> Microsoft VBScript runtime (0x800A000D)
    >>>> Type mismatch: 'Format'
    >>>>
    >>>> DYNAMIC SQL STATEMENT:
    >>>> sql = " INSERT INTO tblExpense (ENO, EntryDate,
    >>>> ContractedServiceExpense, " sql = sql & "TravelExpense,
    >>>> PersonnelExpense)" sql = sql & " VALUES('" & l_ENO & "', " &
    >>>> Format(l_Date, "\#mm\/dd\/yyyy\#") & ", " &
    >>>> l_contractedserviceexpense & ", " &l_travelexpense & ", "
    >>>> &l_personnelexpense & ")"

    >>
    >> --
    >> Microsoft MVP - ASP/ASP.NET
    >> Please reply to the newsgroup. This email account is my spam trap so
    >> I
    >> don't check it very often. If you must reply off-line, then remove
    >> the "NO SPAM"


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Apr 1, 2005
    #10
  11. Jack

    Jack Guest

    Got you. Let me go through the code. Thanks.

    "Bob Barrows [MVP]" wrote:

    > Verify that you have valid values for each of your parameters
    > (response.write them)
    >
    > Use the appropriate type conversion functions to convert the values to the
    > proper types (as I did with the date) before creating your array.
    >
    > I can't get more specific than that without knowing your table structure
    > (specifically, the datatypes of the fields). I can guarantee that the date
    > value is not the problem.
    >
    > Bob Barrows
    >
    > Jack wrote:
    > > Bob
    > > After inserting your code and making the necessary changes I got the
    > > following error coming:
    > > d (0x800A0E7C)
    > > Parameter object is improperly defined. Inconsistent or incomplete
    > > information was provided. Error in line 132. Incidentally line 132 is
    > > the following
    > > cmd.execute ,arParms,129
    > > Any further throughts where could the problem lie?
    > > "Bob Barrows [MVP]" wrote:
    > >
    > >> I bet this would work:
    > >>
    > >> on error resume next
    > >> l_date=cdate(l_Date)
    > >> if err <> 0 then
    > >> response.write l_date & " is not a valid date"
    > >> response.end
    > >> end if
    > >> on error goto 0
    > >>
    > >> sql = " INSERT INTO tblExpense " & _
    > >> "(ENO, EntryDate, ContractedServiceExpense" & _
    > >> ",TravelExpense, PersonnelExpense) " & _
    > >> " VALUES(?,?,?,?,?)"
    > >>
    > >> arParms = array(l_ENO, l_Date , _
    > >> l_contractedserviceexpense,l_travelexpense, _
    > >> l_personnelexpense)
    > >>
    > >> ' assuming conn is your connection variable
    > >> set conn=createobject("adodb.connection")
    > >> conn.open "<ole db connection string>"
    > >>
    > >> set cmd=createobject("adodb.command")
    > >> cmd.commandtext=sql
    > >> set cmd.activeconnection = conn
    > >> cmd.execute ,arParms,129
    > >>
    > >>
    > >>
    > >> But you just probably want to keep going with the dynamic sql,
    > >> right? If so, open your database in Access, create a new query using
    > >> the query builder to create a query that works. Switch to SQL view
    > >> to see what you need to make your sql string look like.
    > >>
    > >> I gotta say though, you're sure going to a lot of trouble just to
    > >> get this dynamic sql crutch working ...
    > >>
    > >> Bob Barrows
    > >>
    > >> Jack wrote:
    > >>> Ray,
    > >>> I am using access in the backend. My problem is figure out how to
    > >>> manipulate the date variable so that a response.write sql throws
    > >>> back
    > >>> date as #10/01/2005#. The way I got it now, it does not allow the
    > >>> response.write statement to work. At this point I do not care about
    > >>> any date format, rather the sql statement to work properly by adding
    > >>> the delimiters to the date. Thanks. Regards.
    > >>>
    > >>> "Jack" wrote:
    > >>>
    > >>>> Hi,
    > >>>> I cannot get the date format correctly in dynamic sql statement,
    > >>>> after trying various ways of handling it. I need some help with the
    > >>>> date format in the following dynamic sql statement. Any help is
    > >>>> appreciated in advance. While running the asp page, I still get an
    > >>>> error as
    > >>>> Error Type:
    > >>>> Microsoft VBScript runtime (0x800A000D)
    > >>>> Type mismatch: 'Format'
    > >>>>
    > >>>> DYNAMIC SQL STATEMENT:
    > >>>> sql = " INSERT INTO tblExpense (ENO, EntryDate,
    > >>>> ContractedServiceExpense, " sql = sql & "TravelExpense,
    > >>>> PersonnelExpense)" sql = sql & " VALUES('" & l_ENO & "', " &
    > >>>> Format(l_Date, "\#mm\/dd\/yyyy\#") & ", " &
    > >>>> l_contractedserviceexpense & ", " &l_travelexpense & ", "
    > >>>> &l_personnelexpense & ")"
    > >>
    > >> --
    > >> Microsoft MVP - ASP/ASP.NET
    > >> Please reply to the newsgroup. This email account is my spam trap so
    > >> I
    > >> don't check it very often. If you must reply off-line, then remove
    > >> the "NO SPAM"

    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >
     
    Jack, Apr 1, 2005
    #11
    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. Brian Candy
    Replies:
    2
    Views:
    1,172
    Janaka
    Feb 18, 2004
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,278
    Michael Borgwardt
    May 30, 2004
  3. Berehem
    Replies:
    4
    Views:
    561
    Lawrence Kirby
    Apr 28, 2005
  4. get/format date field

    , Jan 24, 2005, in forum: Javascript
    Replies:
    4
    Views:
    168
    Dr John Stockton
    Jan 26, 2005
  5. Sound
    Replies:
    2
    Views:
    453
    Randy Webb
    Sep 28, 2006
Loading...

Share This Page