Solved

Open DataReader??

Posted on 2004-10-06
34
678 Views
Last Modified: 2008-01-16
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
Comment
Question by:ClassyLinks
  • 16
  • 13
  • 3
  • +1
34 Comments
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12237706
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
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12237710
or at least the concurrent ones that are causing the exception.
0
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 12237730
Where you are getting the exception?

-Nauman.
0
 
LVL 7

Author Comment

by:ClassyLinks
ID: 12237749
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
 
LVL 15

Expert Comment

by:praneetha
ID: 12237789
'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
 
LVL 7

Author Comment

by:ClassyLinks
ID: 12237836
If I dont' comment out the connect.open() I get:

The connection is already Open (state=Open).
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12237887
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
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 12237918
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
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 12237951
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
 
LVL 15

Expert Comment

by:praneetha
ID: 12237966
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
 
LVL 7

Author Comment

by:ClassyLinks
ID: 12238029
bindstate() is called directly from a ddl as the datasource property.

This code works great on two other pages.....just breaking down here  :((
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12238127
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
 
LVL 7

Author Comment

by:ClassyLinks
ID: 12238170
??

You've lost me Praneetha.

How do I make sure that all the datareaders close after they execute?
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12238250
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
 
LVL 15

Expert Comment

by:praneetha
ID: 12238259
convet that to vb.net ...sorry
0
 
LVL 7

Author Comment

by:ClassyLinks
ID: 12238296
should that go just anywhere on the page??
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12238327
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 7

Author Comment

by:ClassyLinks
ID: 12238408
'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
 
LVL 15

Expert Comment

by:praneetha
ID: 12238435
i mean don't u have to do this in vb.net


 Dim myReader As SqlDataReader;
0
 
LVL 7

Author Comment

by:ClassyLinks
ID: 12238549
I have no idea what you are trying to say.
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12238605
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
 
LVL 15

Expert Comment

by:praneetha
ID: 12238643
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
 
LVL 7

Author Comment

by:ClassyLinks
ID: 12238770
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
 
LVL 15

Expert Comment

by:praneetha
ID: 12238816
comment all the lines where you have myReader=null...does it give any error
0
 
LVL 7

Author Comment

by:ClassyLinks
ID: 12238897
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
 
LVL 15

Accepted Solution

by:
praneetha earned 250 total points
ID: 12239006
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
 
LVL 7

Author Comment

by:ClassyLinks
ID: 12239049
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
 
LVL 15

Expert Comment

by:praneetha
ID: 12239100
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
 
LVL 7

Author Comment

by:ClassyLinks
ID: 12239325
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
 
LVL 15

Expert Comment

by:praneetha
ID: 12239376
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
 
LVL 7

Author Comment

by:ClassyLinks
ID: 12239711
I''m already doing that.

<sigh!>

Ok....What if I used the DataSet for my BindDetailGrid?
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12239739
you can and then u don't have to worry about keeping the connection open like datareader
0
 
LVL 7

Author Comment

by:ClassyLinks
ID: 12239854
Rather than continueing to beat my head against a wall....went with the dataset.

Thanks for you help & patience.
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12239881
you are welcome..good luck
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now