CODEWITHSUNDEEP
c#sql-server-express
Michael A
Michael A

Reputation: 5840

Deleting row in a table not workng

I am trying to delete a row in my users_stocks table.

I use this code:

public bool removeStock(string user_name,string stock_symbol)
{
    user_name = user_name.Trim();
    stock_symbol = stock_symbol.Trim();
    string statement = "DELETE FROM " + "users_stocks" + " WHERE user_name = '" + user_name + "'" + " AND " + "stock_symbol = " + "'" + stock_symbol + "'" ;
    SqlCommand cmdnon = new SqlCommand(statement, connection);
    try
    {
        connection.Open();
        int num = cmdnon.ExecuteNonQuery();
         connection.Close();
        return true;
    }
    catch (SqlException ex)
    {
        Console.WriteLine(ex.ToString());
        connection.Close();
        return false;
    }
}

There is a row with this data, but the query wont erase it.

What am i missing?

Upvotes: 0

Views: 155

Answers (3)

M P
M P

Reputation: 2337

As Luis Quijada mentioned above use parameters, they are much safer. In the code below just change the YOUR_CONNECTION_STRING value and the SqlDbType to the ones matching in your DB.

    public bool removeStock(string user_name, string stock_symbol)
    {
        using(SqlConnection connection = new SqlConnection("YOUR_CONNECTION_STRING"))
        {
            using(SqlCommand command = new SqlCommand())
            {
                try
                {
                    command.Connection = connection;
                    command.CommandText = "DELETE FROM user_stocks WHERE user_name=@USERNAME AND stock_symbol=@STOCKSYMBOL";
                    command.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = user_name.Trim();
                    command.Parameters.Add("@STOCKSYMBOL", SqlDbType.VarChar).Value = stock_symbol.Trim();
                    connection.Open();

                    int i = command.ExecuteNonQuery();

                    if (i == 0)
                        return false;

                    return true;
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                    connection.Close();
                    return false;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }

Upvotes: 3

Steve
Steve

Reputation: 216313

Use parametrized query to avoid Sql Injection Attacks and quoting problems Not to mention that a parametrized query could be stored by the optimization engine of SqlServer and reused more quickly. An hand made query will be reevaluated every time you send to the database- 

public bool removeStock(string user_name,string stock_symbol) 
{ 
    user_name = user_name.Trim(); 
    stock_symbol = stock_symbol.Trim(); 
    string statement = "DELETE FROM users_stocks " + 
                        "WHERE user_name = @name AND stock_symbol = @stock" ; 
    SqlCommand cmdnon = new SqlCommand(statement, connection); 
    try 
    { 
        cmdnon.Parameters.AddWithValue("@name", user_name);
        cmdnon.Parameters.AddWithValue("@stock", stock_symbol);
        connection.Open(); 
        int num = cmdnon.ExecuteNonQuery(); 
        connection.Close(); 
        return true; 
    } 
    catch (SqlException ex) 
    { 
        Console.WriteLine(ex.ToString()); 
        connection.Close(); 
        return false; 
    } 
}

Upvotes: 4

user1542538
user1542538

Reputation:

Try this code : 

  public bool removeStock(string user_name,string stock_symbol)
  {
      user_name = user_name.Trim();
      stock_symbol = stock_symbol.Trim();
      string statement = "DELETE FROM users_stocks 
                          WHERE user_name = '" + user_name + "' 
                          AND stock_symbol = '" + stock_symbol + "'" ;
      SqlCommand cmdnon = new SqlCommand(statement, connection);
      try
      {
          connection.Open();
          int num = cmdnon.ExecuteNonQuery();
          connection.Close();
          return true;
      }
      catch (SqlException ex)
      {
          Console.WriteLine(ex.ToString());
          connection.Close();
          return false;
      }
  }

Change in query

Upvotes: 1

Related Questions