Member Avatar for MJV

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();
                        }
                    }
            }

        }
  • 3 Contributors
  • 2 Replies
  • 212 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by Seemant

Dani AI

Generated

A short diagnosis and checklist based on the thread: the stored procedure must declare the parameter as OUTPUT if you expect a parameter value back (RETURN is a separate integer return code). Common mistakes seen in the posts: using ExecuteScalar() instead of ExecuteNonQuery() for output parameters, adding the parameter as an input (via AddWithValue) without setting Direction, and a small syntax bug in the original assignment. and pointed in the right direction — prefer explicit SqlParameter objects and set Direction explicitly, and use using to ensure disposal.

Example stored-proc pattern (T-SQL) — the parameter must be declared OUTPUT:

CREATE PROCEDURE QuoteCreate
    @estnum INT,
    @quotenum INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    -- procedure logic that sets @quotenum
    SET @quotenum = (SELECT ISNULL(MAX(QuoteID),0) + 1 FROM Quotes);
END

Safe, minimal C# pattern to call it and read the output (different from the code already posted):

using (var conn = new SqlConnection(connString))
using (var cmd = new SqlCommand("QuoteCreate", conn) { CommandType = CommandType.StoredProcedure })
{
    cmd.Parameters.Add(new SqlParameter("@estnum", SqlDbType.Int) { Value = estno });
    var outParam = new SqlParameter("@quotenum", SqlDbType.Int) { Direction = ParameterDirection.Output };
    cmd.Parameters.Add(outParam);

    conn.Open();
    cmd.ExecuteNonQuery();                 // run the proc
    int quoteNo = outParam.Value != DBNull.Value ? Convert.ToInt32(outParam.Value) : 0;
    MessageBox.Show(quoteNo.ToString());
}

Quick tips: avoid AddWithValue for parameter type control, check the proc signature for the OUTPUT keyword, read the parameter value only after ExecuteNonQuery() completes, guard against DBNull.Value, and reuse the connection/command (change parameter values) if calling the proc in a loop for better performance.

Recommended Answers

All 2 Replies

Member Avatar for miculnegru

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;
Member Avatar for Seemant

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.