DataReader problem

[JR]

Hi,

In the code below, any attempt to read data results in the exception
"Invalid attempt to read when no data is present." Examining the
DataReader, the _dataReady is false and HasRows is true. This stored
procedure returns around 1000 records. Any idea why I'm getting this
exception?

thanks,

JR


SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "xsp_SolomonToSFOpportunity";
com.Parameters.Add("@ProcessDate", DateTime.Now.Subtract(new
TimeSpan(daysPrevious,0,0,0)).ToString("d"));
SqlDataReader dr = com.ExecuteReader();
bool moreRows = dr.Read();

while (moreRows) {
if (dr.IsDBNull(dr.GetOrdinal("ContractID")) ||
dr.GetString(dr.GetOrdinal("ContractID")) == "") {
// do something
}
}

 

[Guest]

I believe that you have an error in your while loop validation.

while (moreRows){
some code here
}

since more rows contains a true value from when you checked against your
reader it never changes to false when you reach the end of the reader hence
you get the error.

try changing your code to this:

SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "xsp_SolomonToSFOpportunity";
com.Parameters.Add("@ProcessDate", DateTime.Now.Subtract(new
TimeSpan(daysPrevious,0,0,0)).ToString("d"));
SqlDataReader dr = com.ExecuteReader();
// delete this and the row below
// bool moreRows = dr.Read();

while (dr.Read()) {
if (dr.IsDBNull(dr.GetOrdinal("ContractID")) ||
dr.GetString(dr.GetOrdinal("ContractID")) == "") {
// do something
}
}

this should solve your problem of going out of bound.

 

[W.G. Ryan eMVP]

JR:

I agree with Abhishek about the problem. moreRows is set to true assuming
you have at least one row. Thereafter Nothing changes that value so it will
necessarily read past the loop.

you have another issue though .Assume you have 1000 rows. At each pass you
are causing the reader to lookup the index of the column name. So in my
example, you could cause the reader to determine that ContactID is index 0
for instance, 1000 times minimum.

Instead, either use Bill Vaughn's method and create an Enum, with each
string name of the enum corresponding to the index, or using GetOrdinal
ONCE, outside the loop for ContactID.

If you didth9is:
//ExecuteReader...

if(dr.HasRows){
int ContactID = dr.GetOrdinal("ContractID")
while(dr.Read()){
if (dr.IsDBNull(dr.GetString(ContactId) ||
dr.GetString(dr.GetString(ContactId)== "")
}
}

you'd cut down on the lookups and speed things up dramatically.

Also, i'd enclose the connection in a using block and set the
commandbehavior of the reader to CloseConnection
http://www.knowdotnet.com/articles/schemas2.html . The little things in
aggregate can add up to a WHOLE lot. This
http://weblogs.asp.net/angelsb/archive/2004/08/25/220333.aspx will show you
how bad things can get if you aren't careful.