SQL Server stored procedure return value to string

Question

I have a stored procedure that will return the SCOPE_IDENTITY() which is the ID for the row just added.

I have run the procedure from my C# application and adds the correct data to the database. What I need is for this returned value to be stored as a string in C~ so I can populate a text box in the UI.

SqlConnection con = new SqlConnection(connectionString);
con.Open();

SqlDataAdapter aa = new SqlDataAdapter("sp_insert_order", con);
aa.SelectCommand.CommandType = CommandType.StoredProcedure;
aa.SelectCommand.Parameters.Add("@customer_id", SqlDbType.VarChar, (50)).Value = comboBox1.SelectedItem;

aa.SelectCommand.ExecuteNonQuery();

con.Close();

Changed to

SqlConnection con = new SqlConnection(connectionString);
con.Open();

SqlDataAdapter aa = new SqlDataAdapter("sp_insert_order", con);
aa.SelectCommand.CommandType = CommandType.StoredProcedure;
aa.SelectCommand.Parameters.Add("@customer_id", SqlDbType.VarChar, (50)).Value = comboBox1.SelectedItem;

object oString = aa.SelectCommand.ExecuteScalar();

string myString = "";

if (oString != null)
{
    myString = oString.ToString();
    textBox1.Text = myString;
}

Textbox1 is still blank. :(

Is the stored procedure actually returning the value? If not, return it as a value like you would any data element. — UnhandledExcepSean, Dec 14 '18 at 16:13
The implementation of the stored procedure is key to your problem but you haven't shown it. Do you mean you `RETURN` the id or do you `SELECT` it? For `RETURN` you need a `SqlParameter` with direction of `ReturnValue`. However, return values should only be used to communicate status, not data. — Crowcoder, Dec 14 '18 at 17:28
Unrelated tips: There's no need for a Data Adapter here: just use an SqlCommand. And by the way, SqlConnection, SqlCommand and SqlDataReader are all IDisposable so each should be in a `using` block. Once you've done that, you don't need to Close the connection because it will be closed by the implicit Dispose as it exits the block. — Richardissimo, Dec 14 '18 at 17:31
thank you all for your suggestions and further advise. I will see if the work later. My stored procedure inserts data and returns the scope identity. I have tested this and it works find in sql management studio — Chris Birch, Dec 15 '18 at 14:52

MTAdmin · Answer 1 · 2018-12-14T17:15:54.450

Ok, we're assuming your SProc is returning properly. Try assigning an output parameter as follows:

SqlConnection cnx = new SqlConnection(WebConfigurationManager.ConnectionStrings["yourConnName"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnx;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "testSProc";
cmd.Parameters.AddWithValue("name", "test Name");
SqlParameter outputParam = cmd.Parameters.Add("outID", SqlDbType.Int);
outputParam.Direction = ParameterDirection.Output;

object oString;

cnx.Open();
cmd.ExecuteNonQuery();
cnx.Close();

TextBox1.Text = outputParam.Value.ToString();

Textbox 1 is still blank. Changed my code as above – Chris Birch Dec 14 '18 at 16:02 — Chris Birch, Dec 14 '18 at 16:02

SQL Server stored procedure return value to string

1 Answers1