Command Parameters Error Handling

[scott]

My below code prompts the user to "update" or "sync" their data with the
live version of the data that resides on the server. It works by executing a
stored procedure called "MySproc". The "MySproc" procedure executes a SQL
2005 SSISpackage that does the work.

My code works fine except my error handling. I have to plead ignorance on
the topic of error handling. I tried to catch the SPROC's return value with
the variable called "errorCode", but it always returns -1. However, the
cmd.Parameters("@ERROR") variable does return an error code. It's zero if
there's no error and returns a 1 if there is an error.

Can someone help me re-write my error handling better? I listed my SPROC in
CODE 2 section below in case it's needed.


CODE: ********************

Public Sub TestDTS()

On Error GoTo error_

Dim cnn As ADODB.Connection, cmd As ADODB.Command, prm As
ADODB.Parameter
Dim errorCode As Variant, vntStatus As Variant

Dim sMsg As String, BoxResponse As String
Dim sTitle As String, iStyle As Integer

sMsg = "Are you absolutely sure you want to update your data?"
iStyle = vbYesNo + vbQuestion
BoxResponse = MsgBox(sMsg, iStyle, sTitle)

If BoxResponse = vbYes Then

'Prepare status bar
vntStatus = SysCmd(acSysCmdSetStatus, "Updating SQL Server Data
....")

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command

cmd.ActiveConnection = cnn
cmd.CommandText = "MySproc"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("@Error", _
adInteger, adParamOutput, , 0)
cmd.Parameters.Append prm

cmd.Execute errorCode

If cmd.Parameters("@ERROR") <> 0 Then

'ReSet the progress meter
Call SysCmd(acSysCmdRemoveMeter)
vntStatus = SysCmd(acSysCmdClearStatus)

Debug.Print "Error Code = " & errorCode

sMsg = "Data transfer from Office" & vbCrLf & _
"SQL Server was not Successful!"
iStyle = vbCritical + vbOKOnly

sTitle = "Data Transfer"
MsgBox sMsg, iStyle, sTitle

Else
'Reset the progress meter
Call SysCmd(acSysCmdRemoveMeter)
vntStatus = SysCmd(acSysCmdClearStatus)

sMsg = "Data transfer from Office" & vbCrLf & vbCrLf & _
"SQL Server was Successful!" & vbCrLf & _
"Your local data is updated."
iStyle = vbInformation + vbOKOnly

sTitle = "Data Transfer"
MsgBox sMsg, iStyle, sTitle
End If

Else
DoCmd.CancelEvent
Exit Sub
End If

exit_:

Exit Sub
error_:

Select Case Err.Number
Case Else

'Reset the progress meter
Call SysCmd(acSysCmdRemoveMeter)
vntStatus = SysCmd(acSysCmdClearStatus)

MsgBox Err.Number & " " & Err.Description

Resume exit_
End Select

End Sub

CODE 2: *********************

CREATE PROCEDURE [dbo].[MySproc]


(
@error bit OUTPUT
)
AS

DECLARE @shell varchar(255)

SET @shell = 'DTEXEC /SQL "\update_Local_Data" /SERVER MYPCNAME /U "user"
/P "password" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW'

exec @error = master..xp_cmdshell @shell
return @error