CODEWITHSUNDEEP
c#sql-update
user2201221
user2201221

Reputation: 29

SQL Update not working, not sure why

I have the following code that just will not update a record. The data in question is being displayed in a DataGridView so I know that I am connecting to the database with no problem. If anyone sees anything wrong with this can they please let me know, I've been eyeballing it for quite awhile and I can't see anything that doesn't look right. There is no exception thrown it just, does nothing. Thanks in advance.

    string strSQLConnectionString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\dbase.mdf;Integrated Security=True";
        string strUpdateCommand = "UPDATE table1 SET Active = @Active WHERE Order = @Order";
        SqlConnection connection = new SqlConnection(strSQLConnectionString);
        SqlCommand updateCommand = new SqlCommand(strUpdateCommand, connection);
                    connection.Open();
                    updateCommand.Parameters.AddWithValue("Order", "1");
                    updateCommand.Parameters.AddWithValue("@Active", "True");
                    updateCommand.ExecuteNonQuery();
                    connection.Close();
                    updateCommand.Parameters.Clear();

In the real code there's a try/catch starting with the connection.open and ending with the parameters.clear. Thanks again for any help on this. erik

EDIT @Rahul Singh Thanks for the reply, and the link to the blog. The change you suggested with adding the missing '@' did not fix the problem. Instead, what I am now getting is a 'connection is not open, connection must be open' exception on the executenonquery line. I took your suggestion about the using blocks (thanks!) and the revised code is

                    using (SqlConnection connection = new SqlConnection(strSQLConnectionString))
                    {
                        using (SqlCommand updateCommand = new SqlCommand(strUpdateCommand, connection))
                        {
                            updateCommand.Parameters.AddWithValue("@Order", "1");
                            updateCommand.Parameters.AddWithValue("@Active", "True");
                            updateCommand.ExecuteNonQuery();
                        }
                    }

What is odd is that if I explicitly say 'connection.open()' and 'connection.close();' then I don't get this error but, again, nothing gets done. Thanks for any further help on this, I'm about to rub a hole in my scalp from all the head-scratching on this one.

Upvotes: 0

Views: 89

Answers (1)

Rahul Singh
Rahul Singh

Reputation: 21825

Because you are missing @ in following line:-

updateCommand.Parameters.AddWithValue("@Order", "1");

The parameters should exactly match with the query. Also, please read this blog on "Can we stop using AddWithValue".

Also, you should code with a using block to automatically dispose expensive resources. Something like this:-

using(SqlConnection connection = new SqlConnection(strSQLConnectionString)
{
    using(SqlCommand updateCommand = new SqlCommand(strUpdateCommand, connection)
    {
        //Your code goes here.
    }
}

Upvotes: 2

Related Questions