Help with While (reader.read) loop

[mandi7]

I am using while (reader.Read() ) to read the rows from excel spreadsheet and want to exit when the last row has been read. I am using the code below. Some how it is always selecting one extra row.
Any help would be appreciated.




while (reader.Read() )
{
string AgentID = Convert.ToString(reader["Sales ID"]);
string Notes = Convert.ToString(reader["Notes"]);
string AdjAmount = Convert.ToString(reader["Adjustment"]);
string AdjustmentType = "";

if ((AdjAmount == null) || (AgentID == null) || (Notes == null))
{
r = 3; //return value to the calling method
}
else
{
if (Convert.ToString(reader[3]) == "")
{
r = imp.Insert_tblSummaryAdjustment_New("", AdjAmount, AgentID, Notes, xlsheetname, year, month);


import.Update_tblSummaryAdjustment(AgentID);
}
else
{


AdjustmentType = Convert.ToString(reader["AdjustmentType"]);
//AdjustmentType = Convert.ToString(reader[1]);



r = imp.Insert_tblSummaryAdjustment(AdjustmentType, AdjAmount, AgentID, Notes, xlsheetname, year, month);


}

}

}

reader.Close();

[MNovy]

This is a general problem with that Excel reading access:

The reader will never know when the last row is reached,
because some data could be written after 100s of empty rows.

So you need either a delimiter to mark the last line, if this is read you finish your loop,

or

skip reading after a number of lines which will never be filled with data.


But I would suggest the first solution, if you write the Excel files by your own.

Use the second, if you got the Excel file from someone else. Just assure that
the row lines will not exceed a special count. So you can read always 100
lines (but in real filled with data only up to 50-60 lines) or sth like that.

[nelo]

Hi mandi7.

Here are my thoughts...

1. Please use code tags when putting code in your posts. This is how you do it... (http://www.codeguru.com/forum/misc.php?do=bbcode#code). For starters you can edit your original post...

2. You don't show how you got the reader? How did you structure the query? You can select any range you want in the Excel sheet. So perhaps you just need to change the selection to include one row less.