ExamGuru IT/.Net Guru : Parameters wont work on SQL Server sproc on microsoft.public.dotnet.framework.adonet

.NETGURU

Parameters wont work on SQL Server sproc

Messages

Related Types

This message was discovered on microsoft.public.dotnet.framework.adonet.

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>
===========================================

Reply to this message...

Ben

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.

"A Traveler" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

A Traveler

Ben,

Im not sure what you mean. The SqlCommand object does not appear to have a
Close method.

"Ben" <Click here to reveal e-mail address> wrote in message
news:gcvYc.1803$Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Ben

You execute the command into an boject, dataread, or dataset... Close the
datareader and / or dataset and try accessing the parameters.
"A Traveler" <Click here to reveal e-mail address> wrote in message
news:eBr%Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

	System.Data.CommandType
	System.Data.ConnectionState
	System.Data.ParameterDirection
	System.Data.SqlClient.SqlCommand
	System.Data.SqlClient.SqlConnection
	System.Data.SqlClient.SqlParameter
	System.Data.SqlDbType

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification