ASP SQL timestamp UPDATE not working??

[gradinumcp]

Hi! I want to update a TimeStamp field in my database to this format:-5/11/2005 10:00:00 AM.

So in my ASP code I have the following form. It produces an input box where the time has to be entered and the AM/Pm has to be selected froma drop-down menu.

***************************************

<form ACTION ="" method=POST name="Page2">
<table>

<td><input class=fields maxlength="8" name="timer" Value=""></td>

<td>
<td><select name=timertype class=fields >
<option></option>
<option value="AM">AM</option>
<option value="PM">PM</option>
</select></td>
</td>

<td>e.g:&nbsp;01:30:00 PM</td>
</tr>

<tr>
<td><input type=submit value="Update"></td>
</tr>
</table>

*******************************************

The Year, Month and Day are taken from a drop-down menu.

The SQL works in the SQL query analyzer when i enter some numbers. However when i integrate it into the ASP code it doesn't.

The is the SQL query:-

mysql="Update TimeTable set TimeStamp='"& request("Year") &"-"& request("Month") &"-"& request("Day") &" "&request("timer")&" "&request("timertype")&"' where UserID='"& request("Employee") &"' AND DateStamp='"& request("Year") &"-"& request("Month") &"-"& request("Day") &"' AND TimeID='"& rs("TimeID") &"'"
con.execute(mysql)

Any clues????

 

[DotNetGnat]

try this:

mysql="Update TimeTable set [TimeStamp]='"& request("Year") &"-"& request("Month") &"-"& request("Day") &" "&request("timer")&" "&request("timertype")&"' 
where [UserID]='"& request("Employee") &"' 
AND DateStamp='"& request("Year") &"-"& request("Month") &"-"& request("Day") &"' AND TimeID='"& rs("TimeID") &"'"
con.execute(mysql)

also do a response.write mysql and post the actual string here...

-DNG

 

[gradinumcp]

Thanks for your reply. But how do i do a response.write mysql--should it be within the <%....%> tags? And what is the correct syntax and what should it ideally display?

Sorry I am new to ASP.

 

[DotNetGnat]

<%

mysql="your sql statemet"

response.write mysql
con.execute(mysql)

%>


-DNG

 

[gradinumcp]

Thanks--it does not produce any output and no error either.

 

[DotNetGnat]

what??? what happened to the response.write thing...how about just this...

<%
mysql="Update TimeTable set [TimeStamp]='"& request("Year") &"-"& request("Month") &"-"& request("Day") &" "&request("timer")&" "&request("timertype")&"'
where [UserID]='"& request("Employee") &"'
AND DateStamp='"& request("Year") &"-"& request("Month") &"-"& request("Day") &"' AND TimeID='"& rs("TimeID") &"'"
response.write "here is my sql:"
response.write "<BR/>"
response.write mysql
%>

that is...no executing the query...just printing it out

-DNG

 

[gradinumcp]

Again nothing--you think it is not going into the sql itself coz the sql is in an if statement.

Should I post the entire code here???

 

[DotNetGnat]

ok go ahead and post the entire code...

-DNG

 

[gradinumcp]

Well I kinda figured out that it was not getting into the sql becoz of an error in a javascript where a value was not being initialized.

However the SQl UPDATE still does not work.

Is there a different format for entering datetime values?

It enters the date correctly but does not enter any time value atall.

The code remains the same.

 

[DotNetGnat]

can you please post the actual sql string...i wanted to see how the actual values are being passed in the sql string...do a respone.write....

without seeing the actual string...it will be very difficult to suggest you something...

-DNG

 

[Sheco]

Timestamp is not the same thing as DateTime.

The following is from the sql books online:
------------------------------
timestamp
timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

A future release of Microsoft® SQL Server™ may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.

Microsoft® SQL Server™ 2000 introduces a rowversion synonym for the timestamp data type. Use rowversion instead of timestamp wherever possible in DDL statements. rowversion is subject to the behaviors of data type synonyms. For more information, see Data Type Synonyms.

 

[Sheco]

If you use SQL server to generate a dateime with one of its native functions like GetDate() then the date it creates has milliseconds that will not fit into a regular vb date variable.

You can either use a string or variant to hold the datetime but then you cant really use the other native vb date functions on it with CDate().

So if you need to use the vb date functions and you dont need to use the milliseconds, you can strip them off like this: convert(datetime,CONVERT(char(40),GETDATE(),120), 120)

 

[gradinumcp]

Response.write does not produce any result. However the error that it shows is:

page.asp?timer=10%3A00%3A00 & timertype=AM

when i put 10:00:00 AM. I know 3A is the hex for colon)

Any further clues???

 

[DotNetGnat]

mysql="Update TimeTable set [TimeStamp]='"& request("Year") &"-"& request("Month") &"-"& request("Day") &" "&request("timer")&" "&request("timertype")&"' 
where [UserID]='"& request("Employee") &"' 
AND DateStamp='"& request("Year") &"-"& request("Month") &"-"& request("Day") &"' AND TimeID='"& rs("TimeID") &"'"

let me interpret one of the sample strings...

mysql="Update TimeTable set [TimeStamp]='2005-08-25 10:00:00 AM' 
where [UserID]='gradi' 
AND DateStamp='2005-08-25' AND TimeID='someID' "

is that how you want to look...atleast post a sample sql string showing how you want it to look...

-DNG

 

[gradinumcp]

Here's the sql query which works in SQL query analyzer:

Update TimeTracking set TimeStamp='2005-05-11 10:00:00 AM' where UserID='83' AND DateStamp='2005-05-11' AND TimeID='23'

This Produces the following result:

TimeID UserID Description DateStamp TimeStamp
------------------------------------------------------------
23 83 Sign In 5/11/2005 5/11/2005 10:00:00 AM


In the ASP code however, the TimeStamp field only shows the Date and nothing in the time.

 

[DotNetGnat]

huh huh...there you go...then in that case you have additional quotes...try this:

mysql="Update TimeTable set [TimeStamp]='"& request("Year") &"-"& request("Month") &"-"& request("Day") &" "&request("timer")&" "&request("timertype")&"' 
where [UserID]="& request("Employee") &" 
AND DateStamp='"& request("Year") &"-"& request("Month") &"-"& request("Day") &"' AND TimeID="& rs("TimeID") &" "

i have removed additional single quotes around UserID and TimeID

-DNG

 

[DotNetGnat]

oops may be i am wrong...what is the datatype for your UserID and TimeID...if there are strings then you need single quotes...if they are integers then you dont need single quotes...

-DNG

 

[gradinumcp]

Well they are integers. However that does not work too. I think its got something to do with the way the data is being passed 10%3A00%3A00 AM to show 10:00:00 AM. Thats why it doesn't get into the database.

 

[DotNetGnat]

thats why i said you need to print out the sql string to see what actual string is being passed...i dont know hwy the response.write statement does not work...

ok try this:

mysql="Update TimeTable set [TimeStamp]='"& request("Year") &"-"& request("Month") &"-"& request("Day") &" "&[red]server.htmlencode(request("timer"))[/red]&" "&request("timertype")&"' 
where [UserID]='"& request("Employee") &"' 
AND DateStamp='"& request("Year") &"-"& request("Month") &"-"& request("Day") &"' AND TimeID='"& rs("TimeID") &"'"

or try server.urlencode() in place of server.htmlencode...

-DNG

 

[Sheco]

oh, i see... timestamp is the name of the column rather than the datatype... maybe i should learn to read.