Retrieving Data from a Stored Procedure

[whateveragain]

I'm trying to retrieve a value from a stored procedure but the SP doesn't return the correct value.

asp.net VB code:

Sub ckselections()
Dim sqlConn As New SqlConnection
Dim sqlComm As New SqlCommand

'Create a SqlParameter object to hold the output parameter value
MyID = Session.SessionID()
sqlComm.Connection = sqlConn
sqlConn.ConnectionString = Session("MyString")
sqlComm.CommandText = "ckselections" '-- specifying the command text
sqlComm.CommandType = CommandType.StoredProcedure '-- specifying the command text is a stored proc
With sqlComm

'-- adding the parameters
.Parameters.Add("@MyId", SqlDbType.VarChar, 50, Session("sessionid"))

'-- specifying the parameter directions
.Parameters("@MyId").Direction = ParameterDirection.Input

'-- assigning values to the parameters
.Parameters("@MyId").Value = MyID
End With
'-- opening the connection
sqlConn.Open()

Dim nselections As New SqlParameter("@nSelections", SqlDbType.BigInt)
nselections.Direction = ParameterDirection.Output
sqlComm.Parameters.Add(nselections)
sqlComm.Connection = sqlConn
sqlComm.ExecuteNonQuery()
'-- executing the command

'Now you can grab the output parameter's value...
Dim reader As SqlDataReader = sqlComm.ExecuteReader()
' Dim nnum As Integer = nselections.Value
'Dim nnum As Integer = Convert.ToDecimal(nselections.Value)
Dim nnum As Integer = Convert.ToInt16(nselections.Value)
'-- close the connection
If IsNumeric(nnum) Then
If nnum = 0 Then --Value of nnum is incorrect at this point
MsgBox("nnum = 0")
ElseIf nnum = 1 Then
MsgBox("nnum = 1")
ElseIf nnum = 2 Then
MsgBox("nnum = 2")
End If
Else
MsgBox("nnum is not numeric")
End If
sqlConn.Close()
End Sub


Stored Procedure:
ALTER PROCEDURE dbo.ckselections
(
@MyID varchar(55),
@nSelections bigint OUTPUT
)
AS
/* SET NOCOUNT ON */
declare @tmpselcond varchar(55) = 'tmpselcond' + ltrim(rtrim(@MyID))
set rowcount 0
exec('select * from ' + @tmpselcond + ' where selcode = 1')
set @nSelections = @@ROWCOUNT

print '@nSelections = '
print @nSelections --Value of @nSelections is correct at this point

RETURN @nSelections

 

[jmeckley]

to start, you say the stored proc is returning the wrong value. is the proc returning the wrong value, or is your code returning the wrong value? if the proc works, the code is the problem. if the proc does work fix the proc, then fix your code (because it is broken).

to start you need to understand how the command object works. after you have the command setup with text, type, parameters and a connection, you will execute 1 of 3 actions
ExecuteReader
ExecuteScalar
ExecuteNonReader

you wouldn't execute a combination of these methods on the same command they each have a different meaning.

reader will return a result set, tabular data. used 99% of the time.
scalar is used when you are returning a single value from the query. for example

select top 1 field from table where ...

or

declar @i int
select @i = id from table where ...
return @i

nonreader is used when you alter the data: insert, update, delete

output parameters are the oddball. if you're has output put parameters you can set the direction of the parameter on the command. after you execute one of the actions above you can get the value from the parameter

var p = command.AddParameter("name", type);
p.Direction = Direction.Out;
command.ExecuteNonreader();
return command.Parameters["name"].Value;

then you have the issue of object disposal. you need to properly dispose of connetions/command/transactions to prevent memory leaks.

that's another discussion. if you are interested I posted an FAQ on the subject. The link is in my signature below.

Jason Meckley
Programmer

faq855-7190
faq732-7259