Output variable does not return result when using SqlCommand?

Question

Output variable does not return result when using SqlCommand?

les-981979

Valued Member

Points: 55
More actions
January 7, 2009 at 7:43 am

#217308

I am using VS2005 connecting to a hosted SQL 2005 server.
I have a stored procedure that looks like this...
--------------------------------------------------------
ALTER PROCEDURE [dbo].[GetSupplierID]
@SupplierName varchar(100),
@SupplierBranch varchar(100) = NULL,
@SupplierID bigint OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @SupplierID = SupplierID
FROM SU_Suppliers
WHERE (SupplierName = @SupplierName) AND
(@SupplierBranch IS NULL OR SupplierBranch = @SupplierBranch)
END
---------------------------------------------------------
if I run the stored procedure through SQL management studio it works fine and the SupplierID is returned in the OUTPUT parameter '@SupplierID'
When I try to do this in code it doesn't return anything and the result of the SQLCommand is -1
C# code...
----------------------------------------------------------
SqlCommand getCoID = new SqlCommand();
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SupportConnectionString"].ConnectionString);
getCoID.Connection = Conn;
getCoID.CommandType = CommandType.StoredProcedure;
getCoID.CommandText = "GetSupplierID";
getCoID.Parameters.Add("@SupplierName", SqlDbType.VarChar, 100).Value = ThisUser.UserName;
getCoID.Parameters.Add("@SupplierBranch", SqlDbType.VarChar, 100);
getCoID.Parameters.Add("@SupplierID", SqlDbType.BigInt);
getCoID.Parameters["@SupplierID"].Direction = ParameterDirection.Output;
Conn.Open();
int UCo = getCoID.ExecuteNonQuery();
Session["UserCompany"] = getCoID.Parameters["@SupplierID"].Value;
----------------------------------------------------------
Can anyone tell me why this SqlCommand is failing??
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

Are you sure that This.UserName is returning the name in the format you need?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 2

What's your db collation ??

(case sensitive , accent sensitive, ... ??)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

les-981979 Valued Member Points: 55 More actions · Answer 3

Jack,

Thanks a bunch man! Sometimes it helps to just get a different look at things. The ThisUser.Username is from the ASP.NET user membership after login. The problem was that I didn't want to use the username I was supposed to use the comments field where I store the company name of the user.

Working fine now.

Les