[AccessD] Excecuting SP in SQL db using ADO

Hi Borge:

Here is a simple piece of code I use to return a single value through ADO.
In the case below I have executed a Stored Procedure named
'RECompanyExists', passed an integer variable 'lngCompanyCode' to the SP
variable  "@intCompanyCode" and identified a SP integer variable
"@intRecords" from which to retrieve the results, through the ADO command
object, 'objCmd' array and passed them back to the function's 'CheckDelete'
caller. 

If you were passing a number of variables back you would need one parameter
for each command object array position. Example:
.Parameters.Append .CreateParameter("@intRecord1", adInteger, adParamOutput)
.Parameters.Append .CreateParameter("@intRecord2", adInteger, adParamOutput)
.Parameters.Append .CreateParameter("@intRecord3", adInteger, adParamOutput)

...and retreive like:
intValue1 = objCmd(1)
intValue2 = objCmd(2)
intValue3 = objCmd(3)


'-----------------------------------------------------------
Public Function CheckDelete(lngCompanyCode As Long) As Integer
    
On Error GoTo Err_CheckDelete
    
CheckDelete = 0
    
Dim objCmd As New ADODB.Command
    
With objCmd
 .ActiveConnection = gstrConnection
 .CommandText = "RECompanyExists"
 .CommandType = adCmdStoredProc
 .Parameters.Append .CreateParameter("@intCompanyCode", adInteger,
adParamInput, , lngCompanyCode)
 .Parameters.Append .CreateParameter("@intRecords", adInteger,
adParamOutput)
 .Execute
End With

' Retreive the value here through the command object array.
CheckDelete = objCmd(1)
    
Exit_CheckDelete:
 On Error Resume Next
 Set objCmd = Nothing
 Exit Function
    
Err_CheckDelete:
 ShowErrMsg ("DataConnection Module: CheckDelete Function")
 Resume Exit_CheckDelete
    
End Function
'----------------------------------------------------------

Here is what the SP looks like:

//----------------------------------------------------------
CREATE PROC dbo.RECompanyExists
	
 @intCompanyCode INT,
 @intRecords INT OUTPUT

AS

 select @intRecords = 0

 select @intRecords = (select count(*) from EmployeeWorkInformation where
EmployeeWorkInformation.MgmtComp_ID = @intCompanyCode)

 select @intRecords = @intRecords + (select count(*) from Cards where
Cards.MgmtComp_ID = @intCompanyCode)

GO
//------------------------------------------------------------

HTH
Jim

 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Borge Hansen
Sent: Tuesday, August 12, 2008 8:06 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Excecuting SP in SQL db using ADO

Hi,

I haven't been following the conversations  / activity here lately ... and
now opening up the gmail account I've created just for the Accessd
discussion group I see all the familiar names ... I have been hanging out on
this discussion group for a number of years - contributed bery little - but
learned a lot .... so let me just take this random moment to thank each and
everyone on this list ...

No on to today's puzzle: I want to return back into my VBA code the return
value that I can somehow control is coming out of a SQL 2005 Stored
Procedure .... The SP is non row returning:  it checks if a record exists
and inserts one if the record does not exist. The return value of the SP is
normally 0 - I've set my own return values based on the outcome of the
record checking....

Now, how can I pick up the return value in vba ??

More specific is there a built in ADO 'something' that will return the
return code??
Something I can call on the line (see below for full function)

    cn.Execute strSP, lngRecsAffected, adExecuteNoRecords
In the code below you see I am using the lngRecsAffected to signal success
in executing the SP as it will always return -1 .
If there is a timeout or other exception, the SP will not commit and I
return to the calling code that the SP did not commit and  what the SP / SQL
string was ...

Rather than picking up the lngRecsAffected I want to pick up the returncode
from the SP.

Can I do that using ADO in an immediate sense??

I've been looking at the CADOConnSQLServer class module from Total Visual
SourceBook 2002 - is that the direction to go?

If so does any one have a example to start from?

... sigh .... I really should be jumping to .net - don't even mention it!

Regards
borge


Public Function fncExecuteSP(ByVal strSP As String) As String
On Error GoTo EH:
'!--------------------------------------------------------------------------
'!Purpose      : Generic code for executing an SP or sql string in the SQL
Db backend
                 'use for non row returning SPs and sql strings
'!--------------------------------------------------------------------------
'NOTE: The calling code is responsible for providing a fully parameterized
'pass through query or the name of an SP with all parameters set

    ' Set connection string
    ' Assuming use of static functions for setting connection parameter
values
    ' for use throughout the application
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim lngRecsAffected As Long
    lngRecsAffected = 0

    Set cn = New ADODB.Connection
    With cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "SQLOLEDB"
      .Properties("Data Source").Value = CurrentSQLOLEDB_DataSource
'Current... etc are static functions
      .Properties("User ID").Value = CurrentODBC_UID
      .Properties("Password").Value = CurrentODBC_PWD
      .Properties("Initial Catalog").Value = CurrentSQLOLEDB_InitialCatalog
      .Open
    End With

  On Error GoTo ETransaction
    cn.BeginTrans
    cn.Execute strSP, lngRecsAffected, adExecuteNoRecords
    cn.CommitTrans
    fncExecuteSP = lngRecsAffected

EDone:
    On Error Resume Next
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
EX:
    DoCmd.SetWarnings True
    Exit Function
ETransaction:
        cn.RollbackTrans
        fncExecuteSP = "RMDS: Stored Procedure transaction was rolled back!
Attempted to execute:[" & strSP & "]" & vbCrLf
        Resume EDone
EH:
      fncExecuteSP = "RMDS: " & fncExecuteSP & " Error in fncExecuteSP " &
": " & Err.Number & " " & Err.Description & vbCrLf & "Error occurred while
trying to execute: [" & strSP & "]." & vbCrLf
  Resume EDone
End Function
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com