Executing query using OleDbDataReader, getting OleDbException (0x80004005)

Question

I'm having some issues with DataReader holding some data from a database.. or at least I think this is how it works.

I have a ComboBox and at the start of the program I populate its Items with data from:

Connect.Open();
Command.Connection = Connect;
Command.CommandText = "Select * from Department";
OleDbDataReader dr;
dr = Command.ExecuteReader();
if (dr.HasRows)
{
    while (dr.Read())
    {
        cmbDepartment.Items.Add(dr[0].ToString());
    }
}
Connect.Close();

It works fine and populate the ComboBox at the start of the program now when the user selects one item from the combo box.

I would like to have the next ComboBox's Items populate based on what Department the user has selected on the first combo box.

Connect.Open();

Command.CommandText = "select JobTitle from Position where Department ='"+cmbDepartment.Text + "'";

OleDbDataReader dr2;
dr2 = Command.ExecuteReader();

if (dr2.HasRows)
{
    while (dr2.Read())
    {
        cmbPosition.Items.Add(dr2[0]);
    }
}

The thing is, I have perfectly similar codes to my classmates and mine has an unusually long error message that we cannot debug.

The error happens after I select something from the cmbDepartment. Using a try/catch, I got this error message:

System.Data.OleDb.OleDbException (0x80004005): IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.ExecuteReader()
   at katapusan.Employee.cmbDepartment_TextChanged(Object sender, EventArgs e) in c:\Users\Matt\Documents\Visual Studio 2012\Projects\katapusan\katapusan\Employee.cs:line 423

Always use parameters to avoid SQL injection. TextChanged looks like the wrong event: you probably want SelectedIndexChanged. — LarsTech, Oct 20 '14 at 16:23
guys i think the problem IS the Position table name, thanks editing the database now! thanks for quick! god bless everyone! — Matt Yoü, Oct 20 '14 at 16:27

score 0 · Answer 1 · answered Oct 20 '14 at 16:22

You could set the dataReader into the DataSource property and set the columns of your query to show what is the value and text of comboBox. For sample:

cmbDepartment.DataSource = Command.ExecuteReader()
cmbDepartment.DisplayMember = "Name";
cmbDepartment.ValueMember = "Id";

Also change your sql query to get only the columns you need, for sample:

Command.CommandText = "Select Id, Name from Department";

On the second case, just make sure about the column type and use Parameters

Command.CommandText = "select JobTitle from [Position] where Department = @Departament";
Command.Parameters.Add("@Departament", SqlDbType.VarChar).Value = cmbDepartment.Text;

The logic to apply on a second ComboBox is the same, chose the columns you need and set on DisplayMember and ValueMemeber.

thanks for the answer sir kind sir! well i dont have IDS for my department and position.. should i include these to the database? why does the error happen only to me? i mean my classmates have the same codes as well :c — Matt Yoü, Oct 20 '14 at 16:24

Grant Winney · Answer 2 · 2014-10-20T16:31:11.183

I believe "Position" may actually be a reserved word. Enclose it in square brackets:

Command.CommandText =
    "select JobTitle from [Position] where Department ='"+cmbDepartment.Text + "'";

FWIW, you've got some other design issues you may want to look into:

Parameterize your queries instead of concatentating parameter values to the end of your query. It's more secure and easier to maintain.
Instead of creating a single Connect and Command instance at the class-level, and reusing them in multiple places, try creating new instances inside each method, and dispose of them when you're done. You're less likely to leave a database connection open, or to run into other problems (like forgetting to clear the parameters collection when reusing a command).

score 0 · Answer 3 · answered Oct 20 '14 at 16:22

0

Put brackets around position , it's a reserved word.

Reserved Works in Access

      Command.CommandText =
"select JobTitle from [Position] where Department ='"+cmbDepartment.Text + "'";

answered Oct 20 '14 at 16:22

Cam Bruce

5,632
19
34

Executing query using OleDbDataReader, getting OleDbException (0x80004005)

3 Answers3