Inserting a Date into an Access200 database 1

[binky]

I have been trying for days now to insert a date (user input from an ASP page) into an Access2000 database. I keep getting a syntax error.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

Here is my INSERT INTO statement...
SQLstmt = "INSERT INTO Tech_Hours(FK_Tech_ID, FK_Type_ID, FK_Client_ID, [Date], Rate, Hours, Details)"
SQLstmt = SQLstmt & " VALUES("
SQLstmt = SQLstmt & "'" & TechID & "',"
SQLstmt = SQLstmt & "'" & CType & "',"
SQLstmt = SQLstmt & "'" & Location & "',"
SQLstmt = SQLstmt & "" & TDate & ","
SQLstmt = SQLstmt & "'" & Rate & "',"
SQLstmt = SQLstmt & "'" & Hours & "',"
SQLstmt = SQLstmt & "'" & Details & "'"
SQLstmt = SQLstmt & ""

I have very little experience with .asp pages or with VBScript. Any help as to how I can make this work would be greatly appreciated!

Oh BTW my date format is Short Date. "dd/mm/yy"

Thanks in advance!
Ken

 

[JrClown]

Since in my forms, the date is always todays date. I create a date session as follows:

<%
reqdate = session(&quot;date&quot
SQL=&quot;INSERT INTO myTable (dateentered, name) Values (&quot;&_
&quot;#&quot;& date & &quot;#, &quot; &_
&quot;'&quot;& request.form(&quot;name&quot& &quot;')&quot;

Dim myRSset
myRSset=server.createobject(&quot;adodb.recordset&quot
myRSset.open sql, &quot;dsn=mydatabase&quot;
%>
The only reason I place my date in a session is because I want to carry it to my next page and keep the date entered by all users. But you don't have to session the date, you can simply insert it.

I hope this helps QUOTE OF THE DAY
Don't waste time telling people what you are doing or what you are going to do. Results have a way of informing the world.

<%
Jr Clown )
%>

 

[thieg]

simply put a field call date in your database and Now() for the default value.
Don't take care about the date in your insert request, access do it automaticaly...

 

[binky]

Thank you both for your suggestions. I have no problems getting &quot;Today's&quot; date into my database. What I need to do is allow the user to enter their own date. It is to track staff hours and therefor they need to be able to enter any date.

Any thought on this?

Ken

 

[JrClown]

Make sure that your Access date field is formatted for a short date and this is how you would insert a date into your database. The oranged dateentered below is the name of my form field.
<%
SQL=&quot;INSERT INTO myTable (dateentered, name) Values (&quot;&_
&quot;#&quot;& date & &quot;#, &quot; &_
&quot;'&quot;& request.form(&quot;name&quot& &quot;')&quot;

Dim myRSset
myRSset=server.createobject(&quot;adodb.recordset&quot
myRSset.open sql, &quot;dsn=mydatabase&quot;
%>
let me know if it helps.
QUOTE OF THE DAY
Don't waste time telling people what you are doing or what you are going to do. Results have a way of informing the world.

<%
Jr Clown )
%>

 

[binky]

Thanks JrClown, I appreciate all of the help. I have already tried what you suggested. No luck. I think I might have conflicting short date formates within my system. I read somewhere that the ODBC connection can cause a problem if it's set up with the wrong short date format. I think I'm going to make my users enter the info on the day of instead of trying to allow entering in their own date.

Any other thought are more than welcome though!! Thanks again for the help!

Ken

 

[JrClown]

Could it be a server problem binky? I have yet to hear about the ODBC problem you mention.. Good luck QUOTE OF THE DAY
Don't waste time telling people what you are doing or what you are going to do. Results have a way of informing the world.

<%
Jr Clown )
%>

 

[Pandyon]

Binky,

You DID add the &quot;#&quot; symbols to the beginning and end of your Date string? Right??? I don't see it in the code you gave above.

Mark