Can't read result of simple stored procedure

sg48 · February 15th, 2010, 04:15 PM

I was debugging a complex stored procedure involving dates and not getting a result.

I then modified the procedure to always retuirns an integer = 148. The procedure works on a standalone basis. I still can't read it in my ASP.NET code. My function always returns 0. What am I missing? TIA

stored procedure & code below

Code:

ALTER PROCEDURE [dbo].[procMinIdByDateOutput_B] 
 
( @minid INT =1 OUTPUT) 
AS 
SET @minid = (148)
RETURN @minid

Code:

 
ProtectedFunction getstartid(ByVal startdate AsDate) AsInteger
Dim Conn AsNew SqlConnection(SqlDataSource1.ConnectionString)
Dim Cmd1 AsNew SqlCommand("procMinIdByDateOutput_B", Conn)
Dim minid AsInteger = 1
Cmd1.CommandType = Data.CommandType.StoredProcedure
Dim minidparam AsNew SqlParameter("@minid", Data.SqlDbType.Int)
Cmd1.Parameters.Add(minidparam)
minidparam.Direction = Data.ParameterDirection.Output
Conn.Open()
minid = CType(Cmd1.ExecuteScalar(), Integer)
Conn.Close()
Return minid
EndFunction

Old Pedant · February 15th, 2010, 08:23 PM

When you call ExecuteScalar, ADO.NET is expecting that the SQL that is invoked--whether ad hoc or stored proc--is going to return a one-row, one-column recordset.

So you would need to do:

Code:

ALTER PROCEDURE [dbo].[procMinIdByDateOutput_B] 
 ( ) 
AS 
DECLARE @minid INT
SET @minid = (148) -- or whatever you really do to get this single value
SELECT @minid

The return value is *NOT* used by ExecuteScalar.

sg48 · February 16th, 2010, 12:34 PM

Thank you. That was very helpful