Update query in SQL Server isn't working

Question

I have a class method for updating database.. but it isn't working and I can't find out what is wrong..

There is no error in the try catch block.. in database type of id is int and type of catagory is nvarchar(50).

I also tried converting id from int to string but doesn't work and no error..

Database column name and variable name is same. Connection string is saved in web.config file which worked for inserting data.

public string update(int id, string catagory)
{
        //creating database connection
        SqlConnection objConnection = new SqlConnection(strConnection);
        objConnection.Open();
        string error = "";

        try
        {
            //firing command
            string strCommand = "UPDATE Data SET catagory = '" + catagory + "' WHERE (id = '" + id + "')";
            SqlCommand objCommand = new SqlCommand(strCommand, objConnection);
            objCommand.ExecuteNonQuery();
        }
        catch(System.Data.SqlClient.SqlException ex)
        {
            error = ex.ToString();
        }

        //closing database connection
        objConnection.Close();

        return error;
    }

Do not concatenate strings into SQL commands, this makes your code prone to SQL injection attacks and it is less efficient. Use parameterized queries instead. See also msdn.microsoft.com/en-us/library/ms182310.aspx and msdn.microsoft.com/en-us/library/ms254953(v=vs.110).aspx — Kris Vandermotten
– Kris Vandermotten, Commented Feb 23, 2014 at 18:09

Soner Gönül · Accepted Answer · 2014-02-23 18:00:36Z

If id column is int, you don't need to use single quotes with it. Just use it like WHERE (id = " + id + "). Single quotes is for character column types.

But don't use this way

You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

string strCommand = "UPDATE Data SET catagory = @catagory WHERE id = @id";
SqlCommand objCommand = new SqlCommand(strCommand, objConnection);
objCommand.Parameters.AddWithValue("@catagory", catagory);
objCommand.Parameters.AddWithValue("@id", id);
objCommand.ExecuteNonQuery();

Also DATA could be reserved keyword in future releases of SQL Server. You might need to use it in square brackets like [DATA] in the future.

Use using statement to dispose your SqlConnection and SqlCommand by the way.

Sudhakar Tillapudi · Accepted Answer · 2014-02-23 18:22:35Z

Problem : DATA could be Reserved word in future versions of SqlServer.

From MSDN :

Data could be reserved in future releases of SQL Server as new features are implemented.

Solution : enclose the Reserved words in square brackets as [data] Try This:

string strCommand = "UPDATE [Data] SET catagory = '" + catagory + "' WHERE (id = " + id + ")";

Suggestion 1: you don't need to enclose the Integer parameters within single quotes.

Suggestion 2 : your Update query is open to sqlinjection attacks please use Parameterised queries to avoid them.

Suggestion 3: you could use the return value of the ExecuteNonQuery() method to identify the Status of the UPDATE command.

Complete Code: using Parameterised Queries

    try
    {
        //firing command

        string strCommand = "UPDATE [Data] SET catagory = @catagory WHERE (id =@id)";
        SqlCommand objCommand = new SqlCommand(strCommand, objConnection);
        objCommand.Parameters.AddWithValue("@catagory",catagory);
        objCommand.Parameters.AddWithValue("@id",id);
        int status;
        status = objCommand.ExecuteNonQuery();
        if(status>0)
           MessageBox.Show("Data Updated Successfully!");
        else
           MessageBox.Show("Update Failed!");

    }

@SudhakarTillapudi DATA is not a reserved keyword right now. But it can be in the future. This is not the main reason why OP's code doesn't work.
@SonerGönül: yes dear i just now found that information :) Thanks for your remainder.
@SonerGönül i tried like you but still after refreshing database it isnt changed..
@SudhakarTillapudi You get any exception or error message? Are you sure your connection string is right? Give us more details.
@WahidMasud: so did you try with my latest code using status? try with altest code and see what wether it returns success or failure message?

Awtszs · Accepted Answer · 2014-10-17 01:51:39Z

Always use parameterized queries, for anti injection.

The first parameter specifies the SQL statement.
The second parameter is the SqlConnection.
The third parameter is the SqlTransaction. here is a reference

And using statement automatically dispose and close your sql connection and sql command

     public string update(int id, string catagory)
            {
                    //creating database connection
                   using( SqlConnection objConnection = new SqlConnection(strConnection))
            {
                    objConnection.Open();
                    string error = "";

                    try
                    {
                        string strCommand = "UPDATE Data SET catagory = @catagory WHERE id = @id ";
                        using( SqlCommand objCommand = new SqlCommand(strCommand, objConnection))
                       {
                        objCommand.Parameters.AddWithValue("@catagory",catagory);
                        objCommand.Parameters.AddWithValue("@id",id);
                        objCommand.ExecuteNonQuery();
                       }
                    }
                    catch(System.Data.SqlClient.SqlException ex)
                    {
                        error = ex.ToString();
                    }

                    return error;

                }
            }

Collectives™ on Stack Overflow

Update query in SQL Server isn't working

3 Answers 3

But don't use this way

Comments

10 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

But don't use this way

Comments

10 Comments

Comments

Related