«

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 & ")"

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:628506C4-7FF0-4AC0-B324-...
> 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 & ")"
>
>

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:628506C4-7FF0-4AC0-B324-...
> 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,
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 & ")"
>
>

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:AA174137-D217-4108-BFD3-...
> 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 & ")"
> >
> >

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"

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

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:AA174137-D217-4108-BFD3-...
> > 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 & ")"
> > >
> > >
>
>
>

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

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"