ADO SP ReturnValue Params not working and problems with Command.Append 
Author Message
 ADO SP ReturnValue Params not working and problems with Command.Append

Hi,

I'm using VB .Net and ADO 2.7 to exec a stored proc on a SQL Server
database.
Here's the code:

Imports ADODB
...
Dim adocmdSP as New ADODB.Command
Dim adoprmRetVal as New ADODB.Parameter
Dim adoprmName as New ADODB.Parameter
..
adocmdSP.CommandText = "sp_LookupByName"
adocmdSP.CommandType = CommandTypeEnum.adCmdStoredProc
..
adoprmRetVal = adocmdSP.CreateParameter("RETURN_VALUE",
DataTypeEnum.adInteger, ParameterDirectionEnum.adParamReturnValue)

DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 50, "test")

adocmdSP.Parameters.Append(adoprmRetVal)
adocmdSP.Parameters.Append(adoprmName)

adocmd.Execute()

msgbox("Test 1 : SP Return Value = " & adoprmRetVal.Value)
msgbox("Test 2 : SP Return Value = " &
adocmdSP.Parameters("RETURN_NAME").Value)

The SP that I'm calling explicitly returns an integer even if an internal
error condition occurs but adoprmRetval.value is being set to vbNullString ?

As another point, I originally had the following to define the parameters
adoprmRetVal = New ADODB.Parameter()
With adoprmRetVal
 .Direction = ParameterDirectionEnum.adParamReturnValue
 .Name = "RETURN_VALUE"
 .Type = DataTypeEnum.adInteger
End With
adocmdSP.Parameters.Append(adoprmRetVal)

but the last line here threw an InvalidCast exception

I've written the exact same code iv VB6 and it works successfully.  Is there
anything in VB .Net that I should know about when using ADO ?



Wed, 24 Aug 2005 19:55:45 GMT  
 ADO SP ReturnValue Params not working and problems with Command.Append
John,

Did you have any luck ragarding the return parameters?

I'm having exactly the same problem, when I try to
ge the return parameter it says its set to Nothing :-(

Chris.



Sat, 27 Aug 2005 17:14:37 GMT  
 ADO SP ReturnValue Params not working and problems with Command.Append
Hello John,
Can you please post the script for your stored procedure, I will try to
recreate the problem on my side and will update you.

Thanks

Ramesh Thyagarajan, MCSD,MCDBA
Microsoft Developer support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.



Sun, 28 Aug 2005 01:41:26 GMT  
 ADO SP ReturnValue Params not working and problems with Command.Append
Sorry for the delay guys, here you go

CREATE PROCEDURE dbo.ListAllApplications


AS


IF NOT EXISTS (SELECT * FROM dbo.Application (NOLOCK) WHERE Application LIKE

BEGIN

 RETURN -1000
END
ELSE
BEGIN


END


BEGIN

 RETURN -1001
END
ELSE
BEGIN

END

RETURN 0
GO


Quote:
> Hello John,
> Can you please post the script for your stored procedure, I will try to
> recreate the problem on my side and will update you.

> Thanks

> Ramesh Thyagarajan, MCSD,MCDBA
> Microsoft Developer support

> This posting is provided "AS IS" with no warranties, and confers no
rights.

> Are you secure? For information about the Microsoft Strategic Technology
> Protection Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.



Tue, 30 Aug 2005 06:42:47 GMT  
 ADO SP ReturnValue Params not working and problems with Command.Append
Chris,

I haven't resolved this yet .... hopefully Ramesh can help us out :)


Quote:
> John,

> Did you have any luck ragarding the return parameters?

> I'm having exactly the same problem, when I try to
> ge the return parameter it says its set to Nothing :-(

> Chris.



Tue, 30 Aug 2005 06:44:54 GMT  
 ADO SP ReturnValue Params not working and problems with Command.Append
Hello John,
Thanks for posting the Stored procedure, that sure helped me find out what
is different in .net.
When you execute a stored procedure which returns a result set, the return
value is at the end of the stream.
So we have to read the resultset first before we could get to the return
value.
There are two ways to fix your problem.
1. If you are not interested in the result set getting returned. ie., if
you are not returning a resultset, you can use the
ExecuteOptionEnum.adExecuteNoRecords option in the command.execute so that
the return value will be available right away.
ex.
adocmd.Execute(,,ExecuteOptionEnum.adExecuteNoRecords)
If you cehck the returnvalue now you should get the correct value.

2. If you are returning a result set from your stored procedure. The
resultset need to be processed first before you can get to the return value.
For ex.
Dim rs as new adodb.recordset.
rs = adoCmd.Execute()
rs.close ' Finish reading the reult set.
'Once we finish reading the result set the return value will be available.
MsgBox("Test 1 : SP Return Value = " & adoprmRetVal.Value)

Hope this answers your question.

Ramesh Thyagarajan, MCSD,MCDBA
Microsoft Developer support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.



Tue, 30 Aug 2005 22:54:38 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Creating ADO recordset w/o database .Append not working correctly IIS5 win2000 Prof not .Net

2. DataEnv, ADO and VB6 Problem - Requery on command does not seem to work

3. showmodaldialog returnvalue arrays not working

4. ADODB.Command - ReturnValue parameter problem

5. ReturnValue when an SP error Occurs

6. ADO Find Command not working for me

7. Executing stored proc with Output params without ADO Command object

8. HowTo: Reuse ADO Command Params w/ Access 2K DB

9. ADO.Command Excute method with Parameters not working????

10. Visual basic does not work with SP-2

11. Q: DAO 3.5 w/ODBCDirect call Oracle SP w/Output Params

12. ASP.NET Crystal and a SP with params

 

 
Powered by phpBB® Forum Software