Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Output variable does not return result when using SqlCommand? Expand / Collapse
Author
Message
Posted Wednesday, January 7, 2009 7:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 15, 2012 11:06 AM
Points: 5, Visits: 16
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
Post #631464
Posted Wednesday, January 7, 2009 11:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 11,213, Visits: 12,969
Are you sure that This.UserName is returning the name in the format you need?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #631695
Posted Wednesday, January 7, 2009 11:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 2:04 AM
Points: 7,004, Visits: 8,444
What's your db collation ??

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


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #631737
Posted Wednesday, January 7, 2009 11:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 15, 2012 11:06 AM
Points: 5, Visits: 16
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
Post #631744
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse