How to get specific data from excel sheet into textboxes

Question

I want to get specific data from Excel Sheet into winforms TextBoxes using search query. something like this "Search * from [Sheet1] where Staff Number=1234"

i tried this block of code but it's not working. I got an exception every time that says, Connection is not initialized properly.

try{
OleDbConnection con = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=Test.xls;Extended Properties=Excel 8.0;");
           con.Open();
           OleDbCommand oleDbCommand = new OleDbCommand("SELECT * FROM [Sheet1] where Staff Number=1234");

              OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader();


                   TxtDateOfBirth.Text = oleDbDataReader.GetString(1);
                   TxtName.Text=oleDbDataReader.GetString(2);
.
.
.
.
      }
           catch(Exception ex)
           {
               MessageBox.Show(ex.ToString());
           }

It's a simple form where the user will input the staff number and get the details of the person in relevant text boxes. Most of My Search results get me the solution of showing data into a datagridview but my problem is a bit different, i know i have to use data reader and execute it, but don't know why getting this problem.

Any Guide would be very helpful.

Answer 1

It seems to me there are a few problems in your code:

1- You should place the full address of Excel file in your connection, instead of "test.xls".

2- There is a space between "Staff" and "Number" in your command. if the column name is "StaffNumber" there should not be any space. I recommend you to test the command without where clause first.

Answer 2

Tweaked your code a bit..

Improvements

Changed your connection string, added HDR=Yes; which indicates that the first row contains columnnames and also IMEX=1; which tells the driver to always read “intermixed” (numbers, dates, strings etc) data columns as text. These are generally helpful when working with staff dbs.

Problems

Added variables to make it cleaner and full path to your xls db file.

Added a $ after Sheet1 and also wrap [Staff Number] with [] as suggested in the comments by other helping users above.

Added oleDbDataReader.Read() to read through the result(s), as this was also missing in your code above.

Example code below works for me.

try 
{           
    var xlsDbPath= "C:\\Temp\\Test.xls"; //<-- Full name of path
    var connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsDbPath+ ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
    var query = "SELECT * FROM [Sheet1$] WHERE [Staff Number] = 1234";  //<-- Add $ after Sheet1 and fix 'Staff Number'?

    using (var con = new OleDbConnection(connStr))
    {
        con.Open();
        using (var oleDbCommand = new OleDbCommand(query, con))
        {
            using (var oleDbDataReader = oleDbCommand.ExecuteReader())
            {
                while (oleDbDataReader.Read())  //Read through results
                {
                     TxtDateOfBirth.Text = oleDbDataReader.GetString(1);
                     TxtName.Text = oleDbDataReader.GetString(2);
                     //...  //Remember if value is not string you will get error
                     //...  //so if not string use .GetValue(1).ToString();
                } 
            }
        }
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}