Need help returning values from database in vb code

Question

Post reply

Need help returning values from database in vb code

eli-216280

SSC Enthusiast

Points: 190
More actions
April 14, 2005 at 9:04 am

#67187

Hi,
I have a query (or a stored procedure) that I want to return an integer value from sql server into my vba code in access.
How do I go about executing the procedure (or the query) and then getting that integer value from it so that I can use it within my vba code?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

on the server

:

CREATE PROCEDURE [dbo].[SPN_DemoReturn] @SomeParam as int, @SomeOutput as int output

AS

SET NOCOUNT ON

--do usefull

SET @SomeOutput = 14

RETURN 1

SET NOCOUNT OFF

GO

in vb

Private Function exec_SPN_DemoReturn(ByVal SomeParam As Integer, ByRef SomeOutput As Integer, Optional ByRef ReturnValue As Integer) As Integer

On Error GoTo Gestion

Dim MyCmd As ADODB.Command

Set MyCmd = New ADODB.Command

MyCmd.CommandText = "dbo.SPN_DemoReturn"

MyCmd.CommandType = adCmdStoredProc

Dim MyParam As ADODB.Parameter

Set MyParam = New ADODB.Parameter

MyParam.Direction = adParamReturnValue

MyParam.Name = "@Return"

MyParam.Type = adInteger

MyCmd.Parameters.Append MyParam

Set MyParam = New ADODB.Parameter

MyParam.Name = "@SomeParam"

MyParam.Value = SomeParam

MyParam.Size = 4

MyParam.Direction = adParamInput

MyParam.Type = adInteger

MyCmd.Parameters.Append MyParam

Set MyParam = New ADODB.Parameter

MyParam.Name = "@SomeOutput"

MyParam.Value = SomeOutput

MyParam.Size = 4

MyParam.Direction = adParamInputOutput

MyParam.Type = adInteger

MyCmd.Parameters.Append MyParam

MyCn.Open

MyCmd.ActiveConnection = MyCn

MyCmd.Execute exec_SPN_DemoReturn

MyCn.Close

ReturnValue = CInt(MyCmd.Parameters("@Return").Value)

SomeOutput = MyCmd.Parameters("@SomeOutput").Value

Set MyParam = Nothing

Set MyCmd = Nothing

Exit Function

Gestion:

ErrHandler ModuleName, Me.Name, "exec_SPN_DemoReturn", Err

MsgBox Err.Description & " : " & Err.Number

End Function

eli-216280 SSC Enthusiast Points: 190 More actions · Answer 2

when it gets to MyCn.Open it tells me object required.

what do i do?

thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Sorry, I usually have a global connection called MyCn, which I simply open and close when I need to connect to the database.