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????

 

[gradinumcp]

Ya the column name is TimeStamp and its datatype is datetime.

 

[gradinumcp]

DNG---no luck using both server.urlencode() or server.htmlencode...

 

[DotNetGnat]

can you find out why response.write is not working...is there any other way you can check what the actual sql string that is being passed from the asp page...

-DNG

 

[gradinumcp]

response.write is not working becoz its in the server-side script not client side. And the actual string being passed shows timer=10%3A00%3A00 & timertype=AM

This I found from the error in the status bar.

 

[DotNetGnat]

firstly...response.write is server side and it executes on server...

before processing on the server...you see timer=10%3A00%3A00 as the variable being passed but once it gets processed..you will see the actual value...but in anyways if you feel that this is the value that is causing problems then you can do this...

mysql="Update TimeTable set [TimeStamp]='"& request("Year") &"-"& request("Month") &"-"& request("Day") &" "&[red]replace(request("timer"),"%3A",":")[/red]&" "&request("timertype")&"' 
where [UserID]='"& request("Employee") &"' 
AND DateStamp='"& request("Year") &"-"& request("Month") &"-"& request("Day") &"' AND TimeID='"& rs("TimeID") &"'"

-DNG

 

[gradinumcp]

Tried that but no change --maybe it does pass the value in tht format.

 

[DotNetGnat]

thats what i told you...unless you see the actual string being passed...i am out of suggestions...sorry...

-DNG

 

[gradinumcp]

Well I think I figured out the problem. Turns out that after the page refreshes the recordset is empty so it does not update the database.

Anyone knows of a way to store all teh values of teh recordset so that i can use it even after the page refreshes.

I know cookies are an option--but in my case for one there are too many values to be stored and secondly i don't need those values for more than one page so they won't be tht helpful.

Any other way????