.NET DateTime and SQL Server DateTime problem
I have the following if statement in an SQL stored procedure:IF (@Image = 1 AND @DateTimeUploaded = (SELECT ImageMainDateTime FROM Images WHERE UserID = @UserID))
The value of the @DateTimeUpdated variable was taken from the ImageMainDateTime field when the data was requested by a ASP.NET webpage and is passed back here when that page is submitted so I can compare what the DateTime was when the data was requested with what it is now, in other words I’m doing a concurrency check (I can’t use TimeStamp for many reasons, just trust me on that).
Now the problem I’m having is the DateTime comparison always fails even though I know the DateTime hasn’t changed. I’ve stepped through my code meticulously and I know it hasn’t changed, in any case as the system is only being tested I know that no one else can access it so no changes can be made other than by me.
So why is this failing? Is there something I don’t know about DateTime comparisons? Am I missing something here?
Any help much appreciated.
Do you have multiple entries in your Images table for a single user?
No just one for each user.
Im wondering if there isn't some kind of type conversion issue... did you try separating the query?
DECLARE @blah DATETIME
SELECT @blah = ImageMainDateTime FROM Images WHERE UserID = @UserID
Then your conditional statement, but with @blah instead of the query in parenthesis?
I know I'm not being very useful :) But who knows...
Thanks Shados, and you are being helpful as I'm all out of ideas!!! I did try breaking up the query in query analyzer and I did notice that the DateTime being returned from the table included milliseconds even though those milliseconds do not appear in the field in the table itself or when the DateTime is being passed to and from the ASP.NET webpage. Still I assumed this was causing the comparison failure so I altered the if statement to do a <= comparison like so:
IF (@Image = 1 AND @DateTimeUploaded <= (SELECT ImageMainDateTime FROM Images WHERE UserID = @UserID))
And this did work but I'm unsure of what pitfalls there might be doing it this way so I changed it back.
Its because in the background, datetimes ARE stored in milliseconds... what you see when you browse it isn't how it is. Its normally not an issue, because if you select the value, you'll get back those milliseconds too, and things will match up, but I'm not sure how you're manipulating the date on the .NET side, as long as you're not converting the date to string in any part of the operations.
Ok, I was converting the DateTime values to objects in my class (I had an issue with null values which is why) but I've just changed them to DateTime values and the problem still occurred, however once the values reach the GridView I am storing them in a hidden field where I pick them up again when passing the data back to sql server, so I assume this is where it is going wrong as I believe in a hidden field the data will be held as a string. Is that correct? If so what should I store them in once they are in the GridView to ensure they stay as DateTime values that the database will be ok with?
I'm starting to think that maybe I should just have an int field instead of a DateTime field in the database table. I can retreive the int field data, and should the user make any changes in the meantime I simply increment it. I can then make a comparison with the int data value I returned with what is there when updating, which will be different if the user made changes in the meantime, I'm sure this would be far less of a headache.
In the database, the date is -already- a numeric value :) (Its the amount of milliseconds since 1970 or some such).
For null values, you know you can simply declare your date as nullable? In C# its simply DateTime? Blah; (notice the interrogation mark). In VB.NET, you use the Nullable generic type.
For storing the date, why don't you simply store them in the ViewState instead of a custom hidden field? That way you will concerve type information.
If you MUST use a normal hidden field (I still heavily suggest ViewState, so make sure you have a REALLY good reason not to use it), the DateTime type has a "ToBinary" method, which will return a representation as a "long" of the date. You can then later restore it using DateTime.FromBinary. That should make sure you don't have any date format issues.
Thanks Shados, have managed to sort it out with the ToBinary FromBinary methods you suggested and with a bit of messing about. I tried the nullable DateTime but as the values are being assigned in a class constructor in the data layer I couldn't get that to work. Also wasn't sure about how to use viewstate to hold the value for each row in a GridView, my knowledge isn't on your level I'm afraid, so I was stuck with the hidden fields. Thanks tremendously for your help though, much appreciated.
Ahh, if you're needing one per row, you could have stuck an array in viewstate, but thats awkward... The solution you used is just fine. Thats why I made sure to give you several options, so that at least one would suit your need. Doesn't have anything to do with a lower level of knowledge... what you did is just fine :)