Output variable does not return result when using SqlCommand?
-
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
-
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 -
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
-
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply