Cannot write a Date field into Access

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I am having trouble writing date fields into an Access dbase.
My code is as follows:

<% if request.form("isSubmitted") = "yes" then %>
<%
DIM Conn,strConn,SQLstmt,RS
set Conn = server.createobject("adodb.connection")
strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("database.mdb")
Conn.open strConn
SQLstmt = "INSERT INTO TestTable (CurDate,CurDateTime)"
SQLstmt = SQLstmt & " VALUES ("
SQLstmt = SQLstmt & "#" & CurDate & "#,"
SQLstmt = SQLstmt & "#" & CurDateTime & "#"
SQLstmt = SQLstmt & ")"
Set RS = conn.execute(SQLstmt)

My database data type is set to Date/Time with CurDate = Short Date and
CurDateTime = General Date.

When I submit this I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in
INSERT INTO statement.
date.asp, line 50
It was advised to me that I needed to put "#" as the seperator for the field
instead of "'".
(Example: SQLstmt = SQLstmt & "#" & CurDate & "#," NOT SQLstmt =
SQLstmt & "'" & CurDate & "'," )

I can not get it to work either way!

Any help is appreceiated,

Mikeal
 
You're concatenating a couple of apparent variables (CurDate and CurTime)
into your SQL String. However, I see no code which defines the values of
these variables.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
http://www.takempis.com
Big Things are made up of
Lots of Little Things.
 
Try writing your query out to the page, and see what you get.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
http://www.takempis.com
Big Things are made up of
Lots of Little Things.

Mike said:
Kevin,

Sorry, this is at the top of my code:

<%
CurDate = Date()
CurDateTime = Now()
%>

I am able to do a <%response.write CurDateTime%> and it shows correctly on
my screen.

Mikeal


Kevin Spencer said:
You're concatenating a couple of apparent variables (CurDate and CurTime)
into your SQL String. However, I see no code which defines the values of
these variables.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
http://www.takempis.com
Big Things are made up of
Lots of Little Things.

Mike said:
I am having trouble writing date fields into an Access dbase.
My code is as follows:

<% if request.form("isSubmitted") = "yes" then %>
<%
DIM Conn,strConn,SQLstmt,RS
set Conn = server.createobject("adodb.connection")
strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("database.mdb")
Conn.open strConn
SQLstmt = "INSERT INTO TestTable (CurDate,CurDateTime)"
SQLstmt = SQLstmt & " VALUES ("
SQLstmt = SQLstmt & "#" & CurDate & "#,"
SQLstmt = SQLstmt & "#" & CurDateTime & "#"
SQLstmt = SQLstmt & ")"
Set RS = conn.execute(SQLstmt)

My database data type is set to Date/Time with CurDate = Short Date and
CurDateTime = General Date.

When I submit this I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in
INSERT INTO statement.
date.asp, line 50
It was advised to me that I needed to put "#" as the seperator for the field
instead of "'".
(Example: SQLstmt = SQLstmt & "#" & CurDate & "#," NOT
Click to expand...
SQLstmt
=
SQLstmt & "'" & CurDate & "'," )

I can not get it to work either way!

Any help is appreceiated,

Mikeal
Click to expand...
Click to expand...
Click to expand...
 
I figured it out...

I was attempting to write the variable CurDateTime to the dbase when I
needed to write the field name SubmittedDate.

Thanks for your input.

Mikeal

Kevin Spencer said:
No, I mean Response.Write SQLStmt

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
http://www.takempis.com
Big Things are made up of
Lots of Little Things.

Mike said:
If you mean a <%response.write CurDate%> ..... its works fine.


Kevin Spencer said:
Try writing your query out to the page, and see what you get.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
http://www.takempis.com
Big Things are made up of
Lots of Little Things.

Kevin,

Sorry, this is at the top of my code:

<%
CurDate = Date()
CurDateTime = Now()
%>

I am able to do a <%response.write CurDateTime%> and it shows
Click to expand...
correctly
on
my screen.

Mikeal


You're concatenating a couple of apparent variables (CurDate and
CurTime)
into your SQL String. However, I see no code which defines the
Click to expand...
values
of
these variables.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
http://www.takempis.com
Big Things are made up of
Lots of Little Things.

I am having trouble writing date fields into an Access dbase.
My code is as follows:

<% if request.form("isSubmitted") = "yes" then %>
<%
DIM Conn,strConn,SQLstmt,RS
set Conn = server.createobject("adodb.connection")
strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("database.mdb")
Conn.open strConn
SQLstmt = "INSERT INTO TestTable (CurDate,CurDateTime)"
SQLstmt = SQLstmt & " VALUES ("
SQLstmt = SQLstmt & "#" & CurDate & "#,"
SQLstmt = SQLstmt & "#" & CurDateTime & "#"
SQLstmt = SQLstmt & ")"
Set RS = conn.execute(SQLstmt)

My database data type is set to Date/Time with CurDate = Short Date
and
CurDateTime = General Date.

When I submit this I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error
'80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error
in
INSERT INTO statement.
date.asp, line 50
It was advised to me that I needed to put "#" as the seperator
Click to expand...
for
the
field
instead of "'".
(Example: SQLstmt = SQLstmt & "#" & CurDate & "#," NOT
SQLstmt
=
SQLstmt & "'" & CurDate & "'," )

I can not get it to work either way!

Any help is appreceiated,

Mikeal
Click to expand...
Click to expand...
Click to expand...
 
Back
Top