VBA/SQL Server: How to return a succeed/error message back from db to vba excel

Question

I'm using stored procedures in my excel vba code to pass data to the sql server and send a return message back to the vba code. Well, I read a lot in other question topics on this and other sites, but I couldn't find the best solution for this approach. To give you a better imagination, I display an example:

VBA Code:

// defined vba function to return message from database to gui
Public Function fGetMessage() As String
    ' Declare variable and store the data id in the .Tag of userform
    Dim lngVarId As Long
    lngVarId = UserForm1.tbVar.Tag
    
    ' Instance of ADODB.Connection
    Dim oDatabase As clsDatabase
    Set oDatabase = New clsDatabase
    
    ' send sql statement with sproc to sql server and get a return message from database
    Dim oCmd As ADODB.Command
    Set oCmd = New ADODB.Command
    
    With oCmd
        .ActiveConnection = oDatabase.fGetDbConnection()
        .CommandType = adCmdStoredProc
        .CommandText = "dbo.spDeleteProtocolData"
        .Parameters(1) = lngVarId
        
        Dim oRs As ADODB.Recordset
        Set oRs = New ADODB.Recordset
        Set oRs = oCmd.Execute
    End With
    
    fGetDeleteMessage = oCmd.Fields("ReturnMessage") // here I want the string return message
End Function

SQL-Server:

// defined stored procedure in sql server
CREATE PROCEDURE dbo.spDeleteProtocolData
   @VarId AS INT
AS
BEGIN
   SET NOCOUNT ON;

   BEGIN TRY
    BEGIN TRANSACTION
        UPDATE dbo.vProtocolData
        SET StatusId = 0
        WHERE VarId = @VarId
    COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
    -- store error message in log table
    INSERT INTO dbo.Logs (ErrNumber, ErrLine, ErrState, ErrSeverity, ErrSProc, ErrMessage, CreatedBy, CreatedAt)
    SELECT ERROR_NUMBER(), ERROR_LINE(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_PROCEDURE(), ERROR_MESSAGE(), CURRENT_USER, GETDATE()

    ROLLBACK TRANSACTION
   END CATCH
END
GO

Does anyone have an idea or a better solution to handle return messages between vba excel and sql server database?

What string are you wanting to "return" here exactly? I don't see the SQL SELECTing any data to return to the application, so where is the string coming from? — Thom A
– Thom A ♦, Commented Jun 10, 2021 at 12:26
@Larnu I had the idea to take the "return value" of the sql server stored procedure. When you execute a stored procedure for example in ssms you see a result value called "return value". Maybe I could use this column "return value" to handle messages from the database. Is it a good approach or do you have a better solution? — yuro
– yuro, Commented Jun 10, 2021 at 12:32
"When you execute a stored procedure for example in ssms you see a result value called "return value"" No you don't. Are you, however, talking about the RETURN value, which denotes the success of the procedure? 0 for success, anything else for failure? — Thom A
– Thom A ♦, Commented Jun 10, 2021 at 12:37
RETURN isn't a string, it's an int, so referencing to it as a "string" in your code is quite confusing. Does this answer your question? Assign a stored procedure return value to a VBA variable — Thom A
– Thom A ♦, Commented Jun 10, 2021 at 12:47

Christian Reinbothe · Accepted Answer · 2021-06-11 19:23:49Z

You can use Output parameters:

Dim B As Boolean
Dim C As ADODB.Command
Dim I As Long
Dim P As ADODB.Parameter
Dim V As Variant

   Set C = New ADODB.Command
   C.ActiveConnection = glob_CONNECT
   C.CommandType = adCmdStoredProc
   C.CommandText = X.SQL_Name
   
   ' bind Inputparameter
   If X.NInParams > 0 Then
      For I = 1 To X.NInParams Step 1
         Set P = C.CreateParameter(X.InParam(I).SQL_Name, _
                                   X.InParam(I).ADODB_Type, _
                                   adParamInput, _
                                   X.InParam(I).SQL_Size, _
                                   X.InParam(I).SQL_Value)
         C.Parameters.Append P
      Next
   End If
   
   ' bind Outputparameter
   If X.NOutParams > 0 Then
      For I = 1 To X.NOutParams Step 1
         Set P = C.CreateParameter(X.OutParam(I).SQL_Name, _
                                   X.OutParam(I).ADODB_Type, _
                                   adParamOutput, _
                                   X.OutParam(I).SQL_Size)
         C.Parameters.Append P
      Next
   End If
   
   ' Execute SP (Errors are possible!)
   On Error GoTo 0
   On Error Resume Next
   C.Execute
   
   ' Error?
   If Err.Number <> 0 Then
      SQL_EXEC = False
      Exit Function
   End If

   ' Fetch Output
   If X.NOutParams > 0 Then
      For I = 1 To X.NOutParams Step 1
         V = VNormExec(C.Parameters(X.OutParam(I).SQL_Name), X.OutParam(I).SQL_Type)
         X.OutParam(I).SQL_Value = V
      Next
   End If

Collectives™ on Stack Overflow

VBA/SQL Server: How to return a succeed/error message back from db to vba excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related