Inserting and Updating data to MDB

Question

I'm trying to make a simple test program that can open MDB files and do 3 basic things the MDB have 3 fields, all of them are text:

ID
INFO 
TEXT

showing data acording to ID = got this working
changing data according to ID = problem
adding new data = problem

the show data works with this code:

con = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c:\\mdb\\testmdb.mdb");
cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandText = "select Info, text from Table1 where ID = '" + int.Parse(textBox1.Text) + "' ";
con.Open(); // open the connection
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
  textBox2.Text = dr["Info"].ToString();
  textBox3.Text = dr["text"].ToString();
}
con.Close();

How do I insert new data in MDB and update data I already have?

score 2 · Answer 1 · answered Oct 10 '17 at 22:25

Working code

using System;
using ADOX;
using System.Data.OleDb;
using System.Data;
using System.IO;

namespace ConsoleApplication4
{
class Program
{
    static void Main(string[] args)
    {
        CreateMdb("toster_ru.mdb");
        string fileNameWithPath = Environment.CurrentDirectory + "\\toster_ru.mdb";

        CreateTableInToMdb(fileNameWithPath);
        InsertToMdb(fileNameWithPath);
        UpdateToMdb(fileNameWithPath);

        var myDataTable = new DataTable();
        using (var conection = new OleDbConnection("Provider = Microsoft.JET.OLEDB.4.0;  Data Source = " + fileNameWithPath))
        {
            conection.Open();
            var query = "Select info From my_table";
            var adapter = new OleDbDataAdapter(query, conection);
            adapter.Fill(myDataTable); 
            Console.WriteLine(myDataTable.Rows[0][0].ToString()); //output: toster2.ru
            Console.ReadKey();
        }
    }

    static void CreateMdb(string fileNameWithPath)
    {
        if (File.Exists(fileNameWithPath))
            return;

        Catalog cat = new Catalog();
        string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=5";
        cat.Create(String.Format(connstr, fileNameWithPath));
        cat = null; 
    }

    static void InsertToMdb(string fileNameWithPath)
    {
        var con = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + fileNameWithPath);
        var cmd = new OleDbCommand();
        cmd.Connection = con;
        cmd.CommandText = "insert into my_table (ID, [Info], [text])  values (@ID, @Info, @text);";
        cmd.Parameters.AddWithValue("@ID", 1);
        cmd.Parameters.AddWithValue("@Info", "toster.ru");
        cmd.Parameters.AddWithValue("@text", "blabla");
        con.Open(); 
        cmd.ExecuteNonQuery();
        con.Close();
    }

    static void UpdateToMdb(string fileNameWithPath)
    {
        var con = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + fileNameWithPath);
        var cmd = new OleDbCommand();
        cmd.Connection = con;
        cmd.CommandText = "UPDATE my_table SET [Info] = ?, [text] = ? WHERE ID = ?;";
        cmd.Parameters.AddWithValue("@p1", OleDbType.VarChar).Value = "toster2.ru";
        cmd.Parameters.AddWithValue("@p2", OleDbType.VarChar).Value = "blabla2";
        cmd.Parameters.AddWithValue("@p3", OleDbType.VarNumeric).Value = 1;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    static void CreateTableInToMdb(string fileNameWithPath)
    {
        try
        {
            OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileNameWithPath);
            myConnection.Open();
            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = myConnection;
            myCommand.CommandText = "CREATE TABLE my_table([ID] NUMBER, [Info] TEXT, [text] TEXT)";
            myCommand.ExecuteNonQuery();
            myCommand.Connection.Close();
        }
        catch { }
    }
}
}

sevenkul · Answer 2 · 2011-12-24T22:16:05.743

1

Try this for insert:

con = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c:\\mdb\\testmdb.mdb");
cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandText = "insert (ID, Info, text) into Table1 values (@ID, @Info, @text);";
cmd.Parameters.AddWithValue("@ID", textBox1.Text);
cmd.Parameters.AddWithValue("@Info", textBox2.Text);
cmd.Parameters.AddWithValue("@text", textBox3.Text);
con.Open(); // open the connection
//OleDbDataReader dr = cmd.ExecuteNonQuery();
cmd.ExecuteNonQuery();
con.Close();

Try this for update:

con = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c:\\mdb\\testmdb.mdb");
cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandText = "update Table1 set [Info] = @Info, [text] = @text where ID = @ID;";
cmd.Parameters.AddWithValue("@ID", textBox1.Text);
cmd.Parameters.AddWithValue("@Info", textBox2.Text);
cmd.Parameters.AddWithValue("@text", textBox3.Text);
con.Open(); // open the connection
//OleDbDataReader dr = cmd.ExecuteNonQuery();
cmd.ExecuteNonQuery();
con.Close();

For more operations, examine the left panel of this site.

edited Dec 24 '11 at 22:16

answered Dec 24 '11 at 16:19

sevenkul

966
1
8
14

It's probably considered best practice to use `Using {}` brackets now. Also, you don't need those data readers, just call `cmd.ExecuteNonQuery();` – LarsTech Dec 24 '11 at 17:00
Error 2 Cannot implicitly convert type 'int' to 'System.Data.OleDb.OleDbDataReader' – Iakovl Dec 24 '11 at 17:09
The return value from ExecuteNonQuery is an int, convert those 'OleDbDataReader dr' lines into 'int numAffected', and it should work correctly – Anya Shenanigans Dec 24 '11 at 17:31
OK insert works but i still have problems with update "Syntax error in UPDATE statement." – Iakovl Dec 24 '11 at 19:55
TEXT seems to be a reserved word according to [this page](http://support.microsoft.com/kb/248738). Try putting `text` into brackets like `[text]`. – sevenkul Dec 24 '11 at 22:18

Inserting and Updating data to MDB

2 Answers2

Linked