I have a stored procedure which returns a parameter @quotenum when run in sql but I'm having trouble getting a return value in my C# form. I'm trying to get the message box to show the @quoteno parameter but im failing misserably. The connection is working an the parameter @estnum is being passed but Im having trouble with the code to pick up the return value. ANy help correcting this code would be appreciated.

private void approveestBN_Click(object sender, EventArgs e)
        {
            {
                int estno;
                int endestno;
                int.TryParse(endTB.Text, out endestno);
                int quoteno =0;
                int @quotenum =0;
                

                if (int.TryParse(startestnoCB.Text, out estno))
                    while (estno <= endestno)
                    {
                        SqlConnection conn = new SqlConnection("Data Source=server1;Initial Catalog=estimator;Integrated Security=True");
                        SqlCommand cmd = new SqlCommand("QuoteCreate", conn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@estnum", estno);
                        cmd.Parameters.AddWithValue("@quotenum",quoteno);
                        
                        
                        try
                        {
                            conn.Open();
                            //cmd.ExecuteNonQuery();  // If you do not need a return;
                            cmd.ExecuteScalar();
                            quoteno = @quotenum";
                         
                            MessageBox.Show(Convert.ToString(quoteno));
                            estno = estno + 1;

                        }
                        catch (SqlException err)
                        {
                            MessageBox.Show(err.Message);
                        }
                        finally
                        {
                            if (conn.State == ConnectionState.Open)
                                conn.Close();
                            conn.Dispose();
                        }
                    }
            }

        }

it should be done with return or output parameter direction depending on you stored procedure.

in case it's a return value;

SqlParameter outParameter = new SqlParameter("quotenum", SqlDbType.Int); // or what ever type your return data is;
outParameter.Direction = System.Data.ParameterDirection.ReturnValue;

// now add it to the sql command
cmd.Parameters.Add(outParameter);

// now execute Non-Query
connection.ExecuteNonQuery();

if the stored procedure has a output type then the parameter direction it's output.

outParameter.Direction = System.Data.ParameterDirection.OutPut;

I have a stored procedure which returns a parameter @quotenum when run in sql but I'm having trouble getting a return value in my C# form. I'm trying to get the message box to show the @quoteno parameter but im failing misserably. The connection is working an the parameter @estnum is being passed but Im having trouble with the code to pick up the return value. ANy help correcting this code would be appreciated.

private void approveestBN_Click(object sender, EventArgs e)
        {
            {
                int estno;
                int endestno;
                int.TryParse(endTB.Text, out endestno);
                int quoteno =0;
                int @quotenum =0;
                

                if (int.TryParse(startestnoCB.Text, out estno))
                    while (estno <= endestno)
                    {
                        SqlConnection conn = new SqlConnection("Data Source=server1;Initial Catalog=estimator;Integrated Security=True");
                        SqlCommand cmd = new SqlCommand("QuoteCreate", conn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@estnum", estno);
                        cmd.Parameters.AddWithValue("@quotenum",quoteno);
                        
                        
                        try
                        {
                            conn.Open();
                            //cmd.ExecuteNonQuery();  // If you do not need a return;
                            cmd.ExecuteScalar();
                            quoteno = @quotenum";
                         
                            MessageBox.Show(Convert.ToString(quoteno));
                            estno = estno + 1;

                        }
                        catch (SqlException err)
                        {
                            MessageBox.Show(err.Message);
                        }
                        finally
                        {
                            if (conn.State == ConnectionState.Open)
                                conn.Close();
                            conn.Dispose();
                        }
                    }
            }

        }

Hi MJV,
I have edited your code, try this. I hope it should work in your case.
private void approveestBN_Click(object sender, EventArgs e)
{
{
int estno;
int endestno;
int.TryParse(endTB.Text, out endestno);
int quoteno =0;
int @quotenum =0;


if (int.TryParse(startestnoCB.Text, out estno))
while (estno <= endestno)
{
SqlConnection conn = new SqlConnection("Data Source=server1;Initial Catalog=estimator;Integrated Security=True");
SqlCommand cmd = new SqlCommand("QuoteCreate", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@estnum", estno);
//Change Here ( we need to define the direction of parameter, by default
// it is Input direction.
cmd.Parameters.AddWithValue("@quotenum", SqlDbType.Int).Direction = ParameterDirection.Output;


try
{
conn.Open();
cmd.ExecuteNonQuery(); // If you do not need a return;
//cmd.ExecuteScalar();
//quoteno = @quotenum;
// Change Here
MessageBox.Show(Convert.ToString(cmd.Parameters["@quotenum"].Value.ToString()));
estno = estno + 1;

}
catch (SqlException err)
{
MessageBox.Show(err.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
}
}
}

}

commented: Thank you so much for your help. +1
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.