DBConcurrencyException in winforms app due to munged time portion of datetime field

Question

Hey All,

I've got a winforms .net 2.0 application that's hitting a MS SQL 2008 server. I load up a DataSet from that server, expose a bunch of that dataset via bound winform controls & then push updates via SqlDataAdapters.

One such adapter is attached to a table w/two key fields RecruitID and StatusDate. StatusDate is a DateTime field on the server, and System.DateTime in the DataSet.

I'm getting DBConcurrencyExceptions lately I think b/c of the formatting of the date info. If I capture the SQL that the adapter is generating, I see WHERE conditions like:

WHERE (RecruitID = 236) AND (StatusDate = '2010-05-27 04:45:34.053')

When what they should be is:

WHERE (RecruitID = 236) AND (StatusDate = '2010-05-27 16:45:34.053')

So--hours should be in military time--that's what shows if I query the proper record out of the db in mssql management studio. I'm not sure where the translation from military to am/pm style time is happening, so I don't know where to try and stop it.

This date is being displayed in a DataGrid. The relevant DataGridColumnStyle instance has a .Format property = 'g', which turns the above date into "5/27/2010 4:45 PM" for display. If I remove that 'g' format property it doesn't make any difference.

Can anybody throw me a clue here?

Many thanks in advance!

-Roy

Answer 1

Ack--embarrassing. The date/time formatting in the SQL was off just because of the way I was capturing it. Specifically, I was calling .ToString & passing in a custom format that (I think) was doing the time conversion. The actual stuff getting sent to the db had proper values I believe.

I wound up removing some trigger-populated fields from the sqladapters CommandText SQL, and that appears to be a fix. (Though I'm still not sure just why I was getting the DBConcurrencyException.) Time will tell, perhaps.

Thanks for thinking about this!

-Roy

Private Sub RecordSQL(ByVal sender As Object, ByVal e As SqlClient.SqlRowUpdatingEventArgs) Handles sdaRecruitStatuses.RowUpdating
    Dim pvalue As String
    m_strLastSQL = e.Command.CommandText
    For Each prm As SqlClient.SqlParameter In e.Command.Parameters
        If TypeOf prm.Value Is DateTime Then
            pvalue = "'" & CType(prm.Value, DateTime).ToString("yyyy-MM-dd hh:mm:ss.fff") & "'"
        Else
            pvalue = prm.Value.ToString()
            If pvalue.Length = 0 Then pvalue = "NULL"
        End If
        m_strLastSQL = Replace(m_strLastSQL, prm.ParameterName, pvalue)
    Next
End Sub