Working with OLEDB and MDB

Question

5.00/5 (1 vote)

See more:

OleDB and mdb...

Inserting data to the database is where I'm having problems. My scan.mdb contains 3 columns, which I need to add data row by row to. The connect/disconnect/dataGridView preload strings are concise and work well. The database just needs to overwrite everytime I create a new scan, but OleDbCommand not working well either for me.

Most examples I have found, are not clear, or just read the database.

Any help appreciated!
If I do figure it out though I will write a new article about databases, because this always stumps me. Half way into a project, then I realize I have to do database stuff, and I hit a wall.

/*
 * Begin Connection
 */

private static OleDbConnection OpenConnection()
{
    OleDbConnection conn = new OleDbConnection();
    conn.ConnectionString = @"Provider=Microsoft.JET.OlEDB.4.0;" + @"Data Source=" + Directory.GetCurrentDirectory().ToString() + @"\scanDB.mdb";
    try
    {
        conn.Open();
    }
    catch (Exception ex)
    {
        MessageBox.Show("Failed to connect to data source");
    }
    return conn;
}

/*
 * Close Connection
 */

private static void CloseConnection(OleDbConnection conn)
{
    try
    {
        conn.Close();
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }
}

/*
 * Update dataGridView1
 */

private void retrieveTables()
{
    OleDbConnection conn = OpenConnection();
    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM scan", conn);
    DataSet ds = new DataSet();
    da.Fill(ds, "scan");
    dataGridView1.DataSource = ds.Tables["scan"].DefaultView;
}

Posted 16-Jul-11 7:26am

Bobbydoo8

Add a Solution

Comments

Abhinav S 17-Jul-11 12:37pm

Well done on getting the query!

Member 8043849 17-Jul-11 12:53pm

yup..

2 solutions

Solution 2

Thank you for the information. Spent some more time trying to get the "insert" query to work today and puff, got it.

/*
 * Insert Data
 */
private void insertTable()
{
    OleDbConnection conn = GetConnection();
    OleDbCommand cmd;
    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM scan", conn);
    cmd = new OleDbCommand("INSERT INTO scan ([COL1], [COL2], [COL3]) " +
               "VALUES (@Var1, @Var2, @Var3)", conn);

    cmd.Parameters.Add("@Var1", OleDbType.VarChar).Value = "1";
    cmd.Parameters.Add("@Var2", OleDbType.VarChar).Value = "2";
    cmd.Parameters.Add("@Var3", OleDbType.VarChar).Value = "3";
    cmd.ExecuteNonQuery();
    da.InsertCommand = cmd;
    conn.Close();

    // Update datagridview1
    retrieveTables();
}

Thanks again works great!

Posted 17-Jul-11 6:17am

Bobbydoo8

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Accepted Answer · 2011-07-16T08:13:00

Solution 1

You need an insert query that will actually insert data into the tables.

Posted 16-Jul-11 8:13am

Abhinav S