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 & ")" 10 1853
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 & ")"
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 & ")"
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" <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 & ")"
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" <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 & ")"
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"
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" This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Simon Brooke |
last post by:
I'm investigating a bug a customer has reported in our database
abstraction layer, and it's making me very unhappy.
Brief summary:
I have a database abstraction layer which is intended to...
|
by: koray |
last post by:
hi everyone,
in my form i have to take some date information in dd-mm-yy format.
but i don't want user to use tabs while typing. for example s/he
should simply type 280104 but 28/01/04 must...
|
by: Laurence Neville |
last post by:
This is regarding a change in the Short Date format under Hebrew Regional
Settings, that has caused huge problems in our ASP web application. The
change appears to have been introduced sometime...
|
by: deiopajw |
last post by:
I have a problem with the date format of one particular date field.
I want it to use the english UK/australian date format ie 26/10/05 and not
the U.S. format of 10/26/05.
For some strange...
|
by: David Rysdam |
last post by:
I have a large amount of data that I copy in and out of Sybase very
often. Now I also want to copy this data in and out of postgres. I
have an existing script that creates the entire database(s)...
|
by: priyasmita_guha |
last post by:
Here is a program-
/* PROGRAM: To find the difference between two dates */
#include<dos.h>
#include<stdio.h>
#include<conio.h>
#include<process.h>
void valid_date(int,int,int);
int...
|
by: dubing |
last post by:
Hello everyone,
There is a field of DATETIME type in our Access 2000 database. PHP is
used to query the data and display the query results on the web. Does
Access provide any function that can...
|
by: FAQ server |
last post by:
-----------------------------------------------------------------------
FAQ Topic - How do I format Last Modified date with javascript...
|
by: DontellTrevell via AccessMonster.com |
last post by:
HELP!!....I need to calculate the numer of days elapsed between two field.
But, the date format is YYYYMMDD. How can i accomplsh this?
--
Dontell Trevell
Message posted via AccessMonster.com...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |