CommandBehavior.CloseConnection question

[tshad]

How does the CommandBehavior.CloseConnection work?

I assumed that it is saying when you are done reading all the data, it will
close the connection automatically.

I have it set on one of my SP querys, which does 3 selects - so there are 3
result sets sent back.

In my Database object I do a:

returnReader = command.ExecuteReader( _
CommandBehavior.CloseConnection)

But when I try to read the data and bind them to the dropdownlists, I get
the following error after I have done a bind to the 1st dropdown and am
doing a NextResult() to point to the next set:
**************************************************
Invalid attempt to NextResult when reader is closed.

dbReader.NextResult()
*************************************************

I am doing the following (where the RunProcedure sets up and executes the
reader):

dbReader = myDbObject.RunProcedure("GetPositionDrops1", parameters)
Positions.DataSource = dbReader
Positions.DataValueField = "PositionID"
Positions.DataTextField = "JobTitle"
Positions.DataBind()
Positions.Items.Insert(0, new ListItem("Select from Active & Expired
Jobs",""))

dbReader.NextResult()

JobCategory.DataSource = dbReader
JobCategory.DataValueField = "CategoryCode"
JobCategory.DataTextField = "Category"
JobCategory.DataBind()


Why is the reader getting closed?

Thanks,

Tom

 

[W.G. Ryan - MVP]

When you close the reader, the connection is closed if you use
CommandBehavior.CloseConnection. Where is the connection being opened and
closed? Ifit works for the first one, then it's obviously not being closed
in RunProcedure but what would ultimately close this connection or the
reader? Also, verify NextResult just to be safe, so taht you know there is
in fact a next result coming back (this shoudl be working based on whay you
said, but double check it if you haven't already just to be safe).

 

[tshad]

When you close the reader, the connection is closed if you use
CommandBehavior.CloseConnection. Where is the connection being opened and
closed? Ifit works for the first one, then it's obviously not being
closed in RunProcedure but what would ultimately close this connection or
the reader? Also, verify NextResult just to be safe, so taht you know
there is in fact a next result coming back (this shoudl be working based
on whay you said, but double check it if you haven't already just to be
safe).

I did.

I changed the code to not call the RunProcedure, just to be safe and have
this now:

**************************************************************************
Dim dbReader as SqlDataReader
Dim ConnectionString as String
=System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_ftsolutions")
Dim objConn as New SqlConnection (ConnectionString)
Dim CommandText as String = "GetPositionDrops1"
Dim objCmd as New SqlCommand(CommandText,objConn)
objCmd.CommandType = CommandType.StoredProcedure
with objCmd.Parameters
.Add("@UserID",SqlDbType.Int).value = session("UserID")
end with
objConn.Open()
dbReader = objCmd.ExecuteReader

Positions.DataSource = dbReader
Positions.DataValueField = "PositionID"
Positions.DataTextField = "JobTitle"
Positions.DataBind()
Positions.Items.Insert(0, new ListItem("Select from Active & Expired
Jobs",""))

dbReader.NextResult()

JobCategory.DataSource = dbReader
JobCategory.DataValueField = "CategoryCode"
JobCategory.DataTextField = "Category"
JobCategory.DataBind()
********************************************************************************

This works fine.

But if I change the

dbReader = objCmd.ExecuteReader

to

dbReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)

It gets the error on NextResult().

Nowhere am I closing the Reader directly.

Thanks,

Tom