Adding and Deleting data from sql database via asp.net

Question

basically this is my inserting function which inserts string into sql table

[System.Web.Services.WebMethod]     
        public static string InsertData(string ID)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["SimpleDB"].ToString();          
            using (SqlConnection con = new SqlConnection(connectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("Insert into Book (Name) values(@Name)", con))
                    {
                        con.Open();
                        cmd.Parameters.AddWithValue("@Name", ID);
                        cmd.ExecuteNonQuery();
                        con.Close();
                        return "True";
                    }
                }
        }

Now i want to remove a row from this table if exist and i tried this but i seems to get error when executing the query.

  [System.Web.Services.WebMethod]
        public static string DeleteData(string ID)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["SimpleDB"].ToString();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand
                    (
                    "Delete from Book Where Name = "+ID.ToString()
                    , con))
                {
                    con.Open();                                     
                    cmd.ExecuteNonQuery();                   
                    con.Close();
                    return "True";
                }
            }


        }

Can you explain why you ditched the correct way to query a database using a parameterized query and use instead a very wrong string concatenation? — Steve
– Steve, Commented Jul 2, 2014 at 19:43
As an aside, I would highly recommend not generating SQL queries through string concatenation, because it opens you up for all kinds of SQL injection security problems later. — Tripp Kinetics
– Tripp Kinetics, Commented Jul 2, 2014 at 19:43
Missing quotes around value in Where Name = "+ID.ToString() ? Also why not use a parameterized query there also? In fact, why not just use Entity Framework? — mclaassen
– mclaassen, Commented Jul 2, 2014 at 19:44

Community · Accepted Answer · 2017-05-23 10:26:14Z

2

Notice how the ID variable passed to your method is a string?
This means that the Name field used in the WHERE clause is expecting a string to find the row to delete. Strings, when used as values in a Sql WHERE are passed enclosed in single quotes.

For example, if you write your query directly in Sql Server Management Studio:

 DELETE FROM Book WHERE Name = 'xyz'

Without the quotes you get an error and this is probably your problem now.

The solution is just to follow the same steps used when you inserted the new row. A parameterized query and you don't need to worry about quoting your strings or worse about Sql Injections

[System.Web.Services.WebMethod]
public static string DeleteData(string ID)
{
        string connectionString = ConfigurationManager.ConnectionStrings["SimpleDB"].ToString();
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand(@"Delete from Book 
                                                     Where Name = @ID" , con))
            {
                con.Open();                                     
                cmd.Parameters.AddWithValue("@ID", ID);
                cmd.ExecuteNonQuery();                   
                con.Close();
                return "True";
            }
        }
  }

The delete operation is safe, because if the record doesn't exist, then the WHERE clause cannot find any row to delete. However, if you need for other purposes a check for the existance or not of a record you could write something like this

public static bool Exists(string ID)
{
    string connectionString = ConfigurationManager.ConnectionStrings["SimpleDB"].ToString();
    using (SqlConnection con = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(@"
                      IF EXISTS(SELECT 1 FROM Book Where Name = @ID)
                      SELECT 1 ELSE SELECT 0" , con))
    {
         con.Open();                                     
         cmd.Parameters.AddWithValue("@ID", ID);
         int result = Convert.ToInt32(cmd.ExecuteScalar()); 
         return (result == 1);
    }
}

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Jul 2, 2014 at 19:47

Steve

217k22 gold badges242 silver badges296 bronze badges

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

6 Comments

Arch_interpreter Over a year ago

thanks a lot i didnt know this is the idea of the cmd.parameters.addwithvalue query now i realized it

Arch_interpreter Over a year ago

btw id like to know how to check if a record exists for example if ID exists as an already created row

Steve Over a year ago

Well, the delete operation is safe, because, if the record doesn't exist then no record with the ID specified will be found. If you want to know if a record exists then I will update the answer

Arch_interpreter Over a year ago

so in javascript if i call it like if (PageMethods.Exists(ID) == true){ do something} it will check if ID row exists and then do something if it exists

Steve Over a year ago

Well, I am not so confident about javascript to give you an answer with absolute certainty. I think you should post a new question with javascript in the TAGS list to attract people more expert than me in that language.

|

Collectives™ on Stack Overflow

Adding and Deleting data from sql database via asp.net

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related