Database update error with SQL Server 2012 and C#

Question

I am trying to update my data in a SQL Server database through C#. I am getting updated. But the problem is the data is updated twice.

For example I have 10 (int) in my balance and if I add another 10, it turns to 30.

Any help would be appreciated.

Here is my code:

protected void LoginClick(object sender, EventArgs e)
{
    DataTable dr = new DataTable();
    string email = txtEmail.Text;

    SqlConnection con = new SqlConnection(Ws.Con);
    con.Open();

    int s = Convert.ToInt32(add.Text);
    SqlCommand cmd = new SqlCommand("Update [Order] set  Balance=Balance+'" + s + "',Card='" + card.Text + "' where email=@email ", con);

    cmd.Parameters.AddWithValue("email", email);

    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    sda.Fill(dt);

    int i = cmd.ExecuteNonQuery();

    con.Close();
}

Very strange code. Why are you using 1 parameter instead of 3 (Balance, Card and email) an why are you using sda.Fill and cmd.ExecuteNonQuery? — Jeroen Heier
– Jeroen Heier, Commented Jan 4, 2018 at 5:01
SqlDataAdapter only works for SELECT queries. Also use all parameters instead query string concatenation if possible. — Tetsuya Yamamoto
– Tetsuya Yamamoto, Commented Jan 4, 2018 at 5:02
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables — marc_s
– marc_s, Commented Jan 4, 2018 at 6:10

sujith karivelil · Accepted Answer · 2018-01-04 05:11:13Z

I would like to rectify few mistakes in your code,

DataTable is not needed to execute the update query, ExecuteNonQuery will do the job
The adapter.Fill and ExecuteNonQuery do the same job here and that's why your updates happening twice
Make use of parameterization while dealing with user inputs to avoid exceptions
For parsing integers use int.TryParse instead for Convert.ToInt32

I think the following code would help you to do the same function in a smarter way:

int currentBalance = 0;
if(int.TryParse(txtAdd.Text, out currentBalance))
{ 
    string querSql = "Update [Order] set  Balance = Balance + @balance," +
                     " Card = @card where email = @email"
    using (SqlConnection dbConn = new SqlConnection("connectionString here"))
    {
        dbConn.Open();
        using (SqlCommand sqlCommand = new SqlCommand(querySql, dbConn))
        {
            sqlCommand.Parameters.Add("@balance", SqlDbType.int).value = currentBalance;
            sqlCommand.Parameters.Add("@card", SqlDbType.VarChar).value = card.Text;
            sqlCommand.Parameters.Add("@email", SqlDbType.VarChar).value = email;
            sqlCommand.ExecuteNonQuery();
        }
    }
}

Please note: YOu are parsing the balance as an integer value, so I assume the column Balance is an integer field in the database, if not make use of corresponding datatype for the parameter @balance also update the parsing technique

Tayyab · Accepted Answer · 2018-01-04 05:30:39Z

As per the documentation:

SqlDataAdapter(SqlCommand)

Initializes a new instance of the SqlDataAdapter class with the specified SqlCommand as the SelectCommand property.

What is going wrong in your code?

Actually you are passing SqlDataAdapter your update query as the Select command. So now when you will use this instance of SqlDataAdapter to Fill your datatable then actually you are executing your Update command. Look at the following code along with comments to see what is going wrong:

        DataTable dr = new DataTable();
        string email = txtEmail.Text;
        SqlConnection con = new SqlConnection(Ws.Con);
        con.Open();

        int s = Convert.ToInt32(add.Text);
        SqlCommand cmd = new SqlCommand("Update [Order] set  Balance=Balance+'" + s + "',Card='" + card.Text + "' where email=@email ", con);

        cmd.Parameters.AddWithValue("email", email);

        SqlDataAdapter sda = new SqlDataAdapter(cmd);//The Select command for SqlDataAdapter
        //is actually now the update command specified by cmd instnace of SqlCommand

        DataTable dt = new DataTable();
        sda.Fill(dt);//here SqlDataAdapter will execute it's Select command which is actually set 
        //to an update statement so your record will be updated

        int i = cmd.ExecuteNonQuery();//and here again the update command is being executed now
        //directly using the SqlCommand cmd instance and thus your record gets updated twice


        con.Close();

Fixed Code:

        DataTable dr = new DataTable();
        string email = txtEmail.Text;
        SqlConnection con = new SqlConnection(Ws.Con);
        con.Open();

        int s = Convert.ToInt32(add.Text);
        SqlCommand cmd = new SqlCommand("Update [Order] set  Balance=Balance+'" + s + "',Card='" + card.Text + "' where email=@email ", con);

        cmd.Parameters.AddWithValue("email", email);

        //Create a new SqlComamnd
        SqlCommand selectCommand = new SqlCommand("Select * from [Order]");

        //Put the newly created instance as SelectCommand for your SqlDataAdapter
        SqlDataAdapter sda = new SqlDataAdapter(selectCommand);

        DataTable dt = new DataTable();
        sda.Fill(dt);

        int i = cmd.ExecuteNonQuery();


        con.Close();

Hope this help and do have a look at the documentation for better understanding of the SqlDataAdapter and DataTable. Thanks.

Collectives™ on Stack Overflow

Database update error with SQL Server 2012 and C#

2 Answers 2

Comments

SqlDataAdapter(SqlCommand)

What is going wrong in your code?

Fixed Code:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

SqlDataAdapter(SqlCommand)

What is going wrong in your code?

Fixed Code:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related