Delete all rows in Access DB using C#

Question

This wont work but its as close as I can get, I am tring to delete all rows in table Main.

private void button17_Click(object sender, EventArgs e)
{
    String connStr,sql;
    connStr = (@"SomeString");
    try {
        sql = "Delete from Main";
        using (OleDbConnection conn = new OleDbConnection(connStr));
        using(OleDbCommand cmd1 = new OleDbCommand(sql));
        conn.Open();
        cmd1.ExecuteNonQuery();
        conn.Close();
    }
    catch(Exception ex)
    {
        SetCon.Text = SetCon.Text + "Error " + ex + "\n
    }
}

It fails on lines:

conn.Open();
cmd1.ExecuteNonQuery();
conn.Close();

It does not compile with the error "does not exist in this context".

I get Connection property has not been initialized. on 'cmd1.ExecuteNonQuery();' @Janes — Data
– Data, Commented Jun 19, 2017 at 13:28

jAC · Accepted Answer · 2017-06-19 11:43:22Z

2

using (OleDbConnection conn = new OleDbConnection(connStr)){
    OleDbCommand cmd1 = new OleDbCommand(sql);
    conn.Open();
    cmd1.ExecuteNonQuery();
    conn.Close();
}

When you're using using (yeah, English), then you have to define a scope in which the object (here: conn) is valid.
This scope is defined by { ... } in which your following code is placed.

Writing using (OleDbConnection conn = new OleDbConnection(connStr); ends this scope right after ; which means that following code will not have access to that object.

If you want to use using with your OleDbCommand, too, it should look like:

using (OleDbConnection conn = new OleDbConnection(connStr))
{
    conn.Open();
    using (OleDbCommand cmd1 = new OleDbCommand(sql))
    {                   
        cmd1.ExecuteNonQuery();
    }
    conn.Close();
}

If you want to know more about using, see the Microsoft Docs:

As a rule, when you use an IDisposable object, you should declare and instantiate it in a using statement. The using statement calls the Dispose method on the object in the correct way, and (when you use it as shown earlier) it also causes the object itself to go out of scope as soon as Dispose is called. Within the using block, the object is read-only and cannot be modified or reassigned.

In my last example, the scopes would look like this:

Where yellow resembles the scope of conn and orange resembles the scope of of cmd1.

If you want, you can call scope life time of the object, since it's marked as Disposed after.

edited Jun 19, 2017 at 11:43

answered Jun 19, 2017 at 11:28

jAC

5,3256 gold badges43 silver badges57 bronze badges

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

2 Comments

Data Over a year ago

I get Connection property has not been initialized. on 'cmd1.ExecuteNonQuery();' @Janes Thanks for the blog post very comprehensive Ive modified my code using statments on your example and now back where I started

jAC Over a year ago

@Data: You have to pass your connection to the OleDbCommand, either by passing it via constructor (OleDbCommand cmd1 = new OleDbCommand(sql, conn)) or by setting it via property cmd1.Connection = conn;. This should fix it.

Chris Pickford · Accepted Answer · 2017-06-19 11:35:17Z

The C# using statement ensures that the .Dispose() method is called on the encapsulated IDisposable object when existing the proceeding code block.

As you have semi-colons straight after both of your using statements it's inferred that the scope of your OleDbConnection and OleDbCommand are limited to that one line.

By removing the semi-colons and wrapping your operational statements in curved brackets, indicating the using scope, you can get your code to compile. Note that you can "stack" multiple using statements that share the same scope, as follows:

using (OleDbConnection conn = new OleDbConnection(connStr))
using(OleDbCommand cmd1 = new OleDbCommand(sql))
{
    conn.Open();
    cmd1.ExecuteNonQuery();
    conn.Close();
}

Collectives™ on Stack Overflow

Delete all rows in Access DB using C#

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related