SqlException and SqlError

How many times have you seen something like this on a database trip:

"String or binary data would be truncated."

Or a conversion error like "Can't convert char to datetime."

If you use lots of sprocs and trigger like we do, this can be a debugging nightmare.

Well, starting catching the SqlException and inspect the SqlErrors:

Public Function GetSqlExceptionMessage(ByVal exSql As SqlClient.SqlException) As String

Dim errorMessages As String = String.Empty

If Not exSql Is Nothing Then

Dim i As Integer

For i = 0 To exSql.Errors.Count - 1

errorMessages += "Index #" & i.ToString() & ControlChars.NewLine _

& "Message: " & exSql.Errors(i).Message & ControlChars.NewLine _

& "LineNumber: " & exSql.Errors(i).LineNumber & ControlChars.NewLine _

& "Source: " & exSql.Errors(i).Source & ControlChars.NewLine _

& "Procedure: " & exSql.Errors(i).Procedure & ControlChars.NewLine _

& "Severity Level: " & exSql.Errors(i).Number & ControlChars.NewLine _

& vbCr & "Please contact your system administrator if this continues."

Next i

End If

Return errorMessages

End Function ' GetSqlExceptionMessage

Doing this has saved us hours, possibly days in debugging.