Cannot get to format the date field correctly yet

[Jack]

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

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

 

[Aaron [SQL Server MVP]]

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

 

[Jack]

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.

 

[Ray Costanzo [MVP]]

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

 

[Bob Barrows [MVP]]

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]

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.

 

[Jack]

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.

 

[Jack]

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

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]

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

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


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