EDN Admin
Well-known member
Hi,
I have an application where I make calls to multiple SQL Server Stored Procedures, some of which take an appreciable amount of time to complete, so to keep the user aware that the program is still running and how long the specific SP has been running so
that they can abort it if its taking too long.
My initial attempt was to wrap up each call to the SPs like this example:
Dim cm As SqlCommand = Nothing<br/>
Dim Conn As SqlConnection = Nothing<br/>
Dim Tran As SqlTransaction = Nothing<br/>
Dim PrevTime As Date = Nothing<br/>
Dim ElapsedTime As Date = Nothing<br/>
Dim strSQL As String = ""<br/>
Dim Result As IAsyncResult = Nothing
Try
Conn = New SqlConnection(strCnn)<br/>
Conn.Open()
cm = New SqlCommand("proc_Name_Being_Called", Conn)<br/>
cm.CommandType = CommandType.StoredProcedure<br/>
cm.CommandTimeout = 0
Result = cm.BeginExecuteNonQuery()
Loop round waiting for the transaction to complete
Store the time now.<br/>
PrevTime = Now
Do Until Result.IsCompleted
ElapsedTime = System.DateTime.FromOADate(Now.ToOADate - PrevTime.ToOADate)<br/>
F0.Readout.Text = "Processing ""proc_Name_being_Called"" - Please Wait ... " & vbCRLF & "Time Elapsed=" & Format(ElapsedTime, "hh:mm:ss")
Application.DoEvents() Let system in for a click
Loop
Would like to do something like: <br/>
if Result.IsInError then <br/>
Return False <br/>
Else <br/>
Return True <br/>
End If
Catch ex as Exception
...Error trap code here
Finally
If Not IsNothing(cm) Then<br/>
cm.Dispose()<br/>
Conn.Close()<br/>
Conn = Nothing<br/>
End If
End Try
As you can see in the bold block just outside the Loop, I would like to interrogate the return to see if an error has occurred because I am finding the Result.IsCompleted occurs and I can then move on to the next call but I have found that an error may have
occurred in the SP and I am not aware of it.
Does anyone know how I can get an error that has occurred whilst calling the SP to bubble back to the IASyncResult object or some other method that allows me to see that an error has occurred when the Result is completed.
Any help appreciated,
Siv
<
Graham Sivill - Martley, Worcester. UK<br/>
View the full article
I have an application where I make calls to multiple SQL Server Stored Procedures, some of which take an appreciable amount of time to complete, so to keep the user aware that the program is still running and how long the specific SP has been running so
that they can abort it if its taking too long.
My initial attempt was to wrap up each call to the SPs like this example:
Dim cm As SqlCommand = Nothing<br/>
Dim Conn As SqlConnection = Nothing<br/>
Dim Tran As SqlTransaction = Nothing<br/>
Dim PrevTime As Date = Nothing<br/>
Dim ElapsedTime As Date = Nothing<br/>
Dim strSQL As String = ""<br/>
Dim Result As IAsyncResult = Nothing
Try
Conn = New SqlConnection(strCnn)<br/>
Conn.Open()
cm = New SqlCommand("proc_Name_Being_Called", Conn)<br/>
cm.CommandType = CommandType.StoredProcedure<br/>
cm.CommandTimeout = 0
Result = cm.BeginExecuteNonQuery()
Loop round waiting for the transaction to complete
Store the time now.<br/>
PrevTime = Now
Do Until Result.IsCompleted
ElapsedTime = System.DateTime.FromOADate(Now.ToOADate - PrevTime.ToOADate)<br/>
F0.Readout.Text = "Processing ""proc_Name_being_Called"" - Please Wait ... " & vbCRLF & "Time Elapsed=" & Format(ElapsedTime, "hh:mm:ss")
Application.DoEvents() Let system in for a click
Loop
Would like to do something like: <br/>
if Result.IsInError then <br/>
Return False <br/>
Else <br/>
Return True <br/>
End If
Catch ex as Exception
...Error trap code here
Finally
If Not IsNothing(cm) Then<br/>
cm.Dispose()<br/>
Conn.Close()<br/>
Conn = Nothing<br/>
End If
End Try
As you can see in the bold block just outside the Loop, I would like to interrogate the return to see if an error has occurred because I am finding the Result.IsCompleted occurs and I can then move on to the next call but I have found that an error may have
occurred in the SP and I am not aware of it.
Does anyone know how I can get an error that has occurred whilst calling the SP to bubble back to the IASyncResult object or some other method that allows me to see that an error has occurred when the Result is completed.
Any help appreciated,
Siv
<
Graham Sivill - Martley, Worcester. UK<br/>
View the full article