0

Here is what I am trying: 

sSQL = "INSERT into users (firstName, lastName, email, password, zip, country, company, industry, revenue, timestamp) 
        VALUES('" & fname & "','" & lName & "','" & email & "','" & password & "','" & zip & "','" & country & "','" & company & "','" & industry & "','" & revenue & "','" & CURRENT_TIMESTAMP & "')"

Error Message:

Microsoft VBScript runtime error '800a01f4' Variable is undefined: 'CURRENT_TIMESTAMP' /register.asp, line 24

I tried DEFAULT as the timestamp, didn't work though.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rahul Desai
  • 15,242
  • 19
  • 83
  • 138
  • 4
    This will produce very hackable code by [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) – Erik Philips Apr 21 '12 at 06:15
  • Why do you think so? And how do I fix that? Thanks in advance! – Rahul Desai Apr 21 '12 at 06:19
  • what if I set firstName = `") Values('blah') GO DELETE * FROM users GO --"`. I just deleted all the users in your table. Here is a small tid bit from microsoft about how to prevent [Sql Injection](http://msdn.microsoft.com/en-us/library/ms161953.aspx). – Erik Philips Apr 21 '12 at 06:20
  • Thank you very much! Will go through it! – Rahul Desai Apr 21 '12 at 06:26
  • 2
    Take a look at [this](http://stackoverflow.com/questions/149848/classic-asp-sql-injection-protection) StackOverflow question. Lot of information on SQL injection. – Guido Gautier Apr 21 '12 at 12:23
  • FYI the TIMESTAMP data type has nothing to do with date or time. In addition to the syntax issues pointed out by others, you should change your column to be DATETIME or SMALLDATETIME. http://stackoverflow.com/questions/7154742/how-do-i-get-date-time-information-from-a-timestamp-column – Aaron Bertrand Apr 24 '12 at 15:43
  • possible duplicate of [SQL Server: Cannot insert an explicit value into a timestamp column](http://stackoverflow.com/questions/10262426/sql-server-cannot-insert-an-explicit-value-into-a-timestamp-column) – mmmmmm May 29 '14 at 11:54

3 Answers3

3

do this

... revenue & "', CURRENT_TIMESTAMP)"

CURRENT_TIMESTAMP should be part of your sql query. It is not a variable in your code.

EDIT: To make it work you should change the data type of your timestamp column to datetime.

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • I tried that, but it gave me the following error: Microsoft OLE DB Provider for SQL Server error '80040e2f' Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. /register.asp, line 37 So I replaced CURRENT_TIMESTAMP with DEFAULT, and it worked. But when I checked the database, the value was something like this: 0x0000007D5 How do I get a readable timestamp? – Rahul Desai Apr 21 '12 at 06:25
  • what db engine are you using? – juergen d Apr 21 '12 at 06:28
  • Try `getdate()` this is a build in function, that returns the current DATETIME. – Guido Gautier Apr 21 '12 at 12:24
  • @rdesai: See my update. You need to change the data type of timestamp column to datetime. – juergen d Apr 22 '12 at 05:47
1

Based upon your comments to one of the other answers, your fundamental problems is trying to insert into a timestamp column. Timestamp colums can not be INSERTed or UPDATEd Timestamp is not actually a date or time value, instead it is a row version column, handled by the db engine. You can compare against it to avoid concurrency issues.

Leave it out of your column list and it should work.

That said, you should be using sql parameters, instead of string concatenation to create your sql commands.

jmoreno
  • 12,752
  • 4
  • 60
  • 91
  • This really should be a comment since it doesn't attempt to answer the question. Good point though. – JohnFx Apr 22 '12 at 06:56
  • @JohnFx: it did answer the question, but based upon a comment he made to another answer. I expanded my answer to make it clearer : it's not working because you can't do that. – jmoreno Apr 22 '12 at 08:05
0

Try making Timestamp = [TimeStamp] instead, you might get issues when using this field name.

Control Freak
  • 12,965
  • 30
  • 94
  • 145