How to connect to an Access database using the OleDb namespace in .NET?

Question

I want to use an Access database for my Windows Forms application. (written with C#)
I have used OleDb namespace for connecting, and I'm able to select the records from the source using the OleDbConnection and ExecuteReader objects.
However, I can't insert, update or delete records yet.

My code is the following:

 OleDbConnection con = new OleDbConnection(strCon);

 try
 {
     string con="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xyz.mdb;Persist Security Info=True";

     con.Open();

     OleDbCommand com = new OleDbCommand("INSERT INTO DPMaster(DPID, DPName, ClientID, ClientName) VALUES('53', 'we', '41', 'aw')", con);

     int a = com.ExecuteNonQuery();

     //OleDbCommand com = new OleDbCommand("SELECT * FROM DPMaster", con);
     //OleDbDataReader dr = com.ExecuteReader();

     //while (dr.Read())
     //{
     //    MessageBox.Show(dr[2].ToString());
     //}

     MessageBox.Show(a.ToString());
 }
 catch
 {
     MessageBox.Show("cannot");
 }

If I the commentted block is executed, the application works fine. But the insert block doesn't.

Knowing this, why I am unable to insert, update or delete database records?

Instead of catching the exception, you should see what the exception says. It should give you an indication of what's going wrong. — Frederik Gheysels
– Frederik Gheysels, Commented Dec 24, 2010 at 13:52
Do catch(Exception ex) and show ex.Message, or see ex detail — Saeed Amiri
– Saeed Amiri, Commented Dec 24, 2010 at 14:01
You shouldn't use access, your application will become slow and the data integrity unstable, and you'll have to migrate the entire db if you go to MS-SQL. Use FireBird embedded if you need an embeddable database. It has the added benefit that you can plug in the embeddable db file into the full firebird server once this becomes necessary, and it's cross-platform. — Stefan Steiger
– Stefan Steiger, Commented Dec 24, 2010 at 14:04
@Quandary: @Abilash is not using Access -- he's using Jet, which happens to be installed on all copies of Windows from Win2000 on. That's a great advantage of using it, that it's available for your application without need of installation. It's also perfectly suitable database engine for a whole host of applications. Adding in the dependency on SQL Server or Firebird brings installation/configuration/administrations issues while for any particular application it may not bring with it any useful benefits. — David-W-Fenton
– David-W-Fenton, Commented Dec 26, 2010 at 0:41

Jahan Zinedine · Accepted Answer · 2010-12-24 13:57:45Z

4

the problem that I encountered myself is as:

You've added the mdb file to your solution and every time you run the program it will be copied into debug folder.

So you can select from it but deleting rows doesn't affect the original file you have in your solution.

Check for it.

answered Dec 24, 2010 at 13:57

Jahan Zinedine

14.9k5 gold badges49 silver badges74 bronze badges

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

2 Comments

James Black Over a year ago

+1 - As it is a database, I would never have thought about adding it to my solution, so never would have come up with that as a probable problem.

Abilash Over a year ago

As you said I have removed the database from the project and used from other location.Now its working fine. The records are inserted into the database. Thanks a lot Jane..

Will Marcouiller · Accepted Answer · 2010-12-24 14:05:44Z

2

First, never strangle your exception. It is better to let your exception bubble up so that you may get important information regarding what is not working properly. It is better to write:

con.Open();
OleDbCommand com = new OleDbCommand("INSERT INTO DPMaster(DPID,DPName,ClientID,ClientName) VALUES('53','we','41','aw')", con);

int a = com.ExecuteNonQuery();

than

try {
    con.Open();
    OleDbCommand com = new OleDbCommand("INSERT INTO DPMaster(DPID,DPName,ClientID,ClientName) VALUES('53','we','41','aw')", con);

    int a=com.ExecuteNonQuery();

} catch {
    MessageBox.Show("cannot");
}

Second, make use of using blocks as much as possible, since those blocks will dispose the no longer needed objects. So your code should look like this:

using (OleDbConnection con = new OleDbConnection(conStr))
    using (OleDbCommand com = new OleDbCommand("INSERT INTO DPMaster(DPID,DPName,ClientID,ClientName) VALUES('53','we','41','aw')", con) {

        con.Open();

        int a = com.ExecuteNonQuery();

        MessageBox.Show(a.ToString());
    }

With this code, you will more likely get to know what is going wrong while the exception will bubble up, plus, as soon as you'll quit the scope of the using blocks, resources used will be freed up as your objects will get disposed.

answered Dec 24, 2010 at 14:05

Will Marcouiller

24.3k26 gold badges100 silver badges166 bronze badges

1 Comment

Abilash Over a year ago

The code which i have pasted works fine without giving any error!!But the record is not inserted into database!!

Collectives™ on Stack Overflow

How to connect to an Access database using the OleDb namespace in .NET?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related