Writing Timestamp to Access via ASP (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

paulie -> Writing Timestamp to Access via ASP (9/10/2003 12:25:18)

Is there a way to add the Timestamp to my SQL statement when doing an Insert like so:

mySQL= mySQL & "(name,email,company) "
mySQL= mySQL & "VALUES ('" & Request.Form("name") & "','"
mySQL= mySQL & Request.Form("email") & "'"
mySQL= mySQL & ",'" & Request.Form("company")
mySQL= mySQL & "','" & Request.Server("Timestamp") & "')"

As a guess, I tried the request.server object and got an "object not supported" error. My DB is Access 2000.



rdouglass -> RE: Writing Timestamp to Access via ASP (9/10/2003 12:53:16)

quote:

mySQL= mySQL & "','" & Request.Server("Timestamp") & "')"


Try this:

mySQL= mySQL & "',#'" & Now() & "#)"



rdouglass -> RE: Writing Timestamp to Access via ASP (9/10/2003 12:55:05)

quote:

mySQL= mySQL & "(name,email,company) "


OOPS...gotta' make sure you add your Access field to the fieldlist:

mySQL= mySQL & "(name,email,company,timeFieldName) "



paulie -> RE: Writing Timestamp to Access via ASP (9/10/2003 13:20:19)

Thanks Roger,

When I try

mySQL= mySQL & "',#'" & Now() & "#)"

I get "Extra ) in query expression '#'9/10/2003 12:09:53 PM#)'.

So when I remove the extra right parens I get:

Syntax error in INSERT INTO statement

Here is the whole shebang in case you can spot any other ID ten T errors:

Dim myConnString
Dim myConnection
Dim mySQL
myConnString = Application("cadregister_ConnectionString")
Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open myConnString
mySQL= "INSERT INTO Results "
mySQL= mySQL & "(name,email,company,[Timestamp]) "
mySQL= mySQL & "VALUES ('" & Request.Form("name") & "','"
mySQL= mySQL & Request.Form("email") & "'"
mySQL= mySQL & ",'" & Request.Form("company")
mySQL= mySQL & "',(#'" & Now() & "#)"
myConnection.Execute mySQL
myConnection.Close
Set myConnection = Nothing

The SQL Syntax Gods must be hungry today...



rdouglass -> RE: Writing Timestamp to Access via ASP (9/10/2003 13:46:23)

Do you actually have a field "Timestamp" and is it a Date/Time field?

Also this line:

mySQL= mySQL & "',(#'" & Now() & "#)"

has an extra paren. Try:

mySQL= mySQL & "',#'" & Now() & "#)"



paulie -> RE: Writing Timestamp to Access via ASP (9/10/2003 13:52:31)

quote:

Do you actually have a field "Timestamp" and is it a Date/Time field?


Affirmative. And as "Timestamp" is a reserved word in Access and SQL, I have tried it with and without the square brackets.

Still getting the obscure " Syntax error in INSERT INTO statement.", so I'll keep throwing variations at it until I find one it likes.



rdouglass -> RE: Writing Timestamp to Access via ASP (9/10/2003 14:19:15)

quote:

Dim myConnString
Dim myConnection
Dim mySQL
myConnString = Application("cadregister_ConnectionString")
Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open myConnString
mySQL= "INSERT INTO Results "
mySQL= mySQL & "(name,email,company,[Timestamp]) "
mySQL= mySQL & "VALUES ('" & Request.Form("name") & "','"
mySQL= mySQL & Request.Form("email") & "'"
mySQL= mySQL & ",'" & Request.Form("company")
mySQL= mySQL & "',(#'" & Now() & "#)"
myConnection.Execute mySQL
myConnection.Close
Set myConnection = Nothing


Try putting a response.write in there to see what it spits out:

Dim myConnString
Dim myConnection
Dim mySQL
myConnString = Application("cadregister_ConnectionString")
Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open myConnString
mySQL= "INSERT INTO Results "
mySQL= mySQL & "(name,email,company,[Timestamp]) "
mySQL= mySQL & "VALUES ('" & Request.Form("name") & "','"
mySQL= mySQL & Request.Form("email") & "'"
mySQL= mySQL & ",'" & Request.Form("company")
mySQL= mySQL & "',(#'" & Now() & "#)"

response.write mySQL

myConnection.Execute mySQL
myConnection.Close
Set myConnection = Nothing


Then can you post back the results of the Response.write?



paulie -> RE: Writing Timestamp to Access via ASP (9/10/2003 14:24:07)

Here is the error in full:

INSERT INTO Results (name,email,company,Timestamp) VALUES ('My Name','myemail@address.com','My Company',#'9/10/2003 1:17:35 PM#)
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

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

/backupcad_library.asp, line 45



paulie -> RE: Writing Timestamp to Access via ASP (9/10/2003 14:33:11)

GOT IT!!!

Here is the code that succeeded:

mySQL= mySQL & "(name,email,company,[Timestamp]) "
mySQL= mySQL & "VALUES ('" & Request.Form("name") & "','"
mySQL= mySQL & Request.Form("email") & "'"
mySQL= mySQL & ",'" & Request.Form("company")
mySQL= mySQL & "','" & Now() & "')"

The square brackets were apparently necessary in the first line, but not the pound signs in the last. Go figure.

Thanks for all your persistence Roger! I also appreciate your suggestion to do the response.write statement there. I will use that a lot in future troubleshooting situations, of which I seem destined for many...[:D]



rdouglass -> RE: Writing Timestamp to Access via ASP (9/10/2003 15:13:38)

I guess I assumed that "Timestamp" was a Date/Time field in Access...[:o]



paulie -> RE: Writing Timestamp to Access via ASP (9/10/2003 15:26:28)

It is!

[image]local://upfiles/1007/Wu59903.jpg[/image]



rdouglass -> RE: Writing Timestamp to Access via ASP (9/10/2003 15:31:29)

And your Timestamp values are still being written to the DB without the # sign delimiters? ..and this is an Access DB?

I've just never seen it work before without 'em...[:o][&:]



best -> RE: Writing Timestamp to Access via ASP (8/15/2004 15:45:00)

hi, i'm new to asp. i need some help here.i want to insert timestamp to my database(access) in my timestamp field(type is Date/Time) . i read all ya previous posts, and tried to slove it myself. well basically i was able to write out e sql statement , but was not able to insert into DB. kinda weird that i couldnt close e connection. pls look into my code. desperately need help. .ty in advance

quote:


<%
dim strName
dim strRegnick
dim strHdd
dim strConn
dim strRec
Dim strUptime
Dim strServe

Dim mySQL
Dim conn
dim adoCon

strName=Request.Form("nick")
strRegnick=Request.Form("regnick")
strHdd=Request.Form("hdd")
strConn=Request.Form("conn")
strRec=Request.Form("rec")
strUptime=Request.Form("uptime")
strServe=Request.Form("serve")


Set adoCon = Server.CreateObject("ADODB.Connection")
conn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("databases/xiso.mdb")
adoCon.Open conn

Set rs = SERVER.CreateObject("ADODB.Recordset")
mySQL = "INSERT INTO reg (nick,regnick,hdd,conn,rec,uptime,serve,[Timestamp]) VALUES ( '"& strName &"' , '"& strRegnick &"' , '"& strHdd &"' , '"& strConn &"' , '"& strRec &"', '"& strUptime &"', '"& strServe &"')"
mySQL= mySQL & "',('" & Now() & ")"

'conn.Execute mySQL
'conn.Close
'rs.Open mySQL, conn

'Set conn = Nothing

response.write mySQL
%>



Spooky -> RE: Writing Timestamp to Access via ASP (8/15/2004 16:04:01)

Please start a new thread if this doesnt work.

mySQL = "INSERT INTO reg (nick,regnick,hdd,conn,rec,uptime,serve,[Timestamp]) VALUES ( '"& strName &"' , '"& strRegnick &"' , '"& strHdd &"' , '"& strConn &"' , '"& strRec &"', '"& strUptime &"', '"& strServe &"', Now())"



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125