Results 1 to 11 of 11

Thread: Sucking tree view loop

  1. #1

    Thread Starter
    Fanatic Member sridharavijay's Avatar
    Join Date
    Sep 2002
    Location
    http://www.vijaysridhara.in
    Posts
    589

    Sucking tree view loop

    Hi...
    The following code worked with 2.0 Framework, now with NPGSQL 2.0.11 and framework 4.0, this is erroring out despite Pooling=False for the connection. Do you get any clue?

    The underlying logic is that add nodes to treeview; for each node added, immediately get subnodes and populate...


    vb Code:
    1. Private Sub LoadRequirements(ByVal parnode As TreeNode)
    2.         Try
    3.             Dim CMd1 As NpgsqlCommand
    4.             CMd1 = New NpgsqlCommand
    5.             Dim Q As String = "SELECT REQID,SUMMARY,ISCHANGE,DEPENDENTBRS FROM FACTUALDATA.REQUIREMENTS WHERE PROJECT='{0}' AND BRID='{1}' AND PARENT='{2}' ORDER BY REQID"
    6.             If parnode Is Nothing Then
    7.                 CMd1.CommandText = String.Format(Q, Selected_project, lsvModules.SelectedItems(0).Text.Trim, lsvModules.SelectedItems(0).Text.Trim)
    8.             Else
    9.  
    10.                 CMd1.CommandText = String.Format(Q, Selected_project, lsvModules.SelectedItems(0).Text.Trim, parnode.Tag)
    11.             End If
    12.  
    13.             CMd1.Connection = CON
    14.             Dim R1 As NpgsqlDataReader = CMd1.ExecuteReader
    15.             While R1.Read
    16.                 Dim nd As New TreeNode
    17.                 With nd
    18.                     .Text = R1(0).ToString.Trim & " => " & R1(1).ToString.Trim
    19.                     If R1(2).ToString.Trim = "Y" And String.IsNullOrEmpty(R1(3).ToString.Trim) Then
    20.                         .ImageIndex = 12
    21.                         .SelectedImageIndex = 12
    22.                     ElseIf R1(2).ToString.Trim = "Y" And String.IsNullOrEmpty(R1(3).ToString.Trim) = False Then
    23.                         .ImageIndex = 14
    24.                         .SelectedImageIndex = 14
    25.                     ElseIf R1(2).ToString.Trim = "N" And String.IsNullOrEmpty(R1(3).ToString.Trim) Then
    26.                         .ImageIndex = 11
    27.                         .SelectedImageIndex = 11
    28.                     ElseIf R1(2).ToString.Trim = "N" And String.IsNullOrEmpty(R1(3).ToString.Trim) = False Then
    29.                         .ImageIndex = 13
    30.                         .SelectedImageIndex = 13
    31.                     End If
    32.                     .Tag = R1(0).ToString.Trim
    33.                     If parnode Is Nothing Then
    34.                         trvItems.Nodes.Add(nd)
    35.                     Else
    36.                         parnode.Nodes.Add(nd)
    37.                     End If
    38.                 End With
    39.  
    40.                 LoadRequirements(nd)
    41.                 nd.ExpandAll()
    42.             End While
    43.             R1.Close()
    44.             R1.Dispose()
    45.             'CM1.Dispose()
    46.         Catch ex As Exception
    47.             DE(ex)
    48.         End Try
    49.     End Sub

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,296

    Re: Sucking tree view loop

    If only there was some way for us to know what the actual error is.

  3. #3

    Thread Starter
    Fanatic Member sridharavijay's Avatar
    Join Date
    Sep 2002
    Location
    http://www.vijaysridhara.in
    Posts
    589

    Re: Sucking tree view loop

    Quote Originally Posted by jmcilhinney View Post
    If only there was some way for us to know what the actual error is.
    Ah sorry... its about DataReader being active

    see the attached image
    Attached Images Attached Images  

  4. #4
    Frenzied Member
    Join Date
    Jan 2010
    Location
    Connecticut
    Posts
    1,687

    Re: Sucking tree view loop

    When you ask for help with an error you need to not only tell us the error but also show us the line that is causing it.
    VB6 Library

    If I helped you then please help me and rate my post!
    If you solved your problem, then please mark the post resolved

  5. #5
    Frenzied Member
    Join Date
    Jan 2010
    Location
    Connecticut
    Posts
    1,687

    Re: Sucking tree view loop

    The error is telling you the problem. You did not close a reader before opening another one. You can only have one open at a time per connection.
    VB6 Library

    If I helped you then please help me and rate my post!
    If you solved your problem, then please mark the post resolved

  6. #6

    Thread Starter
    Fanatic Member sridharavijay's Avatar
    Join Date
    Sep 2002
    Location
    http://www.vijaysridhara.in
    Posts
    589

    Re: Sucking tree view loop

    Quote Originally Posted by MarMan View Post
    When you ask for help with an error you need to not only tell us the error but also show us the line that is causing it.
    Obviously there is only one line where Datareader gets executed... however for the sake of elaboration.. the line
    vb Code:
    1. Dim R1 As NpgsqlDataReader = CMd1.ExecuteReader
    errors out second time when the function is self-referenced.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,296

    Re: Sucking tree view loop

    Quote Originally Posted by sridharavijay View Post
    Obviously there is only one line where Datareader gets executed... however for the sake of elaboration.. the line
    vb Code:
    1. Dim R1 As NpgsqlDataReader = CMd1.ExecuteReader
    errors out second time when the function is self-referenced.
    I'm not sure whether you're pointing out the issue or still asking the question. As has been stated, the error message tells you what the issue is: you can't open a second data reader on a connection. If you must use multiple data readers then you must use multiple connections. I don't think that you need multiple data readers though. Why not just get all the data in one go into a DataTable using a single data reader or a data adapter? You can then use the Select method of that table to get specific records as required.

    Also, don;t use String.Format to insert values into SQL code. Follow the Blog link in my signature and check out my post on ADO.NET Parameters to learn the proper way to do it.

  8. #8

    Thread Starter
    Fanatic Member sridharavijay's Avatar
    Join Date
    Sep 2002
    Location
    http://www.vijaysridhara.in
    Posts
    589

    Re: Sucking tree view loop

    But it doesn't talk about the connection, does it? It only says the command already has a datareader active, whereas the command object is also a new one. The other suggestions, though I would consider to follow in my code... I actually did not want to touch the code existing which was a part of 2.0... so I wanted to know why is it that way for 4.0... ?


    Thank you

  9. #9
    PowerPoster Radjesh Klauke's Avatar
    Join Date
    Dec 2005
    Location
    Sexbierum (Netherlands)
    Posts
    2,244

    Re: Sucking tree view loop

    Jm is correct, but then you can always close the reader (reader.close), before you open a new one


    If you found my post helpful, please rate it.

    Codebank Submission: FireFox Browser (Gecko) in VB.NET, Load files, (sub)folders treeview with Windows icons

  10. #10

    Thread Starter
    Fanatic Member sridharavijay's Avatar
    Join Date
    Sep 2002
    Location
    http://www.vijaysridhara.in
    Posts
    589

    Re: Sucking tree view loop

    Quote Originally Posted by Radjesh Klauke View Post
    Jm is correct, but then you can always close the reader (reader.close), before you open a new one
    Ah.. in this case it doesnt work... the next reader is created before the current reader is traversed entirely.

    JM, however I need to read the NPGSQL documentation if the features are changed in the latest 2.0.11 release (Or is it true for any other connectors too, for example MSSQL, MySQL, Oracle, DB2 etc)?

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,296

    Re: Sucking tree view loop

    It is true for basically all ADO.NET providers that only one data reader can be open over any particular connection at a time, unless that provider has a specific feature to do otherwise, e.g. MARS for SQL Server.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width