ADO Newbie; Return output parameters from Stored Procedure

ADO Newbie; Return output parameters from Stored Procedure

Post by CJ » Thu, 14 Jun 2001 08:07:16



Any help would be appreciated. I am working in an A2K/SQL 7 project (.adp)
environment. I'm trying to call a stored procedure from Access (either from
a form or function?), return  an output parameter value, and set a control
on my form to that parameter. Seems easy but I'm bran new to ADO. I'm fairly
confident that my stored procedure is working correctly but when I set a
control in a form to =RunSP1() I get "#NAME?".
Thanks... CJ

Function RunSP1()
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "EXEC SP1"
.CommandType = adCmdText
.Execute
End With
End Function

use pubs
go

Alter procedure sp1
as
 select * from authors

go




.

 
 
 

ADO Newbie; Return output parameters from Stored Procedure

Post by Stephen How » Fri, 15 Jun 2001 03:27:53



> Any help would be appreciated. I am working in an A2K/SQL 7 project (.adp)
> environment. I'm trying to call a stored procedure from Access (either
from
> a form or function?), return  an output parameter value, and set a control
> on my form to that parameter. Seems easy but I'm bran new to ADO. I'm
fairly
> confident that my stored procedure is working correctly but when I set a
> control in a form to =RunSP1() I get "#NAME?".
> Thanks... CJ

> Function RunSP1()
> Set cmd = New ADODB.Command
> With cmd
> .ActiveConnection = CurrentProject.Connection
> .CommandText = "EXEC SP1"
> .CommandType = adCmdText

Change to

.CommandText = "SP1"
.CommandType = adCmdStoredProc

Does it work now?

Stephen Howe

 
 
 

ADO Newbie; Return output parameters from Stored Procedure

Post by CJ » Sun, 17 Jun 2001 06:41:46


Thanks Steve, your suggestion set me on the right path. I'm still having
problems returning the parameter but I will continue to try and make this
work and post again if necessary.
CJ


> > Any help would be appreciated. I am working in an A2K/SQL 7 project
(.adp)
> > environment. I'm trying to call a stored procedure from Access (either
> from
> > a form or function?), return  an output parameter value, and set a
control
> > on my form to that parameter. Seems easy but I'm bran new to ADO. I'm
> fairly
> > confident that my stored procedure is working correctly but when I set a
> > control in a form to =RunSP1() I get "#NAME?".
> > Thanks... CJ

> > Function RunSP1()
> > Set cmd = New ADODB.Command
> > With cmd
> > .ActiveConnection = CurrentProject.Connection
> > .CommandText = "EXEC SP1"
> > .CommandType = adCmdText

> Change to

> .CommandText = "SP1"
> .CommandType = adCmdStoredProc

> Does it work now?

> Stephen Howe

 
 
 

ADO Newbie; Return output parameters from Stored Procedure

Post by CJ » Wed, 20 Jun 2001 02:04:26


Here's how I got this all to work if anyone is interested. The sp and
function are down below. A few points.
-I added a field to the authors table, ID, int
-You can not use the default project connection, "ActiveConnection =
CurrentProject.Connection", you must explicitly set the connection

Perhaps this will help someone,
CJ


as
set nocount ON

If (select  count(*) from authors

return 0
Else
return 1

Function CountRecordsFunction(variable1 As Long)
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim Prm As ADODB.Parameter
Dim countvariable As Long

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command

cnn.Open "Provider =SQLOLEDB; Data Source = DATASERVER;" &
"Database=Pubs;Trusted_COnnection=yes;"

Set cmd.ActiveConnection = cnn
cmd.CommandText = "AuthorsIDCount"
cmd.CommandType = adCmdStoredProc

Set Prm = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
cmd.Parameters.Append Prm

Set Prm = cmd.CreateParameter("id", adInteger, adParamInput, , variable1)
cmd.Parameters.Append Prm

cmd.Execute

If cmd.Parameters("RETURN") = 0 Then
MsgBox "test1"
Else
MsgBox "test2"
End If
Set Prm = Nothing
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
End Function


> Thanks Steve, your suggestion set me on the right path. I'm still having
> problems returning the parameter but I will continue to try and make this
> work and post again if necessary.
> CJ


 
 
 

1. Stored procedure returning empty output parameters or return value

I have a stored procedure that when passed a username I want to call
sp_droplogin. If this call fails (user or aliased in a database)
return error value -1. If successful return 1.

I can get it to work using sql oledb whether it fails or not, but not
using a ODBC DSN. If it is successful I get 1 returned if it fails I
get nothing at all. I have to use the DSN approach so I need to figure
out what's going on.

I've tried using both a output parameter and a return value with the
same results.

I'm developing in VB 6, SQL 7, MDAC 2.6

Also, even when I set a default value for the output parameter/return
value before I call sp_droplogin, it gets wiped out and nothing is
returned.

Any help would be appreciated.

Steve

2. Database and SQL

3. How to capture the return value and output parameter value of a stored procedure in VB

4. float datatypes losing decimals

5. Stored procedure not returning output parameter

6. SQL Server Left Joins in Access Front End

7. Returning output parameter from Stored Procedure

8. Database Administrator

9. Return PL/SQL Table as output parameter in Oracle Stored Procedure

10. JDBC: return values and output parameters from stored procedures

11. Help! Stored Procedure Return/Output Parameters

12. Returning both recordset and output parameters into VB from stored procedure

13. Getting OUTPUT parameters and return values from Stored Procedures