Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-11-06 : 07:37:45
|
| Jamie writes "I have designed a timecard system for the family business. It works fine except that there exists the potential that an entry might need to be updated.Basically a user goes to the timecard and selects a checkbox - upon hitting submit the current datetime - Now() - is entered into the db for TimeIn, TimeOut, LunchIn, LunchOut, MiscIn, MiscOut depending on what reason they selected for coming or going. This part works great.The company officer who takes care of timecards has admin tools to get into the timecards and view them. On the screen where she views a particular employee's timecard she can select a radio button for a date in the date range listed and upon hitting submit she will be taken to an update form. All of the information for that date (refer to column names above) is listed in the form's textboxes and the value is RS("TimeIn"). To better clarify: this is what she sees in the text box for TimeIn: 11/4/2002 7:59:17 AM. This is where the problems begin:say she changes the time to say 7:30:00 AM and hits submit - she gets the following error: **** Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E14) Line 1: Incorrect syntax near '7'. POST Data: cin=11%2F4%2F2002+7%3A59%3A17+AM&cout=&col=&cbl=&com=&cor=&cbm=&cbr=&update=Update+employee+timecard+information. ******Here's the Update Statment: If found=true And Len(Trim(Request.Form("update")))>0 ThenSQL = "UPDATE Timecard SET TimeIn = "&(Trim(Request.Form("cin")))&", TimeOut = "&(Trim(Request.Form("cout")))&" WHERE id= " & idSet R = conn.execute(SQL)If you could help me out with this, I'd greatly appreciate it!Thanks so much! ---jamie" |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2002-11-06 : 08:03:43
|
| In general I would say that it's a bad idea to send data directly to the SQL Server from what is requested from the ASP page. What you're doing here, if I'm reading this right, is UPDATE Timecard SET TimeIn = 11%2F4%2F2002+7%3A59%3A17+AM, timeout =WHERE id=Which is never going to work. First it looks like your time is not being decoded correctly, second you would need '' around the time, and the timeout is missing from the data completely.You may find it handy to echo what is going to be sent to the SQL Server to the ASP page to see what is going on. |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-11-06 : 09:04:19
|
| I would also wrap single quotes around the time values...UPDATE TABLE1SET TIMECOLUMN = '15/06/2002 21:00'WHERE CODE = 'x'If think that is the root of your problem.Search here also for "SET DATEFORMAT".....it will bring up some useful topics. |
|
|
|
eimaj
Starting Member
1 Post |
Posted - 2002-11-06 : 15:31:52
|
| Thank you Mr. Mist and Andrew - if figured out the problem shortly after I posted this. The quotes were the problem - once I added them it worked just fine. Thanks guys for your help!--jamie |
|
|
|
|
|
|
|
|
Updating datetime column using forms, SQL, update statement