CODEWITHSUNDEEP
c#ms-access
Data
Data

Reputation: 113

Delete all rows in Access DB using C#

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".

Upvotes: 0

Views: 241

Answers (3)

jAC
jAC

Reputation: 5325

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:Scopes

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.

Upvotes: 2

Chris Pickford
Chris Pickford

Reputation: 9001

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();
}

Further reading: using Statement

Upvotes: 2

ZnIpE
ZnIpE

Reputation: 5

Think you'll need to look at how to use the "using" keyword. There should probably be a block statement following it containing parts of your code.

Upvotes: 0

Related Questions