Check for existing value when adding to Access

Question

I have an interface in which one of my functions include deleting a Serial Number from Access. If the Serial Number does exist then it deletes everything as it is supposed to with a confirmation message. The problem is, I can type in a Serial Number that does not exist and it acts like it is deleting it anyway. How do I check to see if the value exists when clicking the delete button, so I can then throw a notification to the user ?

private void btnDelete_Click(object sender, EventArgs e)
    {
        if (!string.IsNullOrEmpty(txtSerial.Text))

            try
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand();
                command.Connection = connection;
                string deleteEntry = "delete from Inventory where SerialNumber='" + txtSerial.Text + "' ";
                DialogResult result = MessageBox.Show("ARE YOU SURE YOU WANT TO DELETE SERIAL NUMBER = " + txtSerial.Text + " ? ", "LAST CHANCE !", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);

                if (result.Equals(DialogResult.OK))
                {
                    command.CommandText = deleteEntry;
                    command.ExecuteNonQuery();
                    MessageBox.Show("Data Has Been Deleted".PadLeft(28));
                }

                if (dataGridFB.DataSource != null)
                {
                    dataGridFB.DataSource = null;
                    txtSerial.Clear();
                    comboSerial.Text = string.Empty;
                    comboPart.Text = string.Empty;
                    comboRO.Text = string.Empty;
                    comboLocation.Text = string.Empty;
                    comboSerial.Items.Clear();
                    comboPart.Items.Clear();
                    comboRO.Items.Clear();
                    comboLocation.Items.Clear();
                }
                else
                {
                    dataGridFB.Rows.Clear();

                }

                ItemsLoad();
                connection.Close(); // CLOSE HERE OR YOU CANNOT ENTER RECORDS SIMULTANEOUSLY

            }
            catch (OleDbException ex)
            {
                MessageBox.Show(ex.Message);
                connection.Close();
            }
    }

Does it matter? `int rows = command.ExecuteNonQuery();` will tell you how many rows were affected. Also, use SQL Parameters rather than string concat for SQL. — Ňɏssa Pøngjǣrdenlarp, Mar 05 '16 at 18:07

score 3 · Accepted Answer · answered Mar 05 '16 at 18:06

3

The OleDbCommand.ExecuteNonQuery returns the number of rows affected by the command. If there is no row to delete the return value will be zero. So it is easy to discover this situation

int rowsDeleted = command.ExecuteNonQuery();
if(rowsDeleted == 0)
    MessageBox.Show("No Serial number found to delete");
else
    ....

Said that, remember that string concatenation to build command text is considered bad practice and you should never use it. A parameterized query is the only correct way to create commands that requires inputs from the user.....

string deleteEntry = "delete from Inventory where SerialNumber=@num"
command.CommandText = deleteEntry;
command.Parameters.Add("@num", OleDbType.VarWChar).Value =  txtSerial.Text;
int deletedRows = command.ExecuteNonQuery();

answered Mar 05 '16 at 18:06

Steve

213,761
22
232
286

2

I knew you'd get to it ;) – Ňɏssa Pøngjǣrdenlarp Mar 05 '16 at 18:10
So parameterized should be used in all functions - UPDATE, DELETE, INSERT, etc....? – CamlCase Mar 05 '16 at 18:38
Yes, whenever you take an input from your user and use it as value for some WHERE clause, or as value for inserts or updates. These inputs should never be concatenated together with the rest of the sql command. This question explains [the Sql Injection hack](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work), – Steve Mar 05 '16 at 19:32
Side note: Access is more difficult to exploit for the simple reason that you cannot send two commands together, but nevertheless, it is still possible to hack it so start to follow the correct way regardless of your database – Steve Mar 05 '16 at 19:35

score 0 · Answer 2 · answered Mar 05 '16 at 18:08

The OleDbCommand.ExecuteNonQuery method you're using returns an Int32 which represents the number of rows affected. You could use this to find out whether a row was deleted or not.

if (command.ExecuteNonQuery() > 0) 
{
    // Row was deleted
}
else
{
    // Row was not deleted
}

score 0 · Answer 3 · answered Mar 05 '16 at 18:16

You can do a Console.WriteLine("Nothing to delete") in an if based statement, but if it is compiled and you can not see the console, try a MessageBox.Show("Nothing to delete") function after an if based statement.

        if (command.ExecuteNonQuery == 0)
    {
        Console.WriteLine("0-removed")
    }
else
    {
        Console.WriteLine("not deleted")

As an example...

    private void timer1_Tick(object sender, EventArgs e)
        if (command.ExecuteNonQuery == 0)
    {
        Console.WriteLine("0-removed")
    }
else
    {
        Console.WriteLine("not deleted")

Check for existing value when adding to Access

3 Answers3