Member Avatar for MJV

I have the following code that executes an sql stored procedure and I pass a customer name and customer contact to the procedure to use to create a new record in a table. When I execute the stored procedure in sql it inputs the customer and contact in the new record but when I use the following code it creates a new record but the customer and contact are blank. Can anyone see what I have done wrong. Here is my code. Thanks in advance.

private void copyestBN_Click(object sender, EventArgs e)
    {
        {
            int estno;
            char customer;
            char contact;
            char.TryParse(customercopyCB.Text, out customer);
            char.TryParse(newcontactTB.Text, out contact);
            int.TryParse(estimatenocopyCB.Text, out estno);

            {
                SqlConnection conn = new SqlConnection("Data Source=server1;Initial Catalog=estimator;Integrated Security=True");
                SqlCommand cmd = new SqlCommand("copyestimate", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@estnum", estno);
                cmd.Parameters.AddWithValue("@customer", customer);
                cmd.Parameters.AddWithValue("@contact", contact);
                cmd.Parameters.AddWithValue("@newestnum", SqlDbType.Int).Direction = ParameterDirection.Output;

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    newestLB.Items.Add(Convert.ToString(cmd.Parameters["@newestnum"].Value.ToString()));

                }
                catch (SqlException err)
                {
                    MessageBox.Show(err.Message);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    conn.Dispose();
                }
            }
        }
    }
Edited by Reverend Jim because: Fixed formatting
  • 2 Contributors
  • 1 Reply
  • 132 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by JerryShaw

Dani AI

Generated

Two quick points explain why the rows were created with blank customer/contact and why the output param is fragile.

is correct: names and contacts are strings, not single characters. Using char.TryParse yields the default '\0' unless the input is exactly one character, so the stored proc receives an empty/zero char. Also the output parameter line in the original code uses AddWithValue("@newestnum", SqlDbType.Int) — that overload treats SqlDbType.Int as a value, not a type. The output parameter must be added with a proper SqlParameter (or Add(name, SqlDbType)), and its Direction set to Output.

A safe pattern:

  • Read UI values into string variables (not char).
  • Parse integers with int.TryParse.
  • Create parameters explicitly with SqlDbType and a sensible size for string params (avoids type/size inference problems).
  • Add the output parameter as a true output parameter.
  • Use using blocks so connections and commands are disposed.

Example (concise, corrected approach):

string customer = customercopyCB.Text;
string contact = newcontactTB.Text;
int estno = 0;
int.TryParse(estimatenocopyCB.Text, out estno);

using (var conn = new SqlConnection(connString))
using (var cmd = new SqlCommand("copyestimate", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@estnum", SqlDbType.Int) { Value = estno });
    cmd.Parameters.Add(new SqlParameter("@customer", SqlDbType.NVarChar, 150) { Value = (object)customer ?? DBNull.Value });
    cmd.Parameters.Add(new SqlParameter("@contact", SqlDbType.NVarChar, 150) { Value = (object)contact ?? DBNull.Value });
    var outP = new SqlParameter("@newestnum", SqlDbType.Int) { Direction = ParameterDirection.Output };
    cmd.Parameters.Add(outP);

    conn.Open();
    cmd.ExecuteNonQuery();
    if (outP.Value != DBNull.Value)
        newestLB.Items.Add(outP.Value.ToString());
}

Extra tips: avoid AddWithValue for parameters you care about — prefer explicit types and sizes. Confirm the stored-procedure parameter names/types match exactly. If values are still missing, inspect the parameter values in the debugger before ExecuteNonQuery() and add simple PRINT/select statements inside the SP or use SQL Profiler to see what arrives.

Member Avatar for JerryShaw

dont cast those strings to char
especially if is not a char[] array... hint

just send the text of the components into the parameter assignment.

cmd.Parameters.AddWithValue("@customer", customercopyCB.Text);
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.