Error Handling with Sproc

[scott]

Below is a sub that fires on a command button click event. I would like to
be able to determine if the Sproc runs and returns data or if there was an
error such as the sql server it queries isn't available.

How can I add some error handling code to determine if the stored procedure
fires off correctly or if there is an error? Do I have to modify the sproc
to return a variable for success or failure or can it be done with vba
within the sub?

Any help will be appreciated.



Private Sub cmdGetDowntime_Click()

Dim cnn As ADODB.Connection, cmd As ADODB.Command

Dim rs As ADODB.Recordset
Dim sDSN, sDSN2, dtStartDate As Date

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
dtStartDate = Me.prod_date

sDSN = "Provider=sqloledb;" & _
"Data Source=192.168.222.222;" & _
"Initial Catalog=myDatabase;" & _
"User Id=userID;" & _
"Password=mypwd;Network Library=dbmssocn"

cnn.Open sDSN

Set cmd.ActiveConnection = cnn
cmd.CommandText = "mySproc_GetDowntime"
cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh
cmd.Parameters("@dtStartDate") = dtStartDate

rs.Open cmd, , adOpenKeyset, _
adLockOptimistic, adCmdStoredProc

Do Until rs.EOF

If rs(0) = "1" Then
Forms!f_data![f_Data_Sub1]!down_time.Value = rs(1)
ElseIf rs(0) = "2" Then
Forms!f_data![f_Data_Sub2]!down_time.Value = rs(1)
ElseIf rs(0) = "3" Then
Forms!f_data![f_Data_Sub3]!down_time.Value = rs(1)
End If

rs.MoveNext
Loop

rs.close

Set rs = Nothing
Set cmd = Nothing
cnn.close
Set cnn = Nothing

End Sub

 

[Stefan Hoffmann]

hi Scott,

Below is a sub that fires on a command button click event. I would like to
be able to determine if the Sproc runs and returns data or if there was an
error such as the sql server it queries isn't available.

Use a normal error handler:

On Local Error Goto LocalError

Dim i As Long
'your code
Exit Sub

LocalError:
MsgBox Err.Description
If Not cnn Is Nothing Then
For i = 0 to cnn.Errors.Count - 1
MsgBox ..
Next i
End If

Dim sDSN, sDSN2, dtStartDate As Date

Even if it doesn't cause any trouble in your case, it is bad coding
practice to declare variables implicit as Variant, use explicit
declarations:

Dim sDSN As String


mfG
--> stefan <--

 

[scott]

thanks.

hi Scott,

Use a normal error handler:

On Local Error Goto LocalError

Dim i As Long
'your code
Exit Sub

LocalError:
MsgBox Err.Description
If Not cnn Is Nothing Then
For i = 0 to cnn.Errors.Count - 1
MsgBox ..
Next i
End If


Even if it doesn't cause any trouble in your case, it is bad coding
practice to declare variables implicit as Variant, use explicit
declarations:

Dim sDSN As String


mfG
--> stefan <--