By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,474 Members | 939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,474 IT Pros & Developers. It's quick & easy.

Cannot get to format the date field correctly yet

P: n/a
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 & ")"
Jul 22 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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" <Ja**@discussions.microsoft.com> wrote in message
news:62**********************************@microsof t.com...
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 & ")"

Jul 22 '05 #2

P: n/a
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" <Ja**@discussions.microsoft.com> wrote in message
news:62**********************************@microsof t.com...
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 & ")"

Jul 22 '05 #3

P: n/a
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 & ")"

Jul 22 '05 #4

P: n/a
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" <Ja**@discussions.microsoft.com> wrote in message
news:AA**********************************@microsof t.com...
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 & ")"

Jul 22 '05 #5

P: n/a
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"
Jul 22 '05 #6

P: n/a
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"

Jul 22 '05 #7

P: n/a
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" <Ja**@discussions.microsoft.com> wrote in message
news:AA**********************************@microsof t.com...
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 & ")"


Jul 22 '05 #8

P: n/a
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"

Jul 22 '05 #9

P: n/a
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"
Jul 22 '05 #10

P: n/a
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"

Jul 22 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.