Adlerm
asked on
Getting a Returned Variable from SQL 2008 into MS Access 2008 ADP Project
Hi
I'm developing a MS Access 2008/SQL 2008 ADP Project.
I'm attempting to return a string variable (nvarchar (30) from the Stored Procedure (Server Side) to the MS Access (client side) but not having any luck.
Executing the Stored Procedure from the client side is working correctly but I can't figure out how to return the SP string variable to the client side VB code and in turn using this returned variable with in the VB Code.
I understand that I have to declare an output variable in the SP as @VariableName Output but I don't know how to return this value to my client side VB calling Execute Stored Procedure command or how to set up a variable in the VB client side code that can accept the Returned variable.
If any one has an example of the code used on the VB client side Calling Procedure command and Code to set up the Return variable on the SP server side I would be most greatful.
I'm developing a MS Access 2008/SQL 2008 ADP Project.
I'm attempting to return a string variable (nvarchar (30) from the Stored Procedure (Server Side) to the MS Access (client side) but not having any luck.
Executing the Stored Procedure from the client side is working correctly but I can't figure out how to return the SP string variable to the client side VB code and in turn using this returned variable with in the VB Code.
I understand that I have to declare an output variable in the SP as @VariableName Output but I don't know how to return this value to my client side VB calling Execute Stored Procedure command or how to set up a variable in the VB client side code that can accept the Returned variable.
If any one has an example of the code used on the VB client side Calling Procedure command and Code to set up the Return variable on the SP server side I would be most greatful.
ASKER
Hi angelIII
Many thanks for you reply.
I think I need to clear up a few points - Yes I understand the difference between Return value and Output parameter. I am using Return/Output interchangerable when in fact I should be using Output in this situation.
Unfortunately all the examples shown (via the supplied hyper links and others I've researched) assume you are in the Server side of things NOT on the client side. For example you don't Declare variables in Access you Dim variables.
As I said I don't have to worry about setting up connections strings etc from the client side as this has all this been done and I have no worries about executing the stored procedure from my client side MS Access 2008 ADP database.
I have attached the code I use to execute the stored procedure (see below) but my concern is how to construct the code to accept a Output parameter from the stored procedure and then to use that output parameter within my client side MS Access VB code.
**************************
Private Sub JobCuttingTemplate_AfterUp
Dim wsx As Boolean
Dim sPassedSPReferenceId As String
sPassedSPReferenceId = "Exec sp9991_upd_tblJobsSelected
& " " & Me!JobReferenceId '<== Comes from the current form
wsx = ADO_ExecCommand(sPassedSPR
'This is where I want to utilise the Output parameter but don't know how
End Sub
Many thanks for you reply.
I think I need to clear up a few points - Yes I understand the difference between Return value and Output parameter. I am using Return/Output interchangerable when in fact I should be using Output in this situation.
Unfortunately all the examples shown (via the supplied hyper links and others I've researched) assume you are in the Server side of things NOT on the client side. For example you don't Declare variables in Access you Dim variables.
As I said I don't have to worry about setting up connections strings etc from the client side as this has all this been done and I have no worries about executing the stored procedure from my client side MS Access 2008 ADP database.
I have attached the code I use to execute the stored procedure (see below) but my concern is how to construct the code to accept a Output parameter from the stored procedure and then to use that output parameter within my client side MS Access VB code.
**************************
Private Sub JobCuttingTemplate_AfterUp
Dim wsx As Boolean
Dim sPassedSPReferenceId As String
sPassedSPReferenceId = "Exec sp9991_upd_tblJobsSelected
& " " & Me!JobReferenceId '<== Comes from the current form
wsx = ADO_ExecCommand(sPassedSPR
'This is where I want to utilise the Output parameter but don't know how
End Sub
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I don't know what you are on about. The above command works and works well. It is also working in a number of other ADP applications that I've developed. I'm simply trying to establish the correct syntax to use an Output variable generated - passed back - by a stored procedure. I've haven't had the need to to utilise a stored procedure's Output variable before so I'm unsure the of the syntax structure I should use, Everthing I try fails thus my request for help -
sorry, but that function is unkown to me, so I cannot tell if/why it doesn't work for you.
and yes, the function shall work for your current other usages, but not for output parameters, so visibly you cannot use the function as is.
the code in the link I gave (http://www.freevbcode.com/ShowCode.asp?ID=3687) is perfectly showing how to do in general. I could suggest a modification of ADO_ExecCommand if I had the source code of that one ...
and yes, the function shall work for your current other usages, but not for output parameters, so visibly you cannot use the function as is.
the code in the link I gave (http://www.freevbcode.com/ShowCode.asp?ID=3687) is perfectly showing how to do in general. I could suggest a modification of ADO_ExecCommand if I had the source code of that one ...
ASKER
Hi AngleIII
I'm awarding you the points as you at least attempted to address my problem. In the end I found a work around to my problem that didn't require an Output parameter by the calling command. This doesn't mean that the Output parameter method was wrong - it only means that I couldn't get it to work in this instance so it gives me something to work for just in case I need to use an Output parameter in a future Access/SQL ADP project.
Again - thanks for you efforts.
I'm awarding you the points as you at least attempted to address my problem. In the end I found a work around to my problem that didn't require an Output parameter by the calling command. This doesn't mean that the Output parameter method was wrong - it only means that I couldn't get it to work in this instance so it gives me something to work for just in case I need to use an Output parameter in a future Access/SQL ADP project.
Again - thanks for you efforts.
http://sqlserverpedia.com/wiki/Stored_Procedures_-_Output_Parameters_%26_Return_Values
next, see this tutorial on how to make this work with ADODB command object, for example:
http://www.freevbcode.com/ShowCode.asp?ID=3687
the key items there are that you indeed define the parameter with the "direction" of output, and after the .execute, you can grab the parameters value