"INSERT INTO" syntax error for time valued field

[new2unix]

Hi,

My asp "INSERT INTO" codes has no problem adding data into an Access2K table (about 9 fields in total) until I tried to add another field, say, "TimeStamp" to a Date/Time field in the table. The data in this hidden form field comes from Time(). While the 9th field, "DateStamp", from Date(), did not cause any problem, I kept getting browser error saying there is some syntax error whenever I included the "TimeStamp" field. Is there special syntax associated with adding a time value from ASP to Access db?

Thanks

Mike

 

[ecannizzo]

Can you response.write your sql and show it to us?

 

[new2unix]

Hi,

The following is the partial listing of the code, it worked up to the last part of the sSQL when I tried to add time into the TimeSampl field in db. If I take it out, the db would be updated properly.

<%
sTable = &quot;tblGuestbook&quot;

' Get the name of the file executing this routine
sScript = Request.ServerVariables(&quot;SCRIPT_NAME&quot

IF NOT isempty( Request.Form ( &quot;Submit&quot; )) THEN

' Get the Form Variables
sFirstName = TRIM( Request( &quot;FirstName&quot; ) )
sLastName = TRIM( Request( &quot;LastName&quot; ) )
sEmailAddr1 = TRIM( Request( &quot;EmailAddr1&quot; ) )
sOrganization= TRIM( Request( &quot;Organization&quot; ) )
sURL1 = TRIM( Request( &quot;URL1&quot; ) )
sCategory = TRIM( Request( &quot;Category&quot; ) )
sSubject = TRIM( Request( &quot;Subject&quot; ) )
sComment = TRIM( Request( &quot;Comment&quot; ) )
sDateStamp = Date()
sTimeStamp = Time()

Set oConn = Server.CreateObject(&quot;ADODB.Connection&quot
oConn.Open getConnString()

sSQL = &quot;INSERT INTO &quot; & sTable
sSQL = sSQL & &quot; ( &quot;
sSQL = sSQL & &quot;FirstName, LastName, EmailAddr1, Organization, URL1, Category, Subject, Comment, DateStamp&quot;
ssQL = sSQL & &quot; ) &quot;
sSQL = sSQL & &quot;VALUES&quot;
sSQL = sSQL & &quot; ( &quot;
sSQL = sSQL & &quot;'&quot; & sFirstName & &quot;', &quot;
sSQL = sSQL & &quot;'&quot; & sLastName & &quot;', &quot;
sSQL = sSQL & &quot;'&quot; & sEmailAddr1 & &quot;', &quot;
sSQL = sSQL & &quot;'&quot; & sOrganization & &quot;', &quot;
sSQL = sSQL & &quot;'&quot; & sURL1 & &quot;', &quot;
sSQL = sSQL & &quot;'&quot; & sCategory & &quot;', &quot;
sSQL = sSQL & &quot;'&quot; & sSubject & &quot;', &quot;
sSQL = sSQL & &quot;'&quot; & sComment & &quot;', &quot;
sSQL = sSQL & &quot;'&quot; & sDateStamp & &quot;', &quot;
sSQL = sSQL & &quot;'&quot; & sTimeStamp & &quot;' &quot;
sSQL = sSQL & &quot; ) &quot;

oConn.Execute sSQL

oConn.Close
Set oConn = Nothing
END IF
%>

 

[new2unix]

Minor correction:

sSQL = sSQL & &quot;FirstName, LastName, EmailAddr1, Organization, URL1, Category, Subject, Comment, DateStamp&quot;

should have a TimeStamp at the end:

sSQL = sSQL & &quot;FirstName, LastName, EmailAddr1, Organization, URL1, Category, Subject, Comment, DateStamp, TimeStamp&quot;

Mike

 

[ecannizzo]

Can you response.write the sql to the browser so that we can see the actual data that its trying to insert?

 

[new2unix]

Hi,

This is the Response.Write of my SQL string and the browser returned error:

INSERT INTO tblGuestbook ( FirstName, LastName, EmailAddr1, Organization, URL1, Category, Subject, Comment, DateStamp, TimeStamp ) VALUES ( 'FirstName Text', 'LastName Text', 'email@address', 'Organization Text', 'URL Text', 'Other', 'Subject Text', 'Comment Line 1', '7/25/01', '9:29:14 AM' )


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

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


Thanks

Mike

/projects/iisig/guestbook/guestbook_functions.asp, line 84

 

[TechnicalAnalysis]

Did you set the data type of timestamp field to Long Time?
Also try to put # instead of ' for date and time fields on the SQL statement.

 

[new2unix]

Hi,

The TimeStamp field in the db has the format of Long Time.

If I take this TimeStamp field out and leave the DateStamp in, the record would be added without error. Just tried to wrap the TimeStamp value with either # sign without single quote or '# ... #', still got the syntax error.

Thanks

Mike

 

[Ody]

check your computers &quot;Regional Settings Properties&quot; dialog box in Windows Control Panel. This should be the same as your &quot;Long Time&quot; format specified in your database.

you may also try to force the format of the time stamp (dont know the Syntax in access but MS-SQL is something like ('09:15:01 AM', 'HH:MM:SS AM')