SqlDataReaded closing unexpectedly

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm having a problem reading from a SqlDataReader accessing SQlServer 2000. Part way within a while ( Read() ) loop, I get an exception with the message "Invalid attempt to read data when reader is closed." However, no where in the code is the reader closed. I've tried extending the connect timeout setting, setting the ExecuteReader's CommandBehavior to CommandBehavior.CloseConnection. These seem to help, but only delays the problem. The code follows. ExceuteQuery() is a method that instantiates a SqlCommand object and calls its ExcuteQuery method--nothing fancy. The exeception gets thrown early (anywhere form 400-600 rows), but the query can return as many as 10,000 rows. Any help will be much appreciated

string SQL = string.Format("select Word1 [Word 1], Word2 [Word 2], freq from ContextBigrams where "
"(Word1 = '{0}' and Pos1 = '{1}' and Pos2 = '{2}')"
"or "
"(Word2 = '{0}' and Pos2 = '{1}' and Pos1 = '{2}')"
word.Replace("'","''"),sourcePos,targetPos)

SqlDataReader rdr = ExecuteQuery(SQL)

DataSet ds = new DataSet()
DataTable dt = ds.Tables.Add("Context words")
dt.Columns.Add("Word",typeof(string))
dt.Columns.Add("Freq",typeof(int))

while ( rdr.Read()

DataRow dr = dt.NewRow()
dt.Rows.Add(dr)
dr["Word"] = ( (string)rdr["Word 1"] == word ? rdr["Word 2"].ToString() : rdr["Word 1"].ToString() )
dr["Freq"] = (int)rdr["freq"]
}

rdr.Close()

Thanks
Jim Carpente
 
I don't know the cause of the problem you are seeing, but wouldn't it be
easier to just adjust the query to return the results, and get the data
right into the datatable?

One thing to try to fix it the way it is now, is to set the timeout on the
command being used to execute the reader (not the connection) to 0.

Jim Carpenter said:
I'm having a problem reading from a SqlDataReader accessing SQlServer
Click to expand...
2000. Part way within a while ( Read() ) loop, I get an exception with the
message "Invalid attempt to read data when reader is closed." However, no
where in the code is the reader closed. I've tried extending the connect
timeout setting, setting the ExecuteReader's CommandBehavior to
CommandBehavior.CloseConnection. These seem to help, but only delays the
problem. The code follows. ExceuteQuery() is a method that instantiates a
SqlCommand object and calls its ExcuteQuery method--nothing fancy. The
exeception gets thrown early (anywhere form 400-600 rows), but the query can
return as many as 10,000 rows. Any help will be much appreciated.
string SQL = string.Format("select Word1 [Word 1], Word2 [Word 2], freq from ContextBigrams where " +
"(Word1 = '{0}' and Pos1 = '{1}' and Pos2 = '{2}')" +
"or " +
"(Word2 = '{0}' and Pos2 = '{1}' and Pos1 = '{2}')",
word.Replace("'","''"),sourcePos,targetPos);

SqlDataReader rdr = ExecuteQuery(SQL);

DataSet ds = new DataSet();
DataTable dt = ds.Tables.Add("Context words");
dt.Columns.Add("Word",typeof(string));
dt.Columns.Add("Freq",typeof(int));

while ( rdr.Read() )
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
dr["Word"] = ( (string)rdr["Word 1"] == word ? rdr["Word 2"].ToString() : rdr["Word 1"].ToString() );
dr["Freq"] = (int)rdr["freq"];
}

rdr.Close();


Thanks,
Jim Carpenter
Click to expand...
 
Hi Jim,

Is it possible that one of fields you are reading contains null value?
For fun, try commenting the code inside while (rdr.Read()) loop and see what
happens.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Jim Carpenter said:
I'm having a problem reading from a SqlDataReader accessing SQlServer
Click to expand...
2000. Part way within a while ( Read() ) loop, I get an exception with the
message "Invalid attempt to read data when reader is closed." However, no
where in the code is the reader closed. I've tried extending the connect
timeout setting, setting the ExecuteReader's CommandBehavior to
CommandBehavior.CloseConnection. These seem to help, but only delays the
problem. The code follows. ExceuteQuery() is a method that instantiates a
SqlCommand object and calls its ExcuteQuery method--nothing fancy. The
exeception gets thrown early (anywhere form 400-600 rows), but the query can
return as many as 10,000 rows. Any help will be much appreciated.
string SQL = string.Format("select Word1 [Word 1], Word2 [Word 2], freq from ContextBigrams where " +
"(Word1 = '{0}' and Pos1 = '{1}' and Pos2 = '{2}')" +
"or " +
"(Word2 = '{0}' and Pos2 = '{1}' and Pos1 = '{2}')",
word.Replace("'","''"),sourcePos,targetPos);

SqlDataReader rdr = ExecuteQuery(SQL);

DataSet ds = new DataSet();
DataTable dt = ds.Tables.Add("Context words");
dt.Columns.Add("Word",typeof(string));
dt.Columns.Add("Freq",typeof(int));

while ( rdr.Read() )
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
dr["Word"] = ( (string)rdr["Word 1"] == word ? rdr["Word 2"].ToString() : rdr["Word 1"].ToString() );
dr["Freq"] = (int)rdr["freq"];
}

rdr.Close();


Thanks,
Jim Carpenter
Click to expand...
 
Back
Top