Deleting specific record from Access database on button click

Question

I have a string that contains 9 characters, for example LON052012 (from London 23.05.2012. - day.month.year.). I want to delete all records from table in Access where all cities start with first three letters from that string and have those months and year (05.2007.).

This is the function:

private void button2_Click(object sender, EventArgs e)
    {
        if (textBox1.Text != "")
        {
            try
            {
                string sTown, s1, s2;
                sTown = textBox1.Text.Substring(0, 3);
                s1 = textBox1.Text.Substring(3, 2);
                s2 = textBox1.Text.Substring(5);

                string sDelete = "DELETE * FROM Country WHERE Town LIKE @p1 and month(TownDate) = @p2 and year(TownDate) = @p3";
                OleDbCommand komDelete = new OleDbCommand(sDelete, connection);
                komDelete.Parameters.AddWithValue("@p1", sTown + "*");
                komDelete.Parameters.AddWithValue("@p2", s1);
                komDelete.Parameters.AddWithValue("@p3", s2);
                connection.Open();
                komDelete.ExecuteNonQuery();
                MessageBox.Show("Deleted");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                connection.Close();
            }
        }
        else
        {
            MessageBox.Show("TextBox is empty!");
        }
    }

This is how table Country looks like (CountryID, Town, TownDate):

It always says that the record is deleted even if it's not.

Answer 1

For UPDATE, INSERT, and DELETE statements, the return value of ExecuteNonQuery is the number of rows affected by the command.

So, modify your code as:

  var n= komDelete.ExecuteNonQuery();
  if (n >0 )
   {
            MessageBox.Show("Deleted");
   }

Edit

The problems in the query are:

• The parameter data type s1 , s2 which they are string , and they should be passed as integer.
• Using * and it should be % (access use %, see my comment)

so, you have to modify the code to be:

        var p1 = sTown + "%"; // not *
        komDelete.Parameters.AddWithValue("@p1", p1);
        var p2 = int.Parse(s1); //convert to numeric data type
        var p3 = int.Parse(s2);
        komDelete.Parameters.AddWithValue("@p2", p2);
        komDelete.Parameters.AddWithValue("@p3", p3);


      var n= komDelete.ExecuteNonQuery();
     if (n >0 )
     {
            MessageBox.Show("Deleted");
     }

I tested that code with access 2007 and 2007 Office System Driver: Data Connectivity Components and it's working fine and deleted the row.

Answer 2

Try this:

        if (textBox1.Text != "")
    {
        //retrieve data
        string sTown, sMonth, sYear;
        sTown = textBox1.Text.Substring(0, 3);
        sMonth = textBox1.Text.Substring(3, 2);
        sYear = textBox1.Text.Substring(5);

        //validate input
        bool valid_town = new Regex(@"[a-z]", RegexOptions.IgnoreCase).IsMatch(sTown) && new Regex(@"[^0-9]").IsMatch(sTown) && sTown.Length ==3;
        bool valid_month = new Regex(@"[0][1-9]|[1][0-2]").IsMatch(sMonth) && new Regex(@"[^a-z]", RegexOptions.IgnoreCase).IsMatch(sMonth) && sMonth.Length ==2;

        int year = 0;
        bool isNum = int.TryParse(sYear,out year);
        bool valid_year = new Regex(@"[^a-z]", RegexOptions.IgnoreCase).IsMatch(sYear) && sYear.Length == 4 && (isNum ? year > 1980 && year < 2100 : false);



        string newLine = System.Environment.NewLine;

        //if input valid
        if (valid_town && valid_month && valid_year)
        {
            //create sql statements
            string sWhere = String.Format("WHERE Town LIKE {0} and month(TownDate) = {1] and year(TownDate) = {2}", sTown,sMonth,sYear);
            string sCount = String.Format("SELECT COUNT(*) FROM Country {0}", sWhere);
            string sDelete = String.Format("DELETE * FROM Country {0}",sWhere);

            //counts; to be deleted, actually deleted
            int initialCount = 0;
            int deletedCount = 0;

            try
            {
                //create connection//NOTE: Using 'Use' statement will handle opening and closing. I dont know where you created your initial 'connection' object but it could cause you problems by haven a connection that is being used in multiple methods.
                using (OleDbCommand connection = new OleDbCommand(ConnectionString))
                {
                    //get total rows to be affected
                    connection.CommandText = sCount;
                    initialCount = connection.ExecuteNonQuery();

                    //delete rows and get delete count
                    connection.CommandText = sDelete;
                    deletedCount = connection.ExecuteNonQuery();
                    //display message
                    MessageBox.Show(String.Format("Found {0} rows to delete. {1}Deleted {2} rows.", initialCount, newLine, deletedCount));

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                connection.Close();
            }
        }
        {
            //else; input not valid
            MessageBox.Show(String.Format("Failed validation of TextBox:{0}Country:{1}{2}Month{3}{4}Year{5}.", newLine, valid_town.ToString(), newLine, valid_month.ToString(), newLine, valid_year.ToString())); ;

        }
    }
    else
    {
        MessageBox.Show("TextBox is empty!");
    }

you will need to add this using:

using System.Text.RegularExpressions;

I didn't get a chance to create sample project to try it myself...