SQL Stored Procedure From Excel

Hi,

I have a stored Procedure that updates only 1 row (No changes in the count of rows).

I am using the below code to execute the Stored Procedure.

Sub ExecuteStoredProcUpdate_Yield()

    Dim SQLConn As ADODB.Connection
    Dim SQLCommand As ADODB.Command
    Dim SourceQuery As String

    Set SQLConn = New ADODB.Connection
    Set SQLCommand = New ADODB.Command
    
    SQLConn.ConnectionString = ConnString
    
    SourceQuery = "EXECUTE DB_Common..Update_Yield"
         
    On Error GoTo CloseConnection
    
    SQLConn.Open
    
    With SQLCommand
        .ActiveConnection = SQLConn
        .CommandType = adCmdStoredProc
        .CommandText = SourceQuery
    End With
                
    On Error GoTo 0
    SQLConn.Close
    
    Exit Sub
    
CloseConnection:
    MsgBox "SQL Stored Procedure Did Not Execute Sucessfully!", vbOKOnly, "SQL Error"
    SQLConn.Close

End Sub

Is there a way I come to know If the Stored Proc Executed properly?

Is there a way of knowing if it is running as desired at all?

Originally Posted by Kyle123

What does the sproc return? What database are you using?

Hi Kyle,

The Stor Proc does not return anything.. All it does is update the Value of a Row in the Table to Zero..

You can pass a variable to either of Command.Execute method Or Connection.Execute methods as an argument Recordsaffected. After execution t will hold the number of affected records.
You can also use option adExecuteNoRecords to tell the provider not to construct Recordset Object if it is not a row-returning query

Originally Posted by buran

You can pass a variable to either of Command.Execute method Or Connection.Execute methods as an argument Recordsaffected. After execution t will hold the number of affected records.
You can also use option adExecuteNoRecords to tell the provider not to construct Recordset Object if it is not a row-returning query

Hi Buran,

Actually I am learning ADO hence I am not understanding very clearly what exactly is to be done..

Can you please clarify..

First of all, I don't see the Execute method of SQLCommand, so I'm not sure how do you actually use this code to run the stored proc.
Second - I'm not able to test, so below code may have errors

Sub ExecuteStoredProcUpdate_Yield()

    Dim SQLConn As ADODB.Connection
    Dim SQLCommand As ADODB.Command
    Dim SourceQuery As String
    Dim lngRecordsAffected As Long 'This will be the variable to hold the number of affected records

    Set SQLConn = New ADODB.Connection
    Set SQLCommand = New ADODB.Command
    
    SQLConn.ConnectionString = ConnString
    
    SourceQuery = "EXECUTE DB_Common..Update_Yield"
         
    On Error GoTo CloseConnection
    
    SQLConn.Open
    
    With SQLCommand
        .ActiveConnection = SQLConn
        .CommandType = adCmdStoredProc
        .CommandText = SourceQuery
        
        ' Next it should execute the command.
        ' Note that adExecuteNoRecords prevents creating Recordset object
        ' It's possible to use it, because you said you procedure does not return anything
        
        .Execute Recordsaffected:=lngRecordsAffected, Options:=adExecuteNoRecords
    End With
    
    'Stored procedure executed without rising an error. Display msg how many records were affected
    MsgBox "Execution of SQL Stored procedure affected " & lngRecordsAffected & " records."
                
    On Error GoTo 0
    SQLConn.Close
    
    Exit Sub
    
CloseConnection:
    MsgBox "SQL Stored Procedure Did Not Execute Sucessfully!", vbOKOnly, "SQL Error"
    SQLConn.Close

End Sub

Originally Posted by buran

First of all, I don't see the Execute method of SQLCommand, so I'm not sure how do you actually use this code to run the stored proc.

Actually I tried using the Execute method, which gave a run time error when I ran the code..

Let me see how this code works.. I will get back in some time..

Cheers!

I tried it, but It returns error on the Execute method..

Are you asking the DB Name? If yes, it is DB_Common

What is your DB?

No, I mean MS SQL, MySQL, Oracle, etc...
also what is the exact error msg?

Its SQL.

The error screenshot -

Originally Posted by NeedForExcel

Its SQL.

SQL is the language, it comes in different flavours and implementations. The underlying database could be MS SQL Server, MySQL, Oracle, Ms Access, sqlite PostgreSQL, to name a few.
I'm not sure about the double dots in your SQL statement. I, as well as Kyle123, am asking about that.
For example in MS SQL it's enough to supply it's stored procedure name to command object CommandText property. In this case you should use

.CommandType = adCmdStoredProc

However in your code you use SQL string, so I think

.CommandType = adCmdText

That is something, together with the 2 dots in the sql string, that I didn't notice

Hi Buran,

Its MS SQL (SSMS)...

And, Double dots were new to me too, but that is how it is used in the place where I work..

And yes, If I use .CommandType = adCmdText, do I still need the Execute method?

Originally Posted by NeedForExcel

Hi Buran,

And yes, If I use .CommandType = adCmdText, do I still need the Execute method?

yes, you should use Execute

I'm pretty sure that MSSQL sprocs always return an int. If nothing is specified or null is returned it's 0, your sproc should really have a return value to indicate the outcome - so you need to change that.

Then it's simply:

    SQLConn.Open
    
    Dim returnValue As ADODB.Parameter
    
    
    
    With SQLCommand
        .ActiveConnection = SQLConn
        .CommandType = adCmdStoredProc
        .CommandText = SourceQuery
        
        'Create an output param for the return value
        Set returnValue = .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue)
        'Add the output param
        .Parameters.Append returnValue
        .Execute
    End With
    
    If returnValue = 0 Then MsgBox "Something went wrong", vbCritical

Your sproc should then have a line:

	RETURN @@rowcount

That returns the number of rows affected

Thank you Buran & Kyle as always..

Guess I'll need a guy with some SQL Experience to help me create that output parameter.

Marking the thread solved as of now, but will get back in case I need further assistance..

So did it work?

Originally Posted by buran

So did it work?

Not as of yet.. Trying real hard..

Originally Posted by Kyle123

It's only that one line, will look something like:

EDIt had to attach as it's SQL (firewall doesn't like it)

Not sure If I am allowed to do that.. Let me check it out..

By the way, can you guide me as to where I can get some reading material on ADODB?

Alright!

Thank You for the help

Hi Kyle & Buran,

I managed to add to the Stored Proc as required..

ALTER PROCEDURE [dbo].[Update_Yield]
	-- Add the parameters for the stored procedure here

@RowCount integer OUTPUT
AS

//Queries//

return

END

And My Final ADO Code is

Sub ExecuteStoredProcUpdate_Yield()

    Dim SQLConn As ADODB.Connection
    Dim SQLCommand As ADODB.Command
    Dim SourceQuery As String
    Dim lngRecordsAffected As Long 'This will be the variable to hold the number of affected records

    Set SQLConn = New ADODB.Connection
    Set SQLCommand = New ADODB.Command
    
    SQLConn.ConnectionString = ConnString
    
    SourceQuery = "EXECUTE DB_Common..Update_Yield"
         
    On Error GoTo CloseConnection
    
    SQLConn.Open
    
    Dim returnValue As ADODB.Parameter
    
    
    With SQLCommand
        .ActiveConnection = SQLConn
        .CommandType = adCmdStoredProc
        .CommandText = SourceQuery
        
        'Create an output param for the return value
        Set returnValue = .CreateParameter("@RowCount", adInteger, adParamReturnValue)
        'Add the output param
        .Parameters.Append returnValue
        .Execute
    End With
    
    If returnValue = 0 Then MsgBox "Something went wrong", vbCritical
    On Error GoTo 0
    SQLConn.Close
    
    Exit Sub
    
CloseConnection:
    MsgBox "SQL Stored Procedure Did Not Execute Sucessfully!", vbOKOnly, "SQL Error"
    SQLConn.Close

End Sub

But still not working.. After the .Execute Method, it jumps to the CloseConnection label..

Shouldn't your SourceQuery variable just be the name of the procedure?

SourceQuery = "DB_Common..Update_Yield"