none
how to get return value from stored proc in QueriesTableAdapter RRS feed

  • Question

  • I am calling a stored proc with a int return param but can't seem to get the return value.

    The SP is defined as:
    CREATE PROCEDURE dbo.updateCount (@count int, @return_code int)
    ...
    set @return_code = ...
    Return @return_code

    Using QueriesTableAdapter the calling code is:

    Int32 returnValue;
    myQueriesTableAdapter
    StoredProcs = new myQueriesTableAdapter();
    returnValue = Convert.ToInt32(StoredProcs.updateCount(count, returnValue));

    returnValue is always set to 0.

    MSDN indicates there should a get_Value() member of the SP, but I don't see this member defined. I know the return value from the SP is working because I can use "Preview Data..." in the DataSet designer to call it and the correct return value is displayed in the dialog's user interface.

    What am I doing wrong here?

    Saturday, October 10, 2009 8:16 PM

Answers

  • Hi Michael,

     

    Could you give more description about your project?

     

    We usually get return value like this,

     

    string connectionString =

      @"Server=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;";

    using (SqlConnection conn = new SqlConnection(connectionString))

    {

        using (SqlCommand cmd = new SqlCommand("dbo.TestReturn"))

        {

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@Invalue", 3));

     

            SqlParameter returnValue = new SqlParameter("@Return_Value", DbType.Int32);

            returnValue.Direction = ParameterDirection.ReturnValue;

     

            cmd.Parameters.Add(returnValue);

     

            conn.Open();

            cmd.Connection = conn;

     

            cmd.ExecuteNonQuery();

            int count = Int32.Parse(cmd.Parameters["@Return_Value"].Value.ToString());

            Response.Write("<p>Return Code: " + count.ToString());

            conn.Close();

        }

    }

     

    The code from this article,

    http://www.sqlteam.com/article/stored-procedures-returning-data

     

     

    Please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun_Feng Friday, October 16, 2009 1:37 AM
    Wednesday, October 14, 2009 4:02 AM