I have been experiencing a problem with one of my vb applications where it is crashing at a certain time of day. In my code, there are only 4 places where that could be the cause of the crash. Three of them are from SQLDataSource queries and the other is in the code behind. I am pretty sure that I don't have a problem with the code behind as I have a using block in place. Further more, inside of that block I have a try catch finally where in the finally I am Disposing the command as well as the connection and Closing the connection. I have been reading some articles that tell me that I should use a SqlDataSource "selected" event to close the connection. I gave that a try but didn't have any success. This is the error that I am receiving:
SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
This makes me feel like the "selected" event is not having a chance to get fired. So I thought I should try the "selecting" event. In it, I am trying to grab the connection string and close it. But I am not quite sure I am going down the correct path because I have been unable to catch exceptions inside of that event. Can someone out there please give me a hand with this issue I am facing?
Edit: This is an example of how I am trying to use the selected event to close the connection
If Not IsNothing(e.Exception) Then
Debug.Print("Exeception encounted while selecting for sqlData")
End If
e.ExceptionHandled = True
And here is and example of how I am trying to use the selecting event (I cannot figure out if an exception has been thrown here).
Dim sqlDataConn As SqlConnection = New SqlConnection("MYConnectionString")
sqlDataConn.Dispose()
If you want to intercept SqlException
you need to use explicit "using
", i.e. try/catch
. Then, you can determine specific issue by the Number
property.
try
catch ex as SqlException
MessageBox.Show(ex.Number)
if ex.Number = xxx then
' do something
end if
catch ex as Exception
finally
end try
if the question about handling this
If Not IsNothing(e.Exception) Then
Debug.Print("Exeception encounted while selecting for sqlData")
End If
you can check the exception type
If e.Exception IsNot Nothing AndAlso TypeOf e.Exception Is SqlException Then
.....
It would help if you did a breakpoint and found the query that was having this issue. The timeout issue is when a long-running query kills itself because it ran past the default timeout period.
A quick and dirty solution could be to just use a larger timeout in the connection strings you use, but long running queries can usually be a bad sign of an unoptimized query and should be addressed before your database grows any larger.
User contributions licensed under CC BY-SA 3.0