Post reply

Output variable does not return result when using SqlCommand?

  • 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

  • January 7, 2009 at 11:05 am

    #922749

    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

  • January 7, 2009 at 11:49 am

    #922785

    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

  • January 7, 2009 at 11:57 am

    #922792

    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 3 (of 3 total)

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