[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS SQL Update command in C#

Posted on 2011-04-26
8
Medium Priority
?
677 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:-AAA-
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 1200 total points
ID: 35469221
try this :

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

Expert Comment

by:devlab2012
ID: 35469243
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
0
 
LVL 4

Expert Comment

by:malikirfan28
ID: 35469253
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Assisted Solution

by:devlab2012
devlab2012 earned 800 total points
ID: 35469256
try following code:

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

//modified the following query
        String myUpdateQuery = @"UPDATE Vendors SET 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();
0
 
LVL 11

Expert Comment

by:brutaldev
ID: 35469272
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.
0
 

Author Comment

by:-AAA-
ID: 35469430
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?
0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 1200 total points
ID: 35470118
It is a bit unusual to be updating a column that is part of your where clause and I'm not sure I've ever had to do it so DevLab may be right. Possibly you need to think about whether your Vendors table needs a unique key/id that would form the where clause rather than the vendor name. Then you would be able to update the vendor name in your update.
0
 

Author Comment

by:-AAA-
ID: 35470292
yes I should be doing it by a unique key which solves the problem.  Thanks for your help
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question