SqlDataReader - Here's one to figure out?

[BobotheBugbear]

Creating a menu system from a DB that can have a submenu.

So, I figured I woudl just create to 2 sqldatareaders and have in inside the other . 1 the parent and the 2nd the child.

Dim objDataReader_parent As SqlDataReader
Dim objDataReader_child As SqlDataReader

And assign the child the parent values so they contain the same values since my stored procedure returns everything (main table linked with sub menu table by parent ID) (see attachment. this is the result of th query).

objDataReader_parent = objSqlCommand.ExecuteReader(Data.CommandBehavior.CloseConnection)
objDataReader_child = objDataReader_parent

Now when I generate the menus, they come out great except that I am missing some menu items in both the menu and submenus. First my count is off by 1 and then 2 and finally 3 where it stops (1 thing missing, then 2 missing and finally 3).

objDataReader_parent = objSqlCommand.ExecuteReader(Data.CommandBehavior.CloseConnection)
objDataReader_child = objDataReader_parent

Do While objDataReader_parent.Read()
Dim parentNode As New MenuItem

Dim parentID As Integer = objDataReader_parent.GetValue(0)

If IsDBNull(objDataReader_parent.GetValue(5)) Then
parentNode.NavigateUrl = objDataReader_parent.GetValue(2) & objDataReader_parent.GetValue(0)
parentNode.Text = objDataReader_parent.GetValue(1)
parentNode.Value = objDataReader_parent.GetValue(1)
mainMenu.Items.Add(parentNode)
End If

If Not IsDBNull(objDataReader_parent.GetValue(5)) Then
parentNode.NavigateUrl = objDataReader_parent.GetValue(2) & objDataReader_parent.GetValue(0)
parentNode.Text = objDataReader_parent.GetValue(1)
parentNode.Value = objDataReader_parent.GetValue(1)
mainMenu.Items.Add(parentNode)

Do While objDataReader_child.Read()
If Not IsDBNull(objDataReader_child.GetValue(5)) Then
If parentID = objDataReader_child.GetValue(5) Then
Dim childNode As New MenuItem
childNode.NavigateUrl = objDataReader_child.GetValue(7) & objDataReader_child.GetValue(4)
childNode.Text = objDataReader_child.GetValue(6)
childNode.Value = objDataReader_child.GetValue(6)
parentNode.ChildItems.Add(childNode)
Else
Exit Do
End If
End If
Loop
End If

Loop

Now, I'm thinking I won't be able to use a datareader for this specific purpose , yet it works so nice and is really fast. If there was a way for the parent node not to increment by 1 when going through the child node that would work. Anyone know of a way to force a datareader to only increment when you tell it to or have another idea?

Oh, this is 2.0 and I can't use the new stuff since the URL is the same, except for adding a different ID at the end or at least it didn't seem I could.

thanks

[nhanson]

I'm not sure I understand exactly what you are saying... but looking at your code alone I would say that I think your issue is that you incorrectly believe you have two DataReader objects (parent and child). I don't believe this is the case. Instead what is happening is you are creating a DataReader object and you are storing references to that SAME object in two different variables. This means every time you are calling Read() on one it is also advancing the other, because they are in fact one in the same. Unless it's changed in 2.0 (I haven't checked) you cannot have more than one DataReader open at a time.

[BobotheBugbear]

I was kind of thinking the same thing was happening. I was considering opening up a whole new connection for the child datareader but this opens up a whole new cans of worms.

I think I read somewhere that you can't have a datareader inside of a datareader even if they use different connections as the internal would cause the external to advance even if they where different content. Anywone confirm this or did my marbles roll away on the way to the office?

[nhanson]

I haven't spent a great deal of time trying to get around the one at a time DataReader limit so I'm not sure if multiple connections works or not. If I was trying to make what you are I'd probably just create a DataTable and Fill is using an SqlDataAdapter. Then you can us that table to do whatever you want and you don't have to worry about the forward only or one at a time DataReader issues. DataReaders are great but their speed comes with limitations and not everything works with those limitations.

[BobotheBugbear]

Yeah. That is what I was kind of thinking. Would give more flexibility. Appreciate your help.