CODEWITHSUNDEEP
c#ms-access-2010
Toddlerj
Toddlerj

Reputation: 13

C# Query MS-Access Table and place read values from column in a text box

Below is a snapshot of my code. I am trying to access the only column in the customer table and place the values into a textbox on the form. I keep getting the error with my code "InvalidOperationException was unhandled" at the line declaring dr as a OleDbDataReader object.

  1. What do I have wrong with the below code that would be giving me this error?

  2. Should I do a list to pick out the text I want from the database?

  3. How can I return the column values from access into a list in C# so that I can search the list for a particular value?

    string strsql = "Select * from Customer";
OleDbCommand cmd = new OleDbCommand();

cmd.CommandText = strsql;
conn.Open();

OleDbDataReader dr = cmd.ExecuteReader();



while(dr.Read())
{
    textBox1.Text += dr["Customer"].ToString();
}
conn.Close();

Upvotes: 1

Views: 1579

Answers (2)

Steve
Steve

Reputation: 216243

A command carries the info to be executed, a connection carries the info to reach the database server. The two objects should be linked together to produce any result. You miss that line

OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = strsql;
cmd.Connection = conn; // <= here
conn.Open();

Remember also that disposable objects like a command, a reader and a connection should be disposed immediately after usage. For this pattern exists the using statement

So you should write 

string cmdText = "Select * from Customer";
using(OleDbConnection conn = new OleDbConnection(.....constring...))
using(OleDbCommand cmd = new OleDbCommand(cmdText, conn))
{
    conn.Open();
    using(OleDbDataReader reader = cmd.ExecuteReader())
    {
        while(reader.Read())
           .....
    }
}

Upvotes: 1

Pascal
Pascal

Reputation: 251

Here is some sample code.

try
            {
                using (OleDbConnection myConnection = new OleDbConnection())//make use of the using statement 
                {
                    myConnection.ConnectionString = myConnectionString;
                    myConnection.Open();//Open your connection
                    OleDbCommand cmdNotReturned = myConnection.CreateCommand();//Create a command 
                    cmdNotReturned.CommandText = "someQuery";
                    OleDbDataReader readerNotReturned = cmdNotReturned.ExecuteReader(CommandBehavior.CloseConnection);
                        // close conn after complete
                    // Load the result into a DataTable
                    if (readerNotReturned != null) someDataTable.Load(readerNotReturned);
               }
            }

After that you have a Datatable containing your data. Ofcourse you can afterwards search for records in the Datatable any way you like.

Upvotes: 1

Related Questions