asked on

MS SQL Update command in C#

Ok I have some Text Boxes that the user is filling out that I want to use to update fields on a MS SQL 2008 Server. I have been beating my head searching all over google for a day and am stumped/burnt out. My code is in C# as follows and any help is appreciated!
thanks!

        string myConnectionString = @"Data Source=sql2;Initial Catalog=bleh;User Id =bleh;Password=bleh";
        SqlConnection myConnection = new SqlConnection(myConnectionString);

        String myUpdateQuery = @"UPDATE Vendors SET Vendor=@Vendor, 
Address=@Address, City=@City, State=@State, Zip=@Zip 
WHERE Vendor=@Vendor";
        SqlCommand MyCmd = new SqlCommand(myUpdateQuery);

        MyCmd.Parameters.Add("@Vendor", SqlDbType.NVarChar, 100, vendorTextBox.Text);
        MyCmd.Parameters.Add("@Address", SqlDbType.NVarChar, 100, addyTextBox.Text);
        MyCmd.Parameters.Add("@City", SqlDbType.NVarChar, 100, cityTextBox.Text);
        MyCmd.Parameters.Add("@State", SqlDbType.NVarChar, 30, stateTextBox.Text);
        MyCmd.Parameters.Add("@Zip", SqlDbType.NVarChar, 20, zipTextBox.Text);

        MyCmd.Connection = myConnection;
        myConnection.Open();
        MyCmd.ExecuteNonQuery();
        myConnection.Close();

Open in new window

SOLUTION

Paul Jackson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

devlab2012

you have parameter @vendor twice. Use it only once or if you need at both the place, use two separate name like @vendor1 and @vendor2
and you can pass the same value to both of these parameters

malikirfan28

Kindly pass your parameter as given here.

SqlParameter param = MyCmd.Parameters.Add("@Vendor", SqlDbType.NVarChar, 100);
param.Value = vendorTextBox.Text;
param = MyCmd.Parameters.Add("@Address", SqlDbType.NVarChar, 100);
param.Value = addyTextBox.Text;
param = MyCmd.Parameters.Add("@City", SqlDbType.NVarChar, 100);
param.Value = cityTextBox.Text;
param = MyCmd.Parameters.Add("@State", SqlDbType.NVarChar, 30);
param.Value = stateTextBox.Text;
param = MyCmd.Parameters.Add("@Zip", SqlDbType.NVarChar, 20);
param.Value = zipTextBox.Text;

I hope it will help.

SOLUTION

devlab2012

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

brutaldev

MyCmd.Parameters.AddWithValue("@param", valueOfAnyType);

Using this method leaves the types and sizes out of the mix in case your database fields change.

Also consider putting the command and connection creation (new) in a using statement so that the connection is always closed and the command is cleaned up in case of failure.

-AAA-

ASKER

jacko72, your code works when doing the update but only when combined devlab2012 updated SQL command:

String myUpdateQuery = @"UPDATE Vendors SET Address=@Address, City=@City, state=@State, Zip=@Zip WHERE Vendor=@Vendor";

Open in new window

So this makes it seem like I will be unable to update the actual vendor name. any ideas for that or does it not makes sense to change the value of what i am actually searching by?

ASKER CERTIFIED SOLUTION

Paul Jackson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

-AAA-

ASKER

yes I should be doing it by a unique key which solves the problem. Thanks for your help