I spent a little too long on this rainy Sunday afternoon tripping over myself. I'm using the SqlDataSource and the FormView controls in a quick prototype. I clicked the "Use optimistic concurrency" field in the SqlDataSource configuration page so it would compare the original values before updating the database.
I was going along fine when I realized the updates didn't work, but the insert was OK. After several repeated attempts with zero rows updates, I fired up the SQL Profiler and saw my update query right there, plain as day. Why wasn't the update working?
I even grabbed the SQL out of SQL Profiler and ran it through Query Analyzer - and then bam! I could see it on my screen. The milliseconds for the date time fields were all set to zero, and the actual values in the database were non-zero values. Who was loosing the milliseconds?
Finally, it hit me. I was losing the data.
I was trying to be real smart about setting the hidden "Created" and "Modified" DateTime fields during the updating event of the SqlDataSource control. I would do a similar assignment during the update event for just the "Modified" DateTime field.
protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
DateTime now = DateTime.Now;
e.Command.Parameters["@Created"].Value = now;
e.Command.Parameters["@Modified"].Value = now;
}
As I was peering at this method, it struck me that I was inadvertently setting the fields to a value that was too granular for my SqlDataSource object. It wasn't passing back the millisecond value, so I compensated by making sure the value is always zero milliseconds, like so:
protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
DateTime now = DateTime.Now;
now = now.AddMilliseconds(1000 - now.Millisecond);
e.Command.Parameters["@Created"].Value = now;
e.Command.Parameters["@Modified"].Value = now;
}
With this adjustment, my inserts and updates are playing nicely. Yay!
So, now that i works, I'm still not real happy with it. I'd much rather have the code check a single timestamp column named "Version" than see all that bloat in there.