data from access db file

Question

I am unsure but i am unable to get the data to show in the text box. Here is the code i have written so far. Any help would be great. There is nothing going in the boxes but doing the test i got the message box. Is there something i am not doing correct ? I can provide the access file if needed. But is is just five fields with data in it.

DataSet DataSet1; //use to put data in form

System.Data.OleDb.OleDbDataAdapter dataadapter;

private void Breed_Load(object sender, EventArgs e)
{
    dbconnect = new System.Data.OleDb.OleDbConnection();//database connection variable
    DataSet1 = new DataSet(); //variable to help get info from DB

    dbconnect.ConnectionString = "PROVIDER= Microsoft.Jet.OLEDB.4.0; Data Source=C:/Pets.mdb"; //location of DB to open

    dbconnect.Open(); //open command for DB

    string sql = "SELECT * From tblPets"; //sql string to select all records from the table pets
    dataadapter = new System.Data.OleDb.OleDbDataAdapter(sql, dbconnect); // pulls the records from sql command

    MessageBox.Show("Database is Open");

    dataadapter.Fill(DataSet1, "Pets"); // used the database to fill in the form.
    NavRecords(); //calls NavRecords Method

    dbconnect.Close();

   MessageBox.Show("Database is Closed");

    dbconnect.Dispose(); 


}


private void NavRecords()
{
    DataRow DBrow = DataSet1.Tables["Pets"].Rows[0];

    //PetNametextBox.Text = DBrow.ItemArray.GetValue(1).ToString(); //puts data in textbox
    TypeofPettextBox.Text = DBrow.ItemArray.GetValue(1).ToString();//puts data in textbox
    PetWeighttextBox.Text = DBrow.ItemArray.GetValue(2).ToString();//puts data in textbox
    ShotsUpdatedtextBox.Text = DBrow.ItemArray.GetValue(3).ToString();//puts data in textbox
    AdoptabletextBox.Text = DBrow.ItemArray.GetValue(4).ToString();//puts data in textbox
    BreedtextBox.Text = DBrow.ItemArray.GetValue(5).ToString();//puts data in textbox
}

Set a break point and check if the row actually contains data. Consider using "DBrow[0].ToString()" also you should consider using a DataGridView. — MrFox
– MrFox, Commented Jun 29, 2011 at 19:37
I set a break point and did not see anything going to it. But I am not 100 percent where to look. UGH — matthew moore
– matthew moore, Commented Jun 29, 2011 at 19:46
Okay i set breakpoint and see the database is getting open then break point will it used the database to fill the form but when it gets to the navrecords method i do not see anything. . I do not understand dbrow to string but will try. — matthew moore
– matthew moore, Commented Jun 29, 2011 at 19:54

Icemanind · Accepted Answer · 2011-06-29 20:42:03Z

2

Fetching data from an Access database is fairly simple. Here is an example:

public static DataTable GetBySQLStatement(string SQLText)
{
    System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
    string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Pets.MDB";
    System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection();

    Conn.ConnectionString = ConnectionString;
    cmd.CommandType = CommandType.Text;
    cmd.Connection = Conn;
    cmd.CommandText = SQLText;

    DataSet ds;
    System.Data.OleDb.OleDbDataAdapter da;
    DataTable Table = null;

    Conn.Open();
    da = new System.Data.OleDb.OleDbDataAdapter();
    da.SelectCommand = cmd;
    ds = new DataSet();
    da.Fill(ds);

    if (ds.Tables.Count > 0)
        Table = ds.Tables[0];
    Conn.Close();
    return Table;
}

You call this function like so:

DataTable dt = GetBySQLStatement("SELECT * FROM tblPets");

if (dt != null) {
    // If all goes well, execution should get to this line and
    // You can pull your data from dt, like dt[0][0]
}

The only "gotcha" to be aware of is that this code must be compiled as a 32-bit application because there are no 64-bit Jet drivers. By default, Visual Studio will compile as a mixed 32-bit and 64-bit program. Change the option in your project settings to make sure its 32-bit only.

edited Jun 29, 2011 at 20:42

answered Jun 29, 2011 at 19:45

Icemanind

48.9k52 gold badges182 silver badges308 bronze badges

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

1 Comment

David-W-Fenton Over a year ago

There are no 64-bit Jet drivers, but you can use the ACE (downloadable from MS) in its 64-bit version to access MDB files in a 64-bit context.

Collectives™ on Stack Overflow

data from access db file

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related