0

I have some code I've written in C# that is to update a table. While the code runs without raising an error, the table doesn't get updated.

If I take the SQL command and run in the SSMS query window it does work.

Here's the code:

        try
        {

            string connectionString = "Server=XXXX;Database=XXX;Integrated Security=True";

            using (SqlConnection connection = new SqlConnection(connectionString))
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = "update address set central_phone_number = '" + NewPhoneNumber + "'" + " where id = " + ID;

                connection.Open();

                int result = command.ExecuteNonQuery();

                connection.Close();
            }
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message, "SQL Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }

id is the primary key for the table so only a specific row gets updated.

Kritner
  • 13,557
  • 10
  • 46
  • 72
L. Levine
  • 145
  • 3
  • 16
  • What is your query looks like just before execute it? What are the types of your columns? You should always use [parameterized queries](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/) by the way. This kind of string concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. – Soner Gönül Feb 26 '16 at 15:22
  • The `ExecuteNonQuery` method returns a number, indicating the number of rows it affected. Did you check the `result` variable, is it 0? If so then your `where` clause simply did not match any rows. What, specifically, is `ID`? What is the content? What is the final SQL you're executing? – Lasse V. Karlsen Feb 26 '16 at 15:23
  • Before you do anything else you need to read about, understand and start using parameterized queries. This code is vulnerable to sql injection. – Sean Lange Feb 26 '16 at 15:23
  • Please Google "sql injection" and "parameterized query" – Tom H Feb 26 '16 at 15:23
  • Is ID passed in or something.. or ur getting the value of id from a textbox or something? try ID.text . – psj01 Feb 26 '16 at 15:23
  • @TomH - he may be new to it, have some patience. –  Feb 26 '16 at 15:25
  • 2
    You need to add the connection to the command before calling ExecuteNonQuery: command.Connection = connection; Nevermind - I see you created the command from the connection which links them. – Kevin Feb 26 '16 at 15:27
  • 1
    @JᴀʏMᴇᴇ how you changed your Display Name font? – Ehsan Sajjad Feb 26 '16 at 15:27
  • @JᴀʏMᴇᴇ I'm not criticizing the poster (although I realized that it sounded like that, which is why I had edited my comment). I can't believe that there are still websites and people out there who would teach someone this. He had to have learned it from somewhere. The fact that an example might still exist on the internet with code like this without a giant red box that says, "WARNING!!! NEVER DO THIS!!!" amazes me. – Tom H Feb 26 '16 at 15:27
  • `[address]` use like this – Jaydip Jadhav Feb 26 '16 at 15:27
  • can you give us more details on the types of the id field in the DB and ID in code? Show us some context. You may be comparing apple to oranges there. Other then that I don't see anything obvious. – Tudor Carean Feb 26 '16 at 15:32
  • I've recently answered a very similar question with additional links and resources: Possible duplicate of [ASP.NET, C# How to Pass a StringQuery to a custom SQL Command](http://stackoverflow.com/questions/35614173/asp-net-c-sharp-how-to-pass-a-stringquery-to-a-custom-sql-command) – Dan Field Feb 26 '16 at 15:33
  • 1
    Put a breakpoint after command.CommandText, get the value of the string with the debugger and run the query manually. I bet it will not run as expected either. – Tudor Carean Feb 26 '16 at 15:34
  • @EhsanSajjad - I didn't. It's just part of the character set: Eʜsᴀɴ Sᴀᴊᴊᴀᴅ –  Feb 26 '16 at 15:38
  • @JᴀʏMᴇᴇ did'nt understood, how to write mine in that character set? – Ehsan Sajjad Feb 26 '16 at 15:40
  • @EʜsᴀɴSᴀᴊᴊᴀᴅ - not via the keyboard that's for sure. Just find them on character map or google for a funky tool like 'small caps generator'. –  Feb 26 '16 at 15:41
  • 1
    @JᴀʏMᴇᴇ i copied the version you pasted of my name and it worked :D – Ehsan Sajjad Feb 26 '16 at 15:42

1 Answers1

2

obviously, since you concatenate id with your query string, id is a string in your program. However id datatype in your DB is an int. You will solve your issue (as well as other issues like injection) simple by using parameters:

using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "update address set central_phone_number =@num where id = @ID";
command.Parameters.AddWithValue("@num", NewPhoneNumber);
command.Parameters.AddWithValue("@ID",ID);
....
apomene
  • 14,282
  • 9
  • 46
  • 72
  • 2
    Be careful here. Using AddWithValue can misinterpret the datatypes when using pass through queries like this. It would be better to specify the datatypes here. http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ Regardless a +1 from me. – Sean Lange Feb 26 '16 at 15:36
  • `since you concatenate id with your query string, id is a string in your program` not true. It is fine to concatenate an `int` without an explicit call to `ToString` –  Feb 26 '16 at 15:45