S
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
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