Discuss "Lock page/recordset?" in the ASP Development forum.

Rebelle · #31 October 27th, 2008, 09:40 AM

just a bit more info...

weird but it looks like i am able to change some records but others are not changing...

very very strange....
i go and edit one record and it will change/update the record but then if i go and try to reedit again it doesn't update...some will continue to update but others won't. and some others won't change at all.

i added a new record to see if maybe that was the issue...but no luck...it let me change that record but only like 3 times then stopped. other records...i was able to change about 5 times then it just stops. and there are some it still won't change.

Rebelle · #32 October 27th, 2008, 01:22 PM

Quote:

Originally Posted by Wolffy

Here's what I would do.
In your result page (the one with the table):

Code:

<TD><a href="edit.asp?MyID=<%=rs("MyID")%>">edit</a></TD>

Then in your edit page, select the record for editing thus:

Code:

sSql = "SELECT MyID,Cast(RecordDT as float) as RecordDTValue,Region,District, etc... FROM tblTEST"
sSql = sSql & " WHERE MyID='" & sMyID & "'"
:
originalDT = rs("RecordDTValue")

Then when you go to do the update:

Code:

:
sSql = sSql & "WHERE MyID='" & sMyID & "' AND RecordDT = Cast(" & originalDT & " as DateTime)"

And that should work.

OriginalDT should be a float (i.e. 34531.34324 or some such rot) so quotes aren't necessary

Still, I don't know why the milliseconds are being trucated -- I've never had to do it this way.

Hey Wolffy,
Is there a way you can test the above? It appears I'm getting the value in milliseconds but not sure why only sometimes it works and not other times. I think I may have broke it now because i'm not able to updating any of the records.

Wolffy · #33 October 27th, 2008, 01:38 PM

On your update query do you have:
Update tblTest Set ..., RecordDT = GetDate()?

Also, when you go back to the page with the grid you are re-driving the query to get the updated time value for RecordDT?

Short of that, do you want to send me your code for the 2 pages? I think I need to look at more that just snippets to suss this one out.

Rebelle · #34 October 27th, 2008, 01:45 PM

yup...like this on the update portion:

Code:

RecordDT = (getdate())

I can see the date/time change when it does update and then doesn't change when it doesn't.

Have to jet at 1 but let me clean pages up and i'll send them...may be tomorrow...meetings

.

Thanks so much!

Wolffy · #35 October 29th, 2008, 01:29 PM

OK, let's set the problem with datetime values to rest here.

I wrote some sample ASP code and did a little research. The problem is that SQL stores time values down to the millisecond -- VB doesn't understand milliseconds and so they get stripped off; which is why time values are displayed as 10/29/2008 12:21:39 PM on your page. The problem then is when you pass this value back to SQL, it converts it back to a datetime, but with .000 milliseconds and thus your WHERE clause fails.

Moral here is when working with time values that you need to do an IS EQUAL TO comparison on, always convert it to a FLOAT in the SQL statement. When you need to display the timestamp value for debugging purposes, display it as the FLOAT so you can see the fractional part. If you need it for human usage as well, then have the QUERY return it both as a DATETIME and as a FLOAT

Another idea here would be to actually STORE to timestamp as a FLOAT in the table (a datetime value actually is a FLOAT under the covers). Of course you would need to convert the gettime() value to a FLOAT (unless this is a conversion that SQL will do implicitly <time passes> which it doesn't, I just tried it).

Now, none of this explains why you are having a problem with being able to update a record only once or just a few times. I think we are back to displaying the timestamps just prior to the UPDATE logic -- but in FLOAT format.

Rebelle · #36 October 29th, 2008, 02:02 PM

Hey Wolffy,

Tell me if this makes sense to you.....i went back to original code that i sent you that you helped me with where it is changing to float....and only made the following change on the editupdate page and i think its working...testing now.

Code:

sSql = sSql & " WHERE MyID='" & sMyID & "' AND RecordDT like Cast(" & originalDT & " as DateTime) "

Wolffy · #37 October 29th, 2008, 03:12 PM

Hmmm, not really. Wouldn't that be the same as IS EQUAL TO if you aren't using a wildcard? Tho, there could be some semantics of LIKE that I don't know about.

Rebelle · #38 October 30th, 2008, 03:34 PM

Not sure...but tested pretty thoroughly....results, if I use = it only allows edit sometimes....when I use LIKE, it works fine when editing and when editing with 2 users editing same record, will not allow second user to overwrite first users data. I'm going to leave with LIKE since I can't make it work with = .

Thanks so much for your help and your patience Wolffy!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to retrieve unknown recordset field names in table?	BLaaaaaaaaaarche	ASP Development	2	August 10th, 2008 08:33 AM
Help with asp display / recordset (troubleshoot)	Rebelle	ASP Development	27	June 30th, 2008 11:19 AM
go page to page questionaire app	peebman2000	.Net Development	3	April 26th, 2008 04:42 PM

Discuss "Lock page/recordset?" in the ASP Development forum.

ASP Development - Learn coding practices and tips to get the best out of your Active Server Pages (ASP). Visit the ASP Development forum for help with ASP/VBScript and ASP/JScript applications.