I am having a problem inserting into a SQL server 7.0 database date field. 
I am using ASP for the front end which is linked to a visual Basic middle 
tier which then inserts into the database. my insert into field is as 
follows:

strSQL = "insert into event(event_id, date, notes, priority, 
process_reference) values (30,'" & ddate & "','" & events & "','" & 
priority & "','" & processref & "') "

the variable ddate holds the date value that I am trying to insert. This 
date value is basically going to be today's date so I just use the date() 
function in ASP to get today's date. When the user is finished with the 
form this value is passed into the VB function and put in this ddate 
variable. Even if I then cast it to a Date again to make sure it is a date 
value in VB and then isert it into SQL server it still gives the following 
error:

Microsoft OLE DB Provider for SQL Server (0x80040E07)
The conversion of a char data type to a datetime data type resulted in an 
out-of-range datetime.

SQL server obviously isn't recognizing it as a date value.
Any help appreciated thanks....

Try this....


strSQL = "insert into event(event_id, date, notes, priority,
process_reference) values (30,'" & formatdatetime(ddate) & "','" & events &
"','" &
priority & "','" & processref & "') "



----- Original Message -----
From: "paul murphy" <paulmcn01@y...>
To: "ActiveX_Data_Objects" <activex_data_objects@p...>
Sent: Monday, February 24, 2003 12:59 PM
Subject: [activex_data_objects] INSERT INTO error with date fields


> I am having a problem inserting into a SQL server 7.0 database date field.
> I am using ASP for the front end which is linked to a visual Basic middle
> tier which then inserts into the database. my insert into field is as
> follows:
>
> strSQL = "insert into event(event_id, date, notes, priority,
> process_reference) values (30,'" & ddate & "','" & events & "','" &
> priority & "','" & processref & "') "
>
> the variable ddate holds the date value that I am trying to insert. This
> date value is basically going to be today's date so I just use the date()
> function in ASP to get today's date. When the user is finished with the
> form this value is passed into the VB function and put in this ddate
> variable. Even if I then cast it to a Date again to make sure it is a date
> value in VB and then isert it into SQL server it still gives the following
> error:
>
> Microsoft OLE DB Provider for SQL Server (0x80040E07)
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime.
>
> SQL server obviously isn't recognizing it as a date value.
> Any help appreciated thanks....
>