CODEWITHSUNDEEP
c#databasems-accessintegration
Tassisto
Tassisto

Reputation: 10345

Failing to retrieve data from populated tables in ACCDB

I created a C# application and integrated an Access DB (with 3 populated tables).

Now I try to get data from the Access DB-tables to display it in a TextBox, but nothing is visible.

Thank you in advance.

UPDATE: namespace --> using System.Data.OleDb;

Here is the code:

    OleDbDataReader myReader;
    OleDbConnection myCon;
    OleDbCommand myQuery;

    public Form1()
    {
        InitializeComponent();
        myCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\UserName\\Desktop\\MyClubAdministration\\MyClubAdministrationDB.accdb;Persist Security Info=False;");
        myCon.Open();
        myQuery = new OleDbCommand("select * from Lid, Lidgeld, Lidmaatschap", myCon);
        myReader = myQuery.ExecuteReader();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        textBox1.Text = myReader.GetInt32(0).ToString();
    }

Upvotes: 0

Views: 1059

Answers (2)

Steve
Steve

Reputation: 216293

You need to Read before GetInt32 on an OleDBDataReader

private void Form1_Load(object sender, EventArgs e)     
{         
    myReader.Read();
    textBox1.Text = myReader.GetInt32(0).ToString();     
}

However this code is very error prone.
Try to isolate the database operarations in a private methods and call just from one point without splitting between constructor and form_load.

private void ReadFromDB()
{
    using(OleDbConnection myCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\UserName\\Desktop\\MyClubAdministration\\MyClubAdministrationDB.accdb;Persist Security Info=False;"))
    {
         myCon.Open();             
         OleDbCommand myQuery = new OleDbCommand("select * from Lid, Lidgeld, Lidmaatschap", myCon);
         OleDbDataReader myReader = myQuery.ExecuteReader();
         if(myReader.HasRows)
         {
              myReader.Read();
              textBox1.Text = myReader.GetInt32(0).ToString();
         }
    }
}

call that method after InitializeComponent();

As a side note, I will avoid the globals for the database operations. With connection pooling the performance cost to open and reopen a connection is really minimal, while the complexity of housecleaning grows exponentially.

Upvotes: 1

JeffFerguson
JeffFerguson

Reputation: 3002

The ExecuteReader() method of the OleDbCommand class returns an object of class OleDbDataReader. When the OleDbDataReader object is first returned to you, it is in an "unpositioned" state -- that is, it is not positioned to any records in the data set. You must call Read() ob the object to position it to the next record in the set. Your Load() method should be as follows:

private void Form1_Load(object sender, EventArgs e)
{
    if(myReader.HasRows == true)
    {
        myReader.Read();
        textBox1.Text = myReader.GetInt32(0).ToString();
    }
}

The call to the HasRows property of the OleDbDataReader is to ensure that there are, in fact, records in the data set before the positioning is performed.

To be even more careful, you could check the field in which you are interested for a database null value before trying to use it:

private void Form1_Load(object sender, EventArgs e)
{
    if(myReader.HasRows == true)
    {
        myReader.Read();
        if(myReader.IsDBNull(0) == false)
            textBox1.Text = myReader.GetInt32(0).ToString();
    }
}

Upvotes: 1

Related Questions