Parameters wont work on SQL Server sproc

  • Thread starter Thread starter A Traveler
  • Start date Start date
A

A Traveler

Help! Ive been searching google, msdn, trying every which way i can and
cannot get this to work.
I am new to SQL Svr, coming from an Oracle background.

I have a stored proc which given an input param of state returns the count
of records in that state. I know the proc works, because if i run it from
Query Analyzer, i get the proper results, so somehow, im doing something
wrong in my ADO.NET code. When i run the proc from adonet, i pass in
@STATE='NY', and have @COUNT specified as a sqlparam with direction of
output. There are three records for NY (this is testing right now). However
my @COUNT parameter always returns zero. If anyone could tell me why, id be
just delighted! Thanks. Code follows below.
===========================================
<stored proc>
CREATE PROCEDURE COUNT_PEOPLE_BY_STATE
(
@STATE VARCHAR(3),
@COUNT INT OUTPUT
)
AS
-- SET NOCOUNT ON -- turned off while testing

SELECT @COUNT = COUNT(*) FROM PEOPLE WHERE STATE = @STATE

RETURN
GO
</stored proc>
===========================================
<vb.net/ado.net code>
If cn.State <> ConnectionState.Open Then cn.Open()
cmd.Connection = cn '// cn is my SqlConnection object.
cmd.CommandType = System.Data.CommandType.StoredProcedure
cmd.CommandText = "COUNT_PEOPLE_BY_STATE"

cmd.Parameters.Add(New SqlParameter("@STATE", SqlDbType.VarChar,
3)).Value = txtSQL.Text
cmd.Parameters.Add(New SqlParameter("@COUNT",
SqlDbType.Int)).Direction = ParameterDirection.Output

cmd.ExecuteNonQuery()
MsgBox(cmd.Parameters("@COUNT").Value)

cn.Close()

</vb.net/ado.net code>
===========================================
 
I'm not sure, but I think the command must be closed before the output
paramaters are available. Think of the stored procedure like it was a
regular method. It won't return any values until its' done running.
 
Ben,

Im not sure what you mean. The SqlCommand object does not appear to have a
Close method.
 
You execute the command into an boject, dataread, or dataset... Close the
datareader and / or dataset and try accessing the parameters.
 
Back
Top