C# MS SQL Update statement not updating database

Question

I have a local MS SQL Database, and I want to update one of it's bit field. I have the following code:

static void UpgradeVevo(string nev)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlCommand command = new SqlCommand("UPDATE Vevok SET Torzsvendeg=True Where Nev=" + nev, connection);
            command.ExecuteNonQuery();
        }
        Console.WriteLine(nev+" mostmár törzsvendég");
    }

Torzsvendeg is a bit datatype(I have tried to set its value to 1 too), and Nev is varchar.

The connectionstring should be fine, since I have tried Select in another method and it works fine. The above code throws no exceptions, but the table does not get updated.

I have tried to find an answer for quite some time, with no success :/. Thank you for your help in advance!

Does a column exist where the database field Nev matches the variable nev? Also, use parameterised queries, your statement is vulnerable to SQL injection. — Darren
– Darren, Commented May 12, 2017 at 21:20
Thank you for your tip, I will, I just want to figure this out first. The Vevok table contains one record so far, where the Nev is vevo1. The variable nev is vevo1. — Hevesi
– Hevesi, Commented May 12, 2017 at 21:31

Rahul · Accepted Answer · 2017-05-12 21:32:45Z

1

True should be in a single quote since it's a string literal like

UPDATE Vevok SET Torzsvendeg='True'

Well brother, you are messed up with quotes. Your query should look like

"UPDATE Vevok SET Torzsvendeg = 1 Where Nev = '" + nev + "'"

Again, use parametarized query and not this concatenated one to avoid SQL Injection

edited May 12, 2017 at 21:32

answered May 12, 2017 at 21:21

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hevesi Over a year ago

There is only one record in my table so far(as I'm trying to figure this out first), where the Nev is vevo1. I pass that paramater, but now it gives an SqlException, "Invalid column name 'vevo1'.". I don't really get what I'm doing wrong still :D

Rahul Over a year ago

@Hevesi, umm .. mind checking the update in answer again

Hevesi Over a year ago

Yes, I know about SQL injection, and I will definetely use parametarized query once I figure this out :D Although, there are no exceptions this time, but its still not updated in the database...

Hevesi Over a year ago

As I have mentioned I have a local db, and for some reason Visual Studio creates a copy of my db to bin/debug, and when I'm updating the db, its updating that copy of my db, not the original db.... The question now is how can I update the original db instead...

horotab · Accepted Answer · 2017-05-12 22:34:37Z

0

If the column is a boolean (bit in sql server) then you will have to write

Torzsvendeg=1

instead of

Torzsvendeg='True' or Torzsvendeg=True

Edit:

Please try this:

static void UpgradeVevo(string nev)
{
    var connection = new SqlConnection(connectionString))
    connection.Open(); // try doing this without a using
    SqlCommand command = new SqlCommand("UPDATE Vevok SET Torzsvendeg=@enabled Where Nev=@nev", connection);
    command.Parameters.AddWithValue(@"enabled", 1);
    command.Parameters.AddWithValue(@"nev", "vevo123");
    command.ExecuteNonQuery();
    command.Parameters.Clear(); // always clear after executed
    // close connection when you shut down your application
    connection.Close();
    connection.Dispose();

    Console.WriteLine(nev+" mostmár törzsvendég");
}

edited May 12, 2017 at 22:34

answered May 12, 2017 at 21:52

horotab

6833 silver badges22 bronze badges

7 Comments

Hevesi Over a year ago

I have also tried that with no success, the database is still not updated.

horotab Over a year ago

Does the column has to be varchar? I usually use nvarchar(256) or nvarchar(512) for stuff like mails and ntext for text. Can you maybe try that?

Hevesi Over a year ago

Tried, and it still shows False. I have no idea what am i missing(Im not forgetting to refresh the table)...

Hevesi Over a year ago

Im using a local database, and I have found that VS is creating a copy of my db to bin/debug and when I'm updating the table it's updating the copy of my db and not the original. I have checked, and the copied db's field in bin/debug is True. Now to figure out how to update the original too/instead...

horotab Over a year ago

Why you dont work with that in you bin folder instead? When you will switch to a database on a server this can not happen anyway.

|

Collectives™ on Stack Overflow

C# MS SQL Update statement not updating database

2 Answers 2

4 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related