Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

Open DataReader??

Hi folks!

I was under the impression that by using 'ExecuteReader(CommandBehavior.CloseConnection)" that all my DataReaders would close....but I was wrong I guess because now I'm getting the dreaded:

There is already an open DataReader associated with this Connection which must be closed first.

Can anyone see where I'm going wrong in the code below??

Thanks!

****************************************

<Script runat="server">
      
      dim Connect as SQLConnection = New SQLConnection
      dim Connect2 AS OLEDBConnection = New OLEDBConnection
      
      dim ConnectString As String
      dim connectString2 As String
      dim CmdSelect AS SqlCommand
      dim IntAreaID As Integer
      Dim RegionID AS Integer      

Sub Page_load(sender as Object, e as EventArgs)
      
      If session("Role") = "" then
            Session("StartPage")= Request.ServerVariables("SCRIPT_NAME")
            response.redirect("/AccessToHousing/login.aspx")
      end if
      
      RegionID=Session("RegionID")      

      ConnectString="myvalues;"
      connectstring2="Provider=SQLOLEDB;myvalues;"
      
      Connect.ConnectionString = ConnectString
      Connect2.ConnectionString= ConnectString2

      If Not IsPostBack then
            BindMasterGrid(RegionID)
          BindDetailGrid(intAreaID)
      end if

End Sub

Sub BindMasterGrid(RegionID As Integer)

      CmdSelect = New SqlCommand("Select * FROM tblAreas WHERE RegionID=@RegionID", Connect)
      CmdSelect.Parameters.Add("@RegionID", RegionID)
      Connect.Open()

      dgrdAreas.DataSource = cmdSelect.ExecuteReader(CommandBehavior.CloseConnection)
      dgrdAreas.DataBind()

      Connect.close
      
End Sub

Sub BindDetailGrid(intAreaID AS Integer)
      dim PropsCommand AS SqlCommand
      dim PropsSelect AS String
      PropsSelect = "Select * FROM tblResidences WHERE RegionID=RegionID AND HousingAreaID=@AreaID Order by HousingDateRegistered DESC"
      PropsCommand = New SQLCommand (PropsSelect, connect)
      PropsCommand.Parameters.Add ("@AreaID", intAreaID)
      
      Connect.Open()

      PropsGrid.DataSource = PropsCommand.ExecuteReader(CommandBehavior.CloseConnection)
      PropsGrid.DataBind()

      Connect.close
      
End Sub

Sub dgrdAreas_ItemCommand (s as Object, e As DataGridCommandEventArgs)
      intAreaID = dgrdAreas.DataKeys (e.Item.ItemIndex)
      dgrdAreas.SelectedIndex = e.Item.ItemIndex
      intAreaID = e.Item.ItemIndex
      BindDetailGrid(intAreaID)
End Sub

Sub PropsGrid_EditCommand(s As Object, e As DataGridCommandEventArgs)
      PropsGrid.editItemIndex = e.Item.ItemIndex
      intAreaID = dgrdAreas.DataKeys (e.Item.ItemIndex)
      propsGrid.databind
      BindDetailGrid(intAreaID)
End Sub

Sub PropsGrid_CancelCommand(s As Object, e As DataGridCommandEventArgs)
      intAreaID = dgrdAreas.DataKeys (e.Item.ItemIndex)
      PropsGrid.EditItemIndex = -1
      BindDetailGrid(intAreaID)
      
End Sub

sub PropsGrid_UpdateCommand(s As Object, f As DataGridCommandEventArgs)
      
   
         dim HousingID as Integer
    dim HousingDateRegistered as Textbox = CType(f.Item.FindControl("HousingDateRegistered"), Textbox)
    dim HousingPrivate as RadioButtonList = CType(f.Item.FindControl("HousingPrivate"), RadioButtonList)
    dim HousingAreaID as DropDownList = CType(f.Item.FindControl("HousingAreaID"), DropdownList)
    dim HousingResidenceTypeID as Dropdownlist =CType(f.Item.FindControl("HousingResidencetypeid"), Dropdownlist)
    dim HousingContact as textbox =CType(f.Item.FindControl("HousingContact"), Textbox)
    dim Address as textbox =CType(f.Item.FindControl("Address"), Textbox)
    dim Housing1stPhone as Textbox = CType(f.Item.FindControl("Housing1stPhone"), Textbox)
      dim HousingAvaildate as textbox =CType(f.Item.FindControl("HousingAvaildate"), Textbox)
    dim HousingPrice as Textbox = CType(f.Item.FindControl("HousingPrice"), Textbox)
    dim HousingStatus as RadioButtonList = CType(f.Item.FindControl("HousingStatus"), RadioButtonList)
    dim Util as RadioButtonList = CType(f.Item.FindControl("Util"), RadioButtonList)
    dim Heat as RadioButtonList = CType(f.Item.FindControl("Util"), RadioButtonList)
      dim Pets as RadioButtonList = CType(f.Item.FindControl("Pets"), RadioButtonList)
      dim Smoke as RadioButtonList = CType(f.Item.FindControl("Smoke"), RadioButtonList)
       dim Laundry as RadioButtonList = CType(f.Item.FindControl("Laundry"), RadioButtonList)
      dim Park as RadioButtonList = CType(f.Item.FindControl("Park"), RadioButtonList)
       dim Furnished as RadioButtonList = CType(f.Item.FindControl("Furnished"), RadioButtonList)
       dim HousingComments as Textbox = CType(f.Item.FindControl("HousingComments"), Textbox)
 
   
   dim strHDateReg As String
   dim strHAvailDate As String
   dim strHContact As String
   dim strHResTypeID As String
   dim strHAddress As String
   dim strHPhone As String
   dim strPrivate As String
   dim strPrice As String
   dim strUtil As String
   dim strLaundry As String
   dim strHeat As String
   dim strPark As String
   dim strFurnished As String
   dim strPets As String
   dim strSmoke As String
   dim strStatus As String
   dim strHComments As String
   dim strHAreaID As String
   
   
   
    strHDateReg = HousingDateRegistered.text
    strHPhone = Housing1stPhone.text
    strPrivate = HousingPrivate.selectedItem.value
         strHAvailDate = HousingAvailDate.text
    strHContact = HousingContact.text
    strHResTypeID = HousingResidenceTypeID.selecteditem.value
    strHAddress = Address.text
    strPrice =HousingPrice.text
    strUtil = Util.selectedItem.Value
    strLaundry = Laundry.selectedItem.Value
    strHeat = Heat.selectedItem.Value
    strPark = Park.selectedItem.Value
    strFurnished = Furnished.selectedItem.Value
    strPets = Pets.selectedItem.Value
    strSmoke =  Smoke.selectedItem.Value
    strStatus = HousingStatus.selectedItem.Value
    strHComments = HousingComments.text
       strHAreaID = HousingAreaID.SelectedItem.value
    HousingID = PropsGrid.DataKeys(f.Item.ItemIndex)
 
   dim cmd As OLEDBCommand
   cmd = New OleDBCommand("sp_Units_Update", Connect2)
   cmd.CommandType = CommandType.StoredProcedure
   cmd.Parameters.Add("@HousingID", HousingID)
   cmd.Parameters.Add("@HousingDateRegistered", strHDateReg)
   cmd.Parameters.Add("@HousingPrivate", strPrivate)
   cmd.Parameters.Add("@HousingAreaID", strHAreaID)
   cmd.Parameters.Add("@HousingResidenceTypeID", strHResTypeID)
   cmd.Parameters.Add("@HousingContact", strHContact)
   cmd.Parameters.Add("@Address", strHAddress)
   cmd.Parameters.Add("@Housing1stPhone", strHPhone)
   cmd.Parameters.Add("@HousingAvailDate", strHAvailDate)
   cmd.Parameters.Add("@HousingPrice", strPrice)
   cmd.Parameters.Add("@HousingStatus", strStatus)              
   cmd.Parameters.Add("@Util", strUtil)
   cmd.Parameters.Add("@Laundry", strLaundry)
   cmd.Parameters.Add("@Heat", strHeat)
   cmd.Parameters.Add("@Park", strPark)
   cmd.Parameters.Add("@Furnished", strFurnished)
   cmd.Parameters.Add("@Pets", strPets)
   cmd.Parameters.Add("@Smoke", strSmoke)
   cmd.Parameters.Add("@HousingComments", strHComments)
     
 
   connect2.open()
   cmd.ExecuteNonQuery()
   connect2.close()
      intAreaID = dgrdAreas.DataKeys (f.Item.ItemIndex)
      PropsGrid.EditItemIndex = -1
      BindDetailGrid(intAreaID)
   
   
End Sub

   
'*******************************************
 
Public Sub DG_ItemDataBound(s as object, e as DataGridItemEventArgs)
   If e.Item.ItemType <> ListItemType.Header And e.Item.ItemType <> ListItemType.Footer Then
         Dim cboStateTemp2 As DropDownList = CType(e.Item.FindControl("HousingAreaID"), DropDownList)
            cboStateTemp2.SelectedIndex = cboStateTemp2.Items.IndexOf(cboStateTemp2.Items.FindByValue(e.Item.DataItem("HousingAreaID")))

      Dim cboStateTemp As DropDownList = CType(e.Item.FindControl("HousingResidenceTypeID"), DropDownList)
            cboStateTemp.SelectedIndex = cboStateTemp.Items.IndexOf(cboStateTemp.Items.FindByValue(e.Item.DataItem("HousingResidenceTypeID")))
   End If  
End Sub

'*******************************************
'*******************************************

Private Function BindState()
         Dim myCommand As SqlCommand = New SqlCommand("sp_listareas_display", Connect)

        myCommand.CommandType = CommandType.StoredProcedure
            'myCommand.Parameters.Add("@RegionId", RegionID)

        Connect.Open()
        Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        Connect.close
End Function
'******************************************
'*******************************************
   
Private Function BindState2()
        Dim myCommand As SqlCommand = New SqlCommand("sp_listrestype_display", Connect)

        myCommand.CommandType = CommandType.StoredProcedure
            'myCommand.Parameters.Add("@RegionId", RegionID)

        Connect.Open()
        Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        Connect.close
End Function

'*******************************************
0
ClassyLinks
Asked:
ClassyLinks
  • 16
  • 13
  • 3
  • +1
1 Solution
 
AerosSagaCommented:
each command/connection should be unique, that is you should commandthis, commmandthat, connectinthis, connectionthat.  If not you will run into this issue.  Just give each command/connection a unique name.

Aeros
0
 
AerosSagaCommented:
or at least the concurrent ones that are causing the exception.
0
 
nauman_ahmedCommented:
Where you are getting the exception?

-Nauman.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ClassyLinksAuthor Commented:
The exception is thrown:
Line 299:        Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)

Which is part of:

Private Function BindState()
         Dim myCommand As SqlCommand = New SqlCommand("sp_listareas_display", Connect)

        myCommand.CommandType = CommandType.StoredProcedure
            'myCommand.Parameters.Add("@RegionId", RegionID)

        'Connect.Open()
        Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        'Connect.close
End Function
0
 
praneethaCommented:
'Connect.Open()
        Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        'Connect.close
did u comment connect.open and connect.close

and you have return before connect.close..so connect.close is never executed,,,

0
 
ClassyLinksAuthor Commented:
If I dont' comment out the connect.open() I get:

The connection is already Open (state=Open).
0
 
praneethaCommented:
bcz in the previous code...it was never closed..since u have return before close...


so it says it has it open...

and where r u calling this bindstate()..i don't see it in the code



0
 
nauman_ahmedCommented:
I will say that you better write a function that returns a DataSet so that you dont have to open the connection multiple times:

Funtion GetRecords
 Dim conn As New OleDbConnection(connection)
 Dim adapter As New OleDbDataAdapter()
 Dim dataSet as New DataSet
 conn.Open
 adapter.SelectCommand = new OleDbCommand(query, conn)
 adapter.Fill(dataset)
 connection.close
 Return dataset
End Function

This one function will save you a lot. Give it a try.

Best, Nauman.
0
 
nauman_ahmedCommented:
You can modify the above method and send your SQL query as a parameter or declare a global variable and access it in your function.

-Nauman.
0
 
praneethaCommented:
yes i don't think you can return a datareader and read it elsewhere...since it would require open connection to read from a datareader
0
 
ClassyLinksAuthor Commented:
bindstate() is called directly from a ddl as the datasource property.

This code works great on two other pages.....just breaking down here  :((
0
 
praneethaCommented:
i think you should have either datatable or dataset as datasource i am not sure if you can do the reader...

but u say it worked somewhere else..

i guess if you make this the firstpage to execute it will work here too..may be it just needs that connection close...since you have return statement before close..

make this firstpage to run and see if this works...

0
 
ClassyLinksAuthor Commented:
??

You've lost me Praneetha.

How do I make sure that all the datareaders close after they execute?
0
 
praneethaCommented:
try
            {
                myConnection.Open();  
                myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException)
            {
                myReader = null;
            }
            catch (Exception)
            {
                myReader = null;
            }/*
            finally
            {
                if (myConnection.State == ConnectionState.Open)
                    myConnection.Close();
            }*/
            return myReader;
0
 
praneethaCommented:
convet that to vb.net ...sorry
0
 
ClassyLinksAuthor Commented:
should that go just anywhere on the page??
0
 
praneethaCommented:
Private Function BindState()
         Dim myCommand As SqlCommand = New SqlCommand("sp_listareas_display", Connect)

        myCommand.CommandType = CommandType.StoredProcedure
          'myCommand.Parameters.Add("@RegionId", RegionID)
SqlDataReader myreader=null;
//vb.net part    
try
            {
                myConnection.Open();  
                myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException)
            {
                myReader = null;
            }
            catch (Exception)
            {
                myReader = null;
            }/*
            finally
            {
                if (myConnection.State == ConnectionState.Open)
                    myConnection.Close();
            }*/
            return myReader;

End Function
0
 
ClassyLinksAuthor Commented:
'SqlDataReader' is a type and cannot be used as an expression.
Line 298:SqlDataReader myreader=null


This is crazy.  There has to be a simple way to close a reader after it is used.
0
 
praneethaCommented:
i mean don't u have to do this in vb.net


 Dim myReader As SqlDataReader;
0
 
ClassyLinksAuthor Commented:
I have no idea what you are trying to say.
0
 
praneethaCommented:
Dim myReader As SqlDataReader;
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vastmTryCatchFinally.asp - try catch format in vb.net
i am sorry i am not a vb.net person...but i am sure...it's not very difficult...just change the if loop,try catch syntax
//vb.net part    
try
            {
                myConnection.Open();  
                myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException)
            {
                myReader = null;
            }
            catch (Exception)
            {
                myReader = null;
            }/*
            finally
            {
                if (myConnection.State == ConnectionState.Open)
                    myConnection.Close();
            }*/
            return myReader;
0
 
praneethaCommented:
Private Function BindState()
         Dim myCommand As SqlCommand = New SqlCommand("sp_listareas_display", Connect)

        myCommand.CommandType = CommandType.StoredProcedure
          'myCommand.Parameters.Add("@RegionId", RegionID)
Dim myReader As SqlDataReader;
myReader=null;
//vb.net part    
Try
           
                myConnection.Open();  
                myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
           
            Catch ex1 as SqlException
           
                myReader = null;
           
            Catch ex As Exception

           
                myReader = null;
            /*
            Finally
           
                if (myConnection.State == ConnectionState.Open)
                    myConnection.Close();
            */
end try
            return myReader;

End Function


i guess like that...

0
 
ClassyLinksAuthor Commented:
That's not going to work.

BC30822: 'null' is not declared. 'Null' constant is no longer supported; use 'System.DBNull' instead.

Line 299:myReader=null
0
 
praneethaCommented:
comment all the lines where you have myReader=null...does it give any error
0
 
ClassyLinksAuthor Commented:
Now...it won't bind the datagrid at all because:

Invalid attempt to Read when reader is closed.

Line 63:       PropsGrid.DataBind()



This is driving me crazy.....
0
 
praneethaCommented:
best way is to use dataset..since datareader needs the connection open as i told you...

Private Function BindState()
   Dim SELECT_STRING As String =  "sp_listareas_display"
Dim CONNECT_STRING As String = _
    "connection string"
Dim m_DataSet As DataSet;
Dim data_adapter As SqlDataAdapter

    ' Create the SqlDataAdapter.
    data_adapter = New SqlDataAdapter(SELECT_STRING, _
        CONNECT_STRING)

      ' Fill the DataSet.
    m_DataSet = New DataSet()
    data_adapter.Fill(m_DataSet)
     return m_DataSet.Tables[0];or return ds;

End Function
0
 
ClassyLinksAuthor Commented:
That is not a viable solution for me.  There is NOTHING wrong with the datareader....all I need to know is how to close the stupid things.
0
 
praneethaCommented:
Invalid attempt to Read when reader is closed.


datagrid.datasource=reader//returned from function
datagrid.databind();
you can't use reader like that

reader always have to inserted in between conn.open and conn.close...

either you have to bind the datagrid inside the bind function...before conn.close...

or just use dataset...sorry

why using a dataset not a viable solution for you...is there any reason...

or you can loop thru datareader and fill then values into datatable..and bind that to datagrid
0
 
ClassyLinksAuthor Commented:
Not a good solution because it is working on all the other pages.  There just has to be something missing on this page.  I'm not going to totally rewrite code just for the sake of rewriting.....the datareader will work, if I can just find out how to close each one after they are being used.
0
 
praneethaCommented:
conn.open
datagrid.datasource=myCommand.ExecuteReader(CommandBehavior.CloseConnection)
datagrid.databind()
conn.close

you can do that if datagrid is available on the same page



0
 
ClassyLinksAuthor Commented:
I''m already doing that.

<sigh!>

Ok....What if I used the DataSet for my BindDetailGrid?
0
 
praneethaCommented:
you can and then u don't have to worry about keeping the connection open like datareader
0
 
ClassyLinksAuthor Commented:
Rather than continueing to beat my head against a wall....went with the dataset.

Thanks for you help & patience.
0
 
praneethaCommented:
you are welcome..good luck
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 16
  • 13
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now