Question Delete Clause with 'WHERE <>' not working

[centaur]

I joined C# Developer today and this is my first post. Hello to all!
I am creating a database with MS Access as my back-end and C# Win Form using VS 2017 as my front-end. I am having issues with deleting rows in my table using the DELETE clause.

Background info:
A user enters a search term in a text box named Srch_Txtbx and clicks search button where my DataGridView list is filtered based on the user's search text. So any row in any of the columns within the DataGridView that has the user's search term is kept while all others are filtered out. I then delete the rows not equal to the user's text entry in a separate temporary table (copy of original). BTW, I am using a copy (temporary table) of the original table because I am deleting rows based on text box and combobox selections. Using the temp table allows me to filter my comboboxes based on the other comboboxes and text box selections but the problem I'm having is that only the rows that are like the text entry are being deleted.

As a simple example:
User enters search term: BIT-1000
My DataGridView filters correctly showing only one row corresponding to the TAG_NAME column. My temporary table should be exactly the same showing only one row containing the TAG_NAME BIT-1000. Instead it deletes all rows where the TAG_NAME starts with BIT but keeps the BIT-1000 row and all other rows where the TAG_NAME doesn't start with BIT. See my code below. I'm using the <> operator but is not working correctly. I also tried using NOT LIKE instead of <> and that gives me the same result as <>. What am I doing wrong?

Here is my code:

using (OleDbCommand cmd = dbConn.CreateCommand())
{
dbConn.Open();
cmd.CommandText = "DELETE FROM [Temp_Tbl] " +
"WHERE [IO_CARD] <> '" + Srch_Txtbx.Text + "'" +
" AND [PLC_PANEL] <> '" + Srch_Txtbx.Text + "'" +
" AND [SLOT_NO] <> '" + Srch_Txtbx.Text + "'" +
" AND [CHANNEL_NO] <> '" + Srch_Txtbx.Text + "'" +
" AND [TAG_NAME] <> '" + Srch_Txtbx.Text + "'";


cmd.Connection = dbConn;
cmd.ExecuteNonQuery();
dbConn.Close();
}

 

[jmcilhinney]

Your SQL code is telling the database to delete records where none of the specified columns is equal to the specified value. If the specified value is "BIT-1000" then that will result in all records where no column is equal to "BIT-1000" being deleted. You say that a record where a column equals "BIT-1000" is not deleted and any that starts with "BIT" but is not equal to "BIT-1000" is deleted. That's exactly what you should expect to happen with that SQL code. I suspect that the SQL doesn't actually represent what you want done. I suspect that what you actually want is '=' rather than '<>' and OR rather than AND. I'll rewrite your code the way I think it should be and note the way I write the SQL code for readability in VS and also how I format the code for readability in the forum:

var sql = "DELETE FROM [Temp_Tbl]
    WHERE [IO_CARD] = @IO_CARD
    OR [PLC_PANEL] = @PLC_PANEL
    OR [SLOT_NO] = @SLOT_NO
    OR [CHANNEL_NO] = @CHANNEL_NO
    OR [TAG_NAME] = @TAG_NAME";

using (OleDbCommand cmd = dbConn.CreateCommand(sql, dbConn))
{
    cmd.Parameters.AddWithValue("@IO_CARD", Srch_Txtbx.Text)
    cmd.Parameters.AddWithValue("@PLC_PANEL", Srch_Txtbx.Text)
    cmd.Parameters.AddWithValue("@SLOT_NO", Srch_Txtbx.Text)
    cmd.Parameters.AddWithValue("@CHANNEL_NO", Srch_Txtbx.Text)
    cmd.Parameters.AddWithValue("@TAG_NAME", Srch_Txtbx.Text)

    dbConn.Open();
    cmd.ExecuteNonQuery();
    dbConn.Close();
}

Note the use of formatting tags to make the code snippet more readable to us here. Within the code, most importantly, note the change to the SQL code to what I think you're actually trying to achieve, i.e. delete any row where any column matches the specified value. Note also the use of a multiline string literal to make the SQL code more readable. Finally, notice the use of parameters to insert values into SQL code. You should pretty much NEVER use string concatenation to do that because there are numerous things that can go wrong. Some can cause app crashes and some can cause data corruption or loss. Unfortunately, Access doesn't support multiple uses of the same parameter so this example needs five parameters that all have the same value. There's a post about ADO.NET parameters on my blog, for which you can find a link in my signature below.

 

[centaur]

Thanks for your reply jmcilhinney. I will try your solution but I actually got my code to work sort of. The issue I was having is that I had too many continuing AND lines. I believe the limit is 23 or something like that. The code I showed in my previous post didn't show the entire code so that was one fix. I also change the WHERE statement to:

"WHERE NOT [IO_CARD] = '" + Srch_Txtbx.Text + "' AND NOT [PLC_PANEL] = '" + Srch_Txtbx.Text + "'" + ....etc.

This actually works except when the value of Srch_Txtbx.Text is empty or has no value. When it's empty, that row is then kept for some reason which is not what I want. I wouldn't think the row would be kept if the entire row doesn't have the value of Srch_Txtbx.Text but if one of the columns cells is empty, it is kept. Again, I will try your solution. I really appreciate your feedback.

 

[centaur]

I fixed the majority of my issues with the Delete statement but I am having some weird results with one of my delete statements. In my code below, all of my DELETE statements execute as I want them to except for the last one that starts with --> if (cntrl_selection == "Instr_Type_Cbx"). Example, if the value for Instr_Type_Cbx.Text is FLOW XMTR, all rows within the Temp_Tbl table that do not have the text FLOW XMTR in column INSTRUMENT_TYPE should be deleted but that's not what's happening. I am stumped as to why it's doing this b/c all of the 4 if blocks below are identical except for the table column name and the combobox name, but ONLY the first 3 if blocks work. The last one filtering column INSTRUMENT_TYPE does not. I've tried the following changes in my delete statement to see if that helped but it didn't:

1) cmd.CommandText = "DELETE FROM [Temp_Tbl] WHERE [INSTRUMENT_TYPE] NOT LIKE '" + Instr_Type_Cbx.Text + "'"; // no difference from what I get in the last if block below
2) cmd.CommandText = "DELETE FROM [Temp_Tbl] WHERE [INSTRUMENT_TYPE] <> '" + Instr_Type_Cbx.Text + "'"; // no difference from what I get in the last if block below

Does anyone have any idea as to why this would be occurring? It just doesn't make sense. Thanks for your help in advance.


My Current Code:
--------------------

// this if block works correctly
if (cntrl_selection == "IO_Card_Cbx")
{
using (OleDbCommand cmd = dbConn.CreateCommand())
{
dbConn.Open();
cmd.CommandText = "DELETE FROM [Temp_Tbl] WHERE NOT [IO_CARD] = '" + IO_Card_Cbx.Text + "'";
cmd.ExecuteNonQuery();
dbConn.Close();
}
}



// this if block works correctly
if (cntrl_selection == "PLC_Pnl_Cbx")
{
using (OleDbCommand cmd = dbConn.CreateCommand())
{
dbConn.Open();
cmd.CommandText = "DELETE FROM [Temp_Tbl] WHERE NOT [PLC_PANEL] = '" + PLC_Pnl_Cbx.Text + "'";
cmd.ExecuteNonQuery();
dbConn.Close();
}
}



// this if block works correctly
if (cntrl_selection == "IO_Type_Cbx")
{
using (OleDbCommand cmd = dbConn.CreateCommand())
{
dbConn.Open();
cmd.CommandText = "DELETE FROM [Temp_Tbl] WHERE NOT [IO_TYPE] = '" + IO_Type_Cbx.Text + "'";
cmd.ExecuteNonQuery();
dbConn.Close();
}
}



// this if block is deleting the wrong rows and the rows that haven't been
// deleted, some of the info in the column rows is also deleted even though I
// have nothing in my WHERE about those columns.
if (cntrl_selection == "Instr_Type_Cbx")
{
using (OleDbCommand cmd = dbConn.CreateCommand())
{
dbConn.Open();
cmd.CommandText = "DELETE FROM [Temp_Tbl] WHERE NOT [INSTRUMENT_TYPE] = '" + Instr_Type_Cbx.Text + "'"; // for this example, Instr_Type_Cbx.Text = FLOW XMTR
cmd.ExecuteNonQuery();
dbConn.Close();
}
}

 

[centaur]

I finally figured out my mistake. In my delete statements I was not handling the case where the column's cell was null. The corrected DELETE statement is as follows:


cmd.CommandText = "DELETE FROM [Temp_Tbl] WHERE (NOT [INSTRUMENT_TYPE] = '" + Instr_Type_Cbx.Text + "' OR [INSTRUMENT_TYPE] IS NULL)";


The reason the other 3 DELETE statements worked was b/c those all had a value (none were null). It seems to me a little odd that I have to add the OR [INSTRUMENT_TYPE] IS NULL to the WHERE criteria in my DELETE statments in order for it to work correctly. I don't have to add IS NOT NULL to the WHERE criteria in a SELECT statement when I'm filtering a field that is equal to my combobox value for it to ignore null cells but I do when it's a DELETE statement. Is there a reason for this or am I doing something wrong?

 

[jmcilhinney]

If a field contains NULL then it contains no value so a comparison to a value, whether for equality or inequality, cannot be TRUE. If X is NULL then neither 'X = @SomeValue' nor 'X <> @SomeValue' is TRUE. If you're trying to match records to a value then you will inherently exclude NULLs but if you're trying to match records to NOT a value then you will exclude NULLs as well. If you have three records where column X contains TRUE, FALSE and NULL respectively, a filter of 'X = TRUE' will only match the first record and a filter of 'X <> TRUE' will only match the second record. No comparison to a value, whether positive or negative, will match the third record.

Obviously a particular database could implement things differently but what I have described seems to be the common way to do it.

 

[centaur]

Thanks for the explanation. I really appreciate your input!