Delete data from Access using SQL Query - C#

Question

\I have three columns in an access database table (DATA) as shown below

I just want to delete some rows based of two conditions in the WHERE clause in the SQL query ; for ex, delete row when NAME = "A" and Date = "1/1/2017"

I used DELETE from DATA Where Name='A' and Date='1/1/2017' This gives "type mismatch error"!

Here is the code in C#:

 using (OleDbConnection thisConnection = new OleDbConnection(connectionname))
 {
    string deletequery = " DELETE FROM DATA WHERE [Name] = 'A' And [Date] = '1/1/2017';

    OleDbCommand myAccessCommandDelete = new OleDbCommand(deletequery, thisConnection);
    thisConnection.Open();
    myAccessCommandDelete.ExecuteNonQuery();
    thisConnection.Close();
}

this isnt a c# issue! it doesnt like your format of date. please remove the c# tag — BugFinder, Feb 20 '17 at 10:51
I added the C# code. I was first trying it in Access and would have used the same query in C#. @BugFinder — user7157732, Feb 20 '17 at 10:54
That's not the correct way to pass values if the field (DATA) is a field of type DateTime. Is Data a DateTime field in access? — Steve, Feb 20 '17 at 10:57
Guessing, but you are passing date as a string, not a date. Long time since I used access, but I think dates are wrapped in # symbols, so try to change [Date] = '1/1/2017' to [Date] = #1/1/2017# If database is a DateTime not a Date, then you will need to factor in times as well.. — Matt, Feb 20 '17 at 10:59

score 4 · Accepted Answer · answered Feb 20 '17 at 11:00

The best way to pass values to a database engine that will be used in a query is through the parameters collection specifying exactly the type of the parameter

using (OleDbConnection thisConnection = new OleDbConnection(connectionname))
{
    string deletequery = @"DELETE FROM DATA WHERE [Name] = @name And 
                         [Date] = @date";
    OleDbCommand myAccessCommandDelete = new OleDbCommand(deletequery, thisConnection);
    thisConnection.Open();
    myAccessCommandDelete.Parameters.Add("@name", OleDbType.VarWChar).Value = "A";
    myAccessCommandDelete.Parameters.Add("@date", OleDbType.Date).Value = new DateTime(2017,1,1);
    myAccessCommandDelete.ExecuteNonQuery();
    // not needed -> thisConnection.Close();
}

In this way you don't leave space to interpretation (conversion from string to date) of your values but you tell exactly to your db engine what your value is. And of course if you specify the correct type you can't have a Type Mismatch error

aaah interesting! I suspected the issue was because I used "Date" as the column name; Date being a keyword. Thanks! @Steve — user7157732, Feb 20 '17 at 11:17
Using parameters is the way to go for every thing you need to pass as value for your columns (WHERE, INSERT,UPDATE,DELETE) Also, albeit more difficult to exploit in MS-Access 'thanks' to the missing batch update functionality, it is the correct way to avoid [Sql Injection mess](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) — Steve, Feb 20 '17 at 11:31

Delete data from Access using SQL Query - C#

1 Answers1