dbReader closing prematurely

[tshad]

I have a section of code that is giving me an error:
************************************************** ***************
Invalid attempt to Read when reader is closed.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Invalid attempt to Read
when reader is closed.

Source Error:
Line 1701: dbReader =
myDBObject.RunProcedure("GetScreenQuestions",param eters)
Line 1702:trace.warn("Before dbReader.Read")
Line 1703: while dbReader.Read() <-
error is here 2nd time around
Line 1704:trace.warn("inside dbRead.Read")
Line 1705: redim parameters(6)
************************************************** ***************************

My code is:
******************************************
Dim myDbObject as new DbObject()
Dim dbReader As SqlDataReader

....

dbReader = myDBObject.RunProcedure("GetScreenQuestions",param eters)
trace.warn("Before dbReader.Read")
while dbReader.Read()
trace.warn("inside dbRead.Read")
redim parameters(6)
parameters(0) = new
SqlParameter("@ScreenQuestionsTemplateID",SqlDBTyp e.Int)
parameters(1) = new SqlParameter("@Question",SqlDBType.VarChar,250)
parameters(2) = new SqlParameter("@QuestionType",SqlDBType.Char,2)
parameters(3) = new SqlParameter("@SortOrder",SqlDBType.SmallInt)
parameters(4) = new SqlParameter("@Answers",SqlDBType.SmallInt)
parameters(5) = new SqlParameter("@Weight",SqlDBType.SmallInt)
parameters(6) = new SqlParameter("@ScreenTemplateMasterID",SqlDBType.I nt)

parameters(0).Value = dbReader("ScreenQuestionsTemplateID")
parameters(1).Value = dbReader("Question")
parameters(2).Value = dbReader("QuestionType")
parameters(3).Value = dbReader("SortOrder")
parameters(4).Value = dbReader("Answers")
parameters(5).Value = dbReader("Weight")
parameters(6).Value = ScreenTemplateMasterID

Call myDbObject.RunProcedure("CopyScreenTemplateFromTem plate",parameters)
end while
*****************************************

RunProcedure is my own Database Object.

The first RunProcedure passes back a DataReader (dbReader) and it works fine
the first time through.

But apparently the call to RunProcedure is killing or closing dbReader. Why
would that be?

I am not reusing the DataReader, just using doing a Call.

Thanks,

Tom

[Peter Bromberg [C# MVP]]

Tom,
A SqlDataReader is a forward-only "firehose" style data object. This means
you only get to read through it "One Time". After you are done with the
Reader, you need to Close it and optionally close the underlying
SqlConnection if you haven't specified CommandBehavior.CloseConnection.

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"tshad" wrote:

I have a section of code that is giving me an error:
************************************************** ***************
Invalid attempt to Read when reader is closed.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Invalid attempt to Read
when reader is closed.

Source Error:
Line 1701: dbReader =
myDBObject.RunProcedure("GetScreenQuestions",param eters)
Line 1702:trace.warn("Before dbReader.Read")
Line 1703: while dbReader.Read() <-
error is here 2nd time around
Line 1704:trace.warn("inside dbRead.Read")
Line 1705: redim parameters(6)
************************************************** ***************************

My code is:
******************************************
Dim myDbObject as new DbObject()
Dim dbReader As SqlDataReader

....

dbReader = myDBObject.RunProcedure("GetScreenQuestions",param eters)
trace.warn("Before dbReader.Read")
while dbReader.Read()
trace.warn("inside dbRead.Read")
redim parameters(6)
parameters(0) = new
SqlParameter("@ScreenQuestionsTemplateID",SqlDBTyp e.Int)
parameters(1) = new SqlParameter("@Question",SqlDBType.VarChar,250)
parameters(2) = new SqlParameter("@QuestionType",SqlDBType.Char,2)
parameters(3) = new SqlParameter("@SortOrder",SqlDBType.SmallInt)
parameters(4) = new SqlParameter("@Answers",SqlDBType.SmallInt)
parameters(5) = new SqlParameter("@Weight",SqlDBType.SmallInt)
parameters(6) = new SqlParameter("@ScreenTemplateMasterID",SqlDBType.I nt)

parameters(0).Value = dbReader("ScreenQuestionsTemplateID")
parameters(1).Value = dbReader("Question")
parameters(2).Value = dbReader("QuestionType")
parameters(3).Value = dbReader("SortOrder")
parameters(4).Value = dbReader("Answers")
parameters(5).Value = dbReader("Weight")
parameters(6).Value = ScreenTemplateMasterID

Call myDbObject.RunProcedure("CopyScreenTemplateFromTem plate",parameters)
end while
*****************************************

RunProcedure is my own Database Object.

The first RunProcedure passes back a DataReader (dbReader) and it works fine
the first time through.

But apparently the call to RunProcedure is killing or closing dbReader. Why
would that be?

I am not reusing the DataReader, just using doing a Call.

Thanks,

Tom

[tshad]

"Peter Bromberg [C# MVP]" <pb*******@yahoo.nospammin.com> wrote in message
news:C6**********************************@microsof t.com...

Tom,
A SqlDataReader is a forward-only "firehose" style data object. This
means
you only get to read through it "One Time". After you are done with the
Reader, you need to Close it and optionally close the underlying
SqlConnection if you haven't specified CommandBehavior.CloseConnection.
That I understand.

But if I comment out the RunProcedure in the While loop, it goes through it
about 10 times (as there are 10 records).

If I don't, I get the error on the 2nd While statement (which is after the
RunProcedure was run for the first time). It appears to have closed the
dbReader before I have read through it.

Thanks,

Tom
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"tshad" wrote:

I have a section of code that is giving me an error:
************************************************** ***************
Invalid attempt to Read when reader is closed.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Invalid attempt to
Read
when reader is closed.

Source Error:
Line 1701: dbReader =
myDBObject.RunProcedure("GetScreenQuestions",param eters)
Line 1702:trace.warn("Before dbReader.Read")
Line 1703: while dbReader.Read() <-
error is here 2nd time around
Line 1704:trace.warn("inside dbRead.Read")
Line 1705: redim parameters(6)
************************************************** ***************************

My code is:
******************************************
Dim myDbObject as new DbObject()
Dim dbReader As SqlDataReader

....

dbReader = myDBObject.RunProcedure("GetScreenQuestions",param eters)
trace.warn("Before dbReader.Read")
while dbReader.Read()
trace.warn("inside dbRead.Read")
redim parameters(6)
parameters(0) = new
SqlParameter("@ScreenQuestionsTemplateID",SqlDBTyp e.Int)
parameters(1) = new SqlParameter("@Question",SqlDBType.VarChar,250)
parameters(2) = new SqlParameter("@QuestionType",SqlDBType.Char,2)
parameters(3) = new SqlParameter("@SortOrder",SqlDBType.SmallInt)
parameters(4) = new SqlParameter("@Answers",SqlDBType.SmallInt)
parameters(5) = new SqlParameter("@Weight",SqlDBType.SmallInt)
parameters(6) = new
SqlParameter("@ScreenTemplateMasterID",SqlDBType.I nt)

parameters(0).Value = dbReader("ScreenQuestionsTemplateID")
parameters(1).Value = dbReader("Question")
parameters(2).Value = dbReader("QuestionType")
parameters(3).Value = dbReader("SortOrder")
parameters(4).Value = dbReader("Answers")
parameters(5).Value = dbReader("Weight")
parameters(6).Value = ScreenTemplateMasterID

Call
myDbObject.RunProcedure("CopyScreenTemplateFromTem plate",parameters)
end while
*****************************************

RunProcedure is my own Database Object.

The first RunProcedure passes back a DataReader (dbReader) and it works
fine
the first time through.

But apparently the call to RunProcedure is killing or closing dbReader.
Why
would that be?

I am not reusing the DataReader, just using doing a Call.

Thanks,

Tom