You’re offline. This is a read only version of the page.
We have a call to a stored procedure that was migrated to GP 2013. If we run the stored procedure in GP 2013 in appears to run fine (no errors) but the stored procedure fails to execute properly. If we run the stored procedure within SQL Query Analyzer it runs fine.
Below is the code in VBA that calls the stored procedure. Any ideas?
Thanks
Mark
-----------------------------------------------
Private Sub ServiceCallNotes_AfterUserChanged()
' RUn the stored procedure to transfer SVC notes to SOP
Dim BachNum As String
Dim Success As Integer
Dim ErrorMessage As String
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
If BatchID.Value = "" Then
MsgBox "Please Enter a BatchID"
Else
'MsgBox BatchID.Value
BachNum = BatchID.Value
MsgBox BachNum
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
MsgBox UserInfoGet.CreateADOConnection
MsgBox UserInfoGet.IntercompanyID
Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn
cmd.CommandText = "DECLARE @SUCCESS int; exec @SUCCESS = osp_batch_note_update "" & BachNum & ""; SELECT @SUCCESS as STATUS"
Set rst = cmd.Execute()
Success = rst!Status
If Success = 0 Then
ErrorMessage = "Notes moved without errors"
Else
ErrorMessage = "There has been an error while moving notes: Batch moving will be cancelled."
End If
MsgBox ErrorMessage
End If
End Sub
*This post is locked for comments
I have the same question (0)I think the issue is that VBA and SQL don't mesh together quite so seamlessly. You're declaring a SQL variable to receive a result from the procedure, but VBA is probably not expecting that.
I have a rough draft of code below that might give you an example of what I think might be on the right path. This is for reference - I wrote it in the web form without testing, so no fair putting this in production . :)
----
Private Sub ServiceCallNotes_AfterUserChanged()
Dim BachNum As String
Dim Success As Integer
Dim ErrorMessage As String
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
If BatchID.Value = "" Then
MsgBox "Please Enter a BatchID"
Exit Sub
End if
'MsgBox BatchID.Value
BachNum = BatchID.Value
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
cmd.CommandType = adCmdStoredProc ' DECLARE STORED PROC
Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn
cmd.CommandText = "osp_batch_note_update '" & BachNum & "'"
cmd.Parameters.Refresh ' PREP
Dim rprm
Set rprm = cmd.CreateParameter("@SUCCESS", adVarChar, adParamOutput, 10)
With cmd
.Parameters.Append rprm
End With
cmd.Execute
If rprm.Value = "0" Then
ErrorMessage = "Notes moved without errors"
Else
ErrorMessage = "There has been an error while moving notes: Batch moving will be cancelled."
End if
MsgBox ErrorMessage
' CLOSE CONNECTION
On Error Resume Next
If rst.State = adStateOpen Then rst.Close
If cn.State = adStateOpen Then cn.Close
On Error GoTo 0
End Sub
Share
Report
All responses (
Answers (
270
