Stored Procedure Not Passing Variables 2

[Saturn57]

I have the following stored procedure that does not seem to pass the @customer and @contact variable to the sql stored procedure to update a table. The @estnum is working but I cannot understand why the two other variables are not being used in the sql procedure to update a table. When I run this same procedure in sql and provide the parameters there the table is updated correctly. Does anyone see something wrong with my code?


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

 

[jmeckley]

it's been awhile since I worked with raw ADO objects, but I think this is the problem
new SqlCommand("copyestimate", conn);
i think it should read
new SqlCommand("copyestimate @estnum @customer @contact @newestnum output", conn);

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[Saturn57]

This did not work. Says it cannot find stored procedure ("copyestimate @estnum @customer @contact @newestnum output", conn

 

[LFCfan]

char.TryParse will return true or false, so you're not actually setting the values of customer/contact in your code.
Or indeed the integer value, but I couldn't tell you why that's being passed in, unless you were expecting a value of zero?

Also, is it intentional that you want to parse the values of the textboxes into a single character string (char datatype) instead of a variable length string (String datatype)
Seems a bit odd that a customer will have only one character

~LFCfan

 

[Saturn57]

Thanks LFCfan. No I do not want to pass in an integer. Can you stear me in the right direction on how to assign the text box value to a variable I can use in my code?

 

[jmeckley]

No I do not want to pass in an integer.

this is important. when assigning values to the parameters.
if your proc doesn't look like

create proc copyestimate
@estnum int,
@customer char(1),
@contact, char(1)
@newestnum int output
as
 ....

then the values you are passing from code do not match the values required by the stored proc it won't work.
try this instead

int estno = (int)estimatenocopyCB.Text;
string customer = customercopyCB.Text;
string contact = newcontactTB.Text;
//same code as before.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[LFCfan]

Also if you wanted to check that the text in the estimatenocopyCB textbox is a valid integer, use

try
{
    estno = Int32.Parse(estimatenocopyCB.Text);
}
catch....

~LFCfan

 

[jmeckley]

try/catching a parsed value is the same as

int i;
if(!int.TryParse("A", out i))
{
   //handle formatting error
}

this is much more succinct and readable than

int i;
try
{
   i = int.Parse("A");
}
catch(FormatException)
{
   i = 0;
   //handle formatting exception
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[LFCfan]

jmeckley

I actually looked in the msdn documentation for TryParse and couldn't find it for int - which would make sense as it's on Int[red]32[/red], d'oh

~LFCfan