ADODB returning a value from sql sp (1 Viewer)

Guys i need help with returning a single value from my sql stored procedure i want to call the sp with vba and return a single value all the code is below any help would be great.

'Access ADODB CODE

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset


Dim SQLServer As String
Dim Catalog As String
Dim SQLString As String
Dim SQLInvNo As String
Dim PURCHASEORDERNO As Integer

PURCHASEORDERNO = Me.PURCHASEORDERNO


SQLServer = "Gretzky\RNYSQLAccess"
Catalog = "SQLcafe2010"
SQLString = "Exec sp_Suppliername " & PURCHASEORDERNO




'Create a new ADO Connection object
Set cn = New ADODB.Connection

'Use the Access 10 and SQL Server OLEDB providers to
'open the Connection
'You will need to replace MySQLServer with the name
'of a valid SQL Server
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = SQLServer
.Properties("Integrated Security").Value = "SSPI"
.Properties("Initial Catalog").Value = Catalog
.Open
End With

'Create an instance of the ADO Recordset class, and
'set its properties


Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = SQLString
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open

End With


__________________________
SQL SP

USE [SQLCAFE2010]
GO
/****** Object: StoredProcedure [dbo].[sp_Deltacurrentid] Script Date: 05/07/2013 07:49:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[sp_Suppliername]
@PoNumber int


As
Begin
declare @SupplierName as nvarchar (100)

set @suppliername = (SELECT Suppliers.[SUPPLIER NAME]
FROM PurchaseOrders INNER JOIN
Suppliers ON PurchaseOrders.SUPPLIERID = Suppliers.SUPPLIERID
WHERE (PurchaseOrders.[PURCHASE ORDER NO] = @PoNumber)
)

return @PoNumber
end

Greetings mendesj1, is there some problem with what you are trying to do? I did not spot a specific question.

For MySQL examples, check out the posts by forum member: JohnPapa

Also I have several examples posted, presently all related to SQL Server 2008 R2 however.

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

all i want to do is set the return value from sql "return @PoNumber" to a variable in vba the result of the query is a single value

1) Set up the calling of the SP in an ADO.Command / ADO.Parameters object
2) Fire the .Execute method of the Command object
3) That returns an ADO.Recordset object with the output variables from the SP.
4) Take that ADO.Recordset object / fetch value(s) from it, close it, cleanup

Here is an example of how to use ADO.Command / ADO.Parameters objects to issue a SQL SELECT. Combine it with the example of using an ADO.Command / ADO.Parameter object to execute a Stored Procedure contained in my prior shared link:

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

thanks for the help but i am still not getting it to work, i can tell that the single value is getting returned to my record set from my stored procedure but have a problem setting it equal to a variable in vba

mendesj1 said:

but have a problem setting it equal to a variable in vba

Click to expand...

Show me that bit of VBA code.

Your SP syntax looks a bit different than I have success with. I either SELECT things and not redirect them to variables in the Stored Procedure, or I use variables and at the end of the Stored Procedure, I specifically SELECT the variables into virtual column names so that the results will be successfully received in VBA. Example of that:

So in this SP there are a number of different queries each performing the calculation needed for one variable. At the end of the SP then I SELECT all of the variables back into virtual field names so that I know which data to expect in which field back in the VBA code ADO.Recordset object. Better?

the sp just runs a simple query set the result of the query to a variable (single result value) the returns the value, it gets down to .open in the vba code then i get a run time erro conversion failed when converting the nvarchar value (the result of the querry a -text-) to data type int , i am not sure why its trying to make it an int

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset


Dim SQLServer As String
Dim Catalog As String
Dim SQLString As String
Dim SQLInvNo As String
Dim PURCHASEORDERNO As Integer

PURCHASEORDERNO = Me.PURCHASEORDERNO


SQLServer = "Gretzky\RNYSQLAccess"
Catalog = "SQLcafe2010"
SQLString = "Exec sp_Suppliername " & PURCHASEORDERNO




'Create a new ADO Connection object
Set cn = New ADODB.Connection

'Use the Access 10 and SQL Server OLEDB providers to
'open the Connection
'You will need to replace MySQLServer with the name
'of a valid SQL Server
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = SQLServer
.Properties("Integrated Security").Value = "SSPI"
.Properties("Initial Catalog").Value = Catalog
.Open
End With

'Create an instance of the ADO Recordset class, and
'set its properties


Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = SQLString
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open

End With

mendesj1 said:

Code:Copy to clipboard

With cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "SQLOLEDB"
      .Properties("Data Source").Value = SQLServer
      .Properties("Integrated Security").Value = "SSPI"
      .Properties("Initial Catalog").Value = Catalog
      [B][COLOR=Red].Open[/COLOR][/B]
   End With

Click to expand...

This will not do what you are seeking.

From my example: