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
– BugFinder, Commented Feb 20, 2017 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
– user7157732, Commented Feb 20, 2017 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
– Steve, Commented Feb 20, 2017 at 10:57

Steve · Accepted Answer · 2017-02-20 11:00:05Z

4

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

answered Feb 20, 2017 at 11:00

Steve

217k22 gold badges242 silver badges296 bronze badges

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

2 Comments

user7157732 Over a year ago

aaah interesting! I suspected the issue was because I used "Date" as the column name; Date being a keyword. Thanks! @Steve

Steve Over a year ago

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

Collectives™ on Stack Overflow

Delete data from Access using SQL Query - C#

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related