asked on

Using joins with a C# DataReader - getting IndexOutOfRangeExceptions

Hi all,

I am having some issues with trying to retrieve the results of a Join using a DataReader. The syntax seems to work ok as a record is returned but I want to access one column from the table i'm joining to and it won't let me for some reason.

Here is the relevant code (I have commented out sections to just try to debug the one column).

try
{

//this.mstrSQL = "SELECT ProjectID FROM Projects WHERE (ProjectID =@@IDENTITY)";
this.mstrSQL = "SELECT Customers.CompanyName FROM Customers, Projects WHERE ProjectID = " + this.mintProjectID + " AND Projects.CustomerID=Customers.CustomerID";
//this.mstrSQL = "SELECT * FROM Projects, Customers WHERE ProjectID = " + this.mintProjectID + " AND Projects.CustomerID=Customers.CustomerID";
this.openDatabase();
OleDbCommand myCmd = new OleDbCommand(this.mstrSQL, this.dbConnect);
OleDbDataReader myReader = myCmd.ExecuteReader();

if (myReader.HasRows)
{
while (myReader.Read())
{
//we found a value, grab the ProjectID
/*
this.txtProjectTitle.Text = (String)myReader["ProjectTitle"];
this.txtProjectNotes.Text = (String)myReader["ProjectNotes"];
this.txtStartDate.Text = System.Convert.ToString(myReader["StartDate"]);
this.txtDueDate.Text = System.Convert.ToString(myReader["DueDate"]);
this.mintProjectID = (int)myReader["ProjectID"];
this.txtRender1Loc.Text = System.Convert.ToString(myReader["RenderLocation1"]);
this.txtRender1Desc.Text = System.Convert.ToString(myReader["RenderDescription1"]);
this.txtRender2Loc.Text = System.Convert.ToString(myReader["RenderLocation2"]);
this.txtRender2Desc.Text = System.Convert.ToString(myReader["RenderDescription2"]);
this.txtRender3Loc.Text = System.Convert.ToString(myReader["RenderLocation3"]);
this.txtRender3Desc.Text = System.Convert.ToString(myReader["RenderDescription3"]);
this.txtRender4Loc.Text = System.Convert.ToString(myReader["RenderLocation4"]);
this.txtRender4Desc.Text = System.Convert.ToString(myReader["RenderDescription4"]);
this.txtRender5Loc.Text = System.Convert.ToString(myReader["RenderLocation5"]);
this.txtRender5Desc.Text = System.Convert.ToString(myReader["RenderDescription5"]);
*/
this.cboCustomers.Text = System.Convert.ToString(myReader["Customers.CompanyName"]); //LINE I AM THROWING EXCEPTION ON

break;
}
}
else
{
//we didnt find any values, this is an issue, abort
MessageBox.Show("Unable to locate any valid data in this project!", "Data Value Error");
myReader.Close();
return;
}
myReader.Close();
this.closeDatabase();
}
catch (Exception exp)
{
//something went wrong, abort
string tempmsg = exp.Message;
string tempmsg2 = exp.ToString(); //<-- This shows that it is an IndexOutOfRangeException on "Customers.CompanyName"
this.closeDatabase();
//MessageBox.Show("Exception thrown during attempt to obtain ProjectID, aborting.", "Fatal Data Error");

}

I know my two SQL statements are a bit different, but neither one works and switching the ordering of Customers, Projects to Projects,Customers seems to make no difference either. It is possible what I am doing is no longer allowed as the way I am doing the Join doesnt use the JOIN keyword itself and worked great under ADO and DAO back in my VB6 days but perhaps I need some different syntax to accomplish the same thing now.

Thanks,
Nathan

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mattbelfast23

The code you have shown looks like its doing 3 separate queries which would return 3 separate result sets. You need to use myReader.NextResult() to advance to the next available result set.

do
{
while (myReader.Read())
{
}
}
while(myReader.NextResult())
}

ironwill96

ASKER

Bah, you'd think I would have tried dropping the table prefix. Thanks angelIII!

Nathan