connect and read .MDB item with C#

Question

Is it possible to connect to a local MDB file and pick a single bit of info out of it ? I have a table in a .mbd file with a single bit of info in it. I would like to have that record be output into a disabled textbox for a reference. I believe I can get the DB open, and run the query but no idea what I need to read from it.

thanks

var myDataTable = new DataTable();
        using (var conection = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" + "data source=C:\\menus\\newmenus\\menu.mdb;Password=****"))
        {
            conection.Open();
            var query = "Select siteid From n_user";
            var adapter = new OleDbDataAdapter(query, conection);
            OleDbCommandBuilder oleDbCommandBuilder = new OleDbCommandBuilder(adapter);
        }

Answer 1

To simply read a single field on your database table you could use an OleDbDataReader that could loop over the result and return the field required..

var myDataTable = new DataTable();
using (var conection = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" + "data source=C:\\menus\\newmenus\\menu.mdb;Password=****"))
{
     conection.Open();
     var query = "Select siteid From n_user";
     var command = new OleDbCommand(query, conection);
     var reader = command.ExecuteReader();
     while(reader.Read())
         textBox1.Text = reader[0].ToString();

 }

if you have just one record and just one field then a better solution is the method ExecuteScalar

     conection.Open();
     // A query that returns just one record composed of just one field
     var query = "Select siteid From n_user where userid=1";
     var command = new OleDbCommand(query, conection);
     int result = (int)command.ExecuteScalar();  // Supposing that siteid is an integer

Probably I should also mention that ExecuteScalar returns null if the query doesn't find a match for the userid, so it is better to be careful with the conversion here

     object result = command.ExecuteScalar();
     if( result != null)
        int userID  = (int)result;
        .....

Answer 2

Yes very possible. Just have the adapter fill the DataTable, also I don't think you'll need the OleDbCommandBuilder.

    using (var conection = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" + "data source=C:\\menus\\newmenus\\menu.mdb;Password=****"))
    {
        conection.Open();
        var query = "Select siteid From n_user";
        var adapter = new OleDbDataAdapter(query, conection);
        adapter.Fill(myDataTable);
        myTextBox.Text = myDataTable.Rows[0][0].ToString();
    }

Also I think using ExecuteScalar would be a better solution, but my answer was tailored to the objects you had already instantiated.

Answer 3

You could use OleDbCommand.ExecuteScalar to retrieve a single value. It is returned as an object and you could cast it to the correct type.