Firing storedProc from ADODB.Command Object returns DBNull... WHY??????? #$$%#!!!

[W1ld0ne [MCSD]]

This is why programmers give up being programmers and become Belly dancers or scuba diving instructors.

The problem explained:

1.. This code works in a production site in a COM+ component built in VB6 using MDAC 2.7. (Well a slightly more complicated version of it... but none-the-less.)
2.. This code is about as simple as it gets.... (Driving me insane!!!)
3.. I am trying to make this work in a .Net class library.
4.. I am currently using ADODB.Net but I have substituted ADODB 2.7 (Microsoft ActiveX Data Objects 2.7) (Still nothing)
5.. I have also included the stored procedure (Rocket science bit there) to this post, in case I am doing something really stupid at that step.
6.. The stored proc fires perfectly in MS SQL Query Analyser.
7.. From this code, called from another .Net Windows Application, ALWAYS RETURNS DBNULL on the output parameter.
8.. Even Parameter 0 (Default return parameter) returns NOTHING
Please, please, please tell me what I am doing wrong!!!

Thanks
David Taylor
Ps. help!!!

The offending Method:
Public Function ExecStoredProc(ByVal StoredProc As String) As String

Try

Dim cmStoredProc As New ADODB.Command
With cmStoredProc
.ActiveConnection = cnSQLAO
.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
.CommandText = StoredProc
.Parameters(1).Type = ADODB.DataTypeEnum.adVarChar
.Parameters(1).Size = 50
.Execute()
End With

Return (cmStoredProc.Parameters(1).Value)

cmStoredProc = Nothing

Catch ex As Exception

ex.Source = TypeName(Me) & ": ExecStoredProc(" & ex.Source & ")"

Throw ex

End Try

End Function

The Really complicated SQL 2000 Stored Procedure in question:

CREATE PROCEDURE [dbo].[sp_CheckAccess]
@Result VARCHAR(50) OUTPUT
AS
SELECT @Result = 'Hello World!'
RETURN(1)

 

[Prateek]

Hi,

Here is how you would execute the stored procedure using ADO.NET

--------------------------------------------------------

Dim con As New SqlClient.SqlConnection(connectionString)

Dim cmd As New SqlClient.SqlCommand("sp_CheckAccess", con)

con.Open()

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@Result", "HI")

Dim sampParm As SqlClient.SqlParameter = cmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)

sampParm.Direction = ParameterDirection.ReturnValue

cmd.ExecuteNonQuery()

MsgBox(cmd.Parameters(1).Value)

--------------------------------------------------------

This is why programmers give up being programmers and become Belly dancers or scuba diving instructors.

The problem explained:

1.. This code works in a production site in a COM+ component built in VB6 using MDAC 2.7. (Well a slightly more complicated version of it... but none-the-less.)
2.. This code is about as simple as it gets.... (Driving me insane!!!)
3.. I am trying to make this work in a .Net class library.
4.. I am currently using ADODB.Net but I have substituted ADODB 2.7 (Microsoft ActiveX Data Objects 2.7) (Still nothing)
5.. I have also included the stored procedure (Rocket science bit there) to this post, in case I am doing something really stupid at that step.
6.. The stored proc fires perfectly in MS SQL Query Analyser.
7.. From this code, called from another .Net Windows Application, ALWAYS RETURNS DBNULL on the output parameter.
8.. Even Parameter 0 (Default return parameter) returns NOTHING
Please, please, please tell me what I am doing wrong!!!

Thanks
David Taylor
Ps. help!!!

The offending Method:
Public Function ExecStoredProc(ByVal StoredProc As String) As String

Try

Dim cmStoredProc As New ADODB.Command
With cmStoredProc
.ActiveConnection = cnSQLAO
.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
.CommandText = StoredProc
.Parameters(1).Type = ADODB.DataTypeEnum.adVarChar
.Parameters(1).Size = 50
.Execute()
End With

Return (cmStoredProc.Parameters(1).Value)

cmStoredProc = Nothing

Catch ex As Exception

ex.Source = TypeName(Me) & ": ExecStoredProc(" & ex.Source & ")"

Throw ex

End Try

End Function

The Really complicated SQL 2000 Stored Procedure in question:

CREATE PROCEDURE [dbo].[sp_CheckAccess]
@Result VARCHAR(50) OUTPUT
AS
SELECT @Result = 'Hello World!'
RETURN(1)

 

[Prateek]

Hi,

A better method would be:


----------------------------------------------------
Dim con As New SqlClient.SqlConnection(conString)
Dim cmd As New SqlClient.SqlCommand("sp_CheckAccess", con)

cmd.CommandType = CommandType.StoredProcedure

Dim outParm As SqlClient.SqlParameter = cmd.Parameters.Add("@Result", SqlDbType.VarChar, 50)
outParm.Direction = ParameterDirection.Output

Dim sampParm As SqlClient.SqlParameter = cmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
sampParm.Direction = ParameterDirection.ReturnValue

con.Open()
cmd.ExecuteNonQuery()

MsgBox(cmd.Parameters("@Result").Value)
MsgBox(cmd.Parameters("RETURN_VALUE").Value)

----------------------------------------------------

-Prateek

This is why programmers give up being programmers and become Belly dancers or scuba diving instructors.

The problem explained:

1.. This code works in a production site in a COM+ component built in VB6 using MDAC 2.7. (Well a slightly more complicated version of it... but none-the-less.)
2.. This code is about as simple as it gets.... (Driving me insane!!!)
3.. I am trying to make this work in a .Net class library.
4.. I am currently using ADODB.Net but I have substituted ADODB 2.7 (Microsoft ActiveX Data Objects 2.7) (Still nothing)
5.. I have also included the stored procedure (Rocket science bit there) to this post, in case I am doing something really stupid at that step.
6.. The stored proc fires perfectly in MS SQL Query Analyser.
7.. From this code, called from another .Net Windows Application, ALWAYS RETURNS DBNULL on the output parameter.
8.. Even Parameter 0 (Default return parameter) returns NOTHING
Please, please, please tell me what I am doing wrong!!!

Thanks
David Taylor
Ps. help!!!

The offending Method:
Public Function ExecStoredProc(ByVal StoredProc As String) As String

Try

Dim cmStoredProc As New ADODB.Command
With cmStoredProc
.ActiveConnection = cnSQLAO
.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
.CommandText = StoredProc
.Parameters(1).Type = ADODB.DataTypeEnum.adVarChar
.Parameters(1).Size = 50
.Execute()
End With

Return (cmStoredProc.Parameters(1).Value)

cmStoredProc = Nothing

Catch ex As Exception

ex.Source = TypeName(Me) & ": ExecStoredProc(" & ex.Source & ")"

Throw ex

End Try

End Function

The Really complicated SQL 2000 Stored Procedure in question:

CREATE PROCEDURE [dbo].[sp_CheckAccess]
@Result VARCHAR(50) OUTPUT
AS
SELECT @Result = 'Hello World!'
RETURN(1)

 

[Herfried K. Wagner [MVP]]

Hello,

[Database problem]

This is a VB.NET language group. Notice that you will have a better
chance to get an answer if you post to the ADO.NET newsgroup in future:

news://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet

Web interface:

http://msdn.microsoft.com/newsgroup...roup=microsoft.public.dotnet.framework.adonet

 

[Fergus Cooney]

Hi W1zard,

|| This is why programmers give up being programmers
|| and become Belly dancers or scuba diving instructors

LOL.


I only have a couple of suggestions, I'm afraid.

|| .ActiveConnection = cnSQLAO

Can you do other stuff with this connection. Ie, it works?


|| Dim cmStoredProc As New ADODB.Command

Have you tried using the .NET Framework classes rather than ADODB?

Dim cmStoredProc As New OleDb.Command
Dim cmStoredProc As New SqlDb.Command

Regards,
Fergus

 

[W1ld0ne [MCSD]]

Okay, I finally got 'Hello World' back from my stored procedure! Cool!

I used sample code supplied by Prateek. (Thanks tons bud)

My problem is however, that I can use the ADODB.Command object, firing a stored procedure to return a stored procedure in the same class. Why does the ADODB method work when returning a recordset and not when I return a single parameter.

@Fergus Cooney: I'm going to have a look at OleDb.Command and SqlDb.Command because I need now to convert the function that returns recordsets to use the same method.

I suppose this is just because I am new to .Net.

Thanks for everyone's help, I hate to overstay my welcome, but if anyone feels like posting sample code for the following function (This works by the way, but I would like to convert it to full .Net as well), it would also be appreciated.

Dim cmStoredProc As New ADODB.Command

With cmStoredProc
.ActiveConnection = cnSQLAO
.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
.CommandText = StoredProc
End With

Dim rsStoredProc As New ADODB.Recordset

rsStoredProc = cmStoredProc.Execute

If rsStoredProc.EOF Then
Return ("")
Else
Return (rsStoredProc.GetString(, , vbTab, vbCrLf))
End If

rsStoredProc = Nothing

cmStoredProc = Nothing

Thanks to all the guys above, we have one less belly dancer in the world. (The world appreciates your help as much as I do now!)

Thanks
David Taylor

This is why programmers give up being programmers and become Belly dancers or scuba diving instructors.

The problem explained:

1.. This code works in a production site in a COM+ component built in VB6 using MDAC 2.7. (Well a slightly more complicated version of it... but none-the-less.)
2.. This code is about as simple as it gets.... (Driving me insane!!!)
3.. I am trying to make this work in a .Net class library.
4.. I am currently using ADODB.Net but I have substituted ADODB 2.7 (Microsoft ActiveX Data Objects 2.7) (Still nothing)
5.. I have also included the stored procedure (Rocket science bit there) to this post, in case I am doing something really stupid at that step.
6.. The stored proc fires perfectly in MS SQL Query Analyser.
7.. From this code, called from another .Net Windows Application, ALWAYS RETURNS DBNULL on the output parameter.
8.. Even Parameter 0 (Default return parameter) returns NOTHING
Please, please, please tell me what I am doing wrong!!!

Thanks
David Taylor
Ps. help!!!

The offending Method:
Public Function ExecStoredProc(ByVal StoredProc As String) As String

Try

Dim cmStoredProc As New ADODB.Command
With cmStoredProc
.ActiveConnection = cnSQLAO
.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
.CommandText = StoredProc
.Parameters(1).Type = ADODB.DataTypeEnum.adVarChar
.Parameters(1).Size = 50
.Execute()
End With

Return (cmStoredProc.Parameters(1).Value)

cmStoredProc = Nothing

Catch ex As Exception

ex.Source = TypeName(Me) & ": ExecStoredProc(" & ex.Source & ")"

Throw ex

End Try

End Function

The Really complicated SQL 2000 Stored Procedure in question:

CREATE PROCEDURE [dbo].[sp_CheckAccess]
@Result VARCHAR(50) OUTPUT
AS
SELECT @Result = 'Hello World!'
RETURN(1)

 

[Jeremy Cowles]

This is not an ADO group, but your problem is that you did not make the
parameter an output param:


With cmStoredProc
.ActiveConnection = cnSQLAO
.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
.CommandText = StoredProc
.Parameters(1).Type = ADODB.DataTypeEnum.adVarChar
.Parameters(1).Size = 50

'// CHANGED:
.Parameters(1).Direction = ParameterDirection.ReturnValue

.Execute()
End With


HTH,
Jeremy

This is why programmers give up being programmers and become Belly dancers
or scuba diving instructors.

The problem explained:

1.. This code works in a production site in a COM+ component built in
VB6 using MDAC 2.7. (Well a slightly more complicated version of it... but
none-the-less.)
2.. This code is about as simple as it gets.... (Driving me insane!!!)
3.. I am trying to make this work in a .Net class library.
4.. I am currently using ADODB.Net but I have substituted ADODB 2.7
(Microsoft ActiveX Data Objects 2.7) (Still nothing)
5.. I have also included the stored procedure (Rocket science bit there)
to this post, in case I am doing something really stupid at that step.
6.. The stored proc fires perfectly in MS SQL Query Analyser.
7.. From this code, called from another .Net Windows Application, ALWAYS
RETURNS DBNULL on the output parameter.
8.. Even Parameter 0 (Default return parameter) returns NOTHING
Please, please, please tell me what I am doing wrong!!!

Thanks
David Taylor
Ps. help!!!

The offending Method:
Public Function ExecStoredProc(ByVal StoredProc As String) As String

Try

Dim cmStoredProc As New ADODB.Command
With cmStoredProc
.ActiveConnection = cnSQLAO
.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
.CommandText = StoredProc
.Parameters(1).Type = ADODB.DataTypeEnum.adVarChar
.Parameters(1).Size = 50
.Execute()
End With

Return (cmStoredProc.Parameters(1).Value)

cmStoredProc = Nothing

Catch ex As Exception

ex.Source = TypeName(Me) & ": ExecStoredProc(" & ex.Source & ")"

Throw ex

End Try

End Function

The Really complicated SQL 2000 Stored Procedure in question:

CREATE PROCEDURE [dbo].[sp_CheckAccess]
@Result VARCHAR(50) OUTPUT
AS
SELECT @Result = 'Hello World!'
RETURN(1)

 

[Fergus Cooney]

Hi D.Whizz,

|| @Fergus Cooney

Just 'Fergus' is fine. ;-)

|| we have one less belly dancer in the world.
|| (The world appreciates your help as much as I do now!)

LOL. My cousin's girlfriend is a bellydancer. Given the choice.... hmmm
.... I think I'm glad that you're getting into the swing of .NET rather than of
the hips. ;-)

Regards,
Fergus

 

[W1ld0ne [MCSD]]

Thanks for the note Jeremy, But alas, twas not the err of my ways

In the code snippet I did forget to include that line, but I did have it and the problem persisted.

Thanks anyway.
This is not an ADO group, but your problem is that you did not make the parameter an output param:


With cmStoredProc
.ActiveConnection = cnSQLAO
.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
.CommandText = StoredProc
.Parameters(1).Type = ADODB.DataTypeEnum.adVarChar
.Parameters(1).Size = 50

'// CHANGED:
.Parameters(1).Direction = ParameterDirection.ReturnValue

.Execute()
End With


HTH,
Jeremy

This is why programmers give up being programmers and become Belly dancers or scuba diving instructors.

The problem explained:

1.. This code works in a production site in a COM+ component built in VB6 using MDAC 2.7. (Well a slightly more complicated version of it... but none-the-less.)
2.. This code is about as simple as it gets.... (Driving me insane!!!)
3.. I am trying to make this work in a .Net class library.
4.. I am currently using ADODB.Net but I have substituted ADODB 2.7 (Microsoft ActiveX Data Objects 2.7) (Still nothing)
5.. I have also included the stored procedure (Rocket science bit there) to this post, in case I am doing something really stupid at that step.
6.. The stored proc fires perfectly in MS SQL Query Analyser.
7.. From this code, called from another .Net Windows Application, ALWAYS RETURNS DBNULL on the output parameter.
8.. Even Parameter 0 (Default return parameter) returns NOTHING
Please, please, please tell me what I am doing wrong!!!

Thanks
David Taylor
Ps. help!!!

The offending Method:
Public Function ExecStoredProc(ByVal StoredProc As String) As String

Try

Dim cmStoredProc As New ADODB.Command
With cmStoredProc
.ActiveConnection = cnSQLAO
.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
.CommandText = StoredProc
.Parameters(1).Type = ADODB.DataTypeEnum.adVarChar
.Parameters(1).Size = 50
.Execute()
End With

Return (cmStoredProc.Parameters(1).Value)

cmStoredProc = Nothing

Catch ex As Exception

ex.Source = TypeName(Me) & ": ExecStoredProc(" & ex.Source & ")"

Throw ex

End Try

End Function

The Really complicated SQL 2000 Stored Procedure in question:

CREATE PROCEDURE [dbo].[sp_CheckAccess]
@Result VARCHAR(50) OUTPUT
AS
SELECT @Result = 'Hello World!'
RETURN(1)