Slightly Complex DataReader Question

[Guest]

Guys, I am trying to use a nested While dtr.Read() over 2 databases(.sdf) in order to retrieve 2 rows(prob bad design - i know!) from a table named OrderDtl. I then fill my ListView based on the results. The code i use is as below. However, the 1st datareader seems to only return 1 row even though my SQLCE select returns 2 rows. I am stumped
I may goto dataset, adapter and row but would love to use the direct SQLCE command to do this. Can anybody help me please

Private Sub btnByItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnByItem.Clic
lvSumGroup.Visible() = Fals
lvSumItem.Visible() = Tru
If lvSumItem.Items.Count > 0 The
lvSumItem.Items.Clear(
End I

Dim item As ListViewIte
Dim item1 As ListViewIte

Dim strSQL As String = "SELECT QUANTITY, ITEMNMBR" &
" FROM OrderDtl WHERE REFRENCE = '" + txtHdrRefrence.Text + "'

Dim cmdSQL As New SqlCeComman
cmdSQL.CommandText = strSQ
cmdSQL.CommandType = CommandType.Tex

If VerifyDBConnection(OrdersDBconnection) = False The
Retur
End I

Dim TmpItemNmbr As Strin
Dim TmpItemQty As Strin
cmdSQL.Connection = OrdersDBconnectio
Tr
Dim dtr As SqlCeDataReader = cmdSQL.ExecuteReader(CommandBehavior.Default
While (dtr.Read()
'item = New ListViewItem(dtr.GetSqlInt32(0).ToString
TmpItemQty = (dtr.GetSqlInt32(0).ToString
TmpItemNmbr = (dtr.GetString(1)
MessageBox.Show(TmpItemQty
While (dtr.Read()
Dim strSQL1 As String = "SELECT ITEMDESC" &
" FROM Products WHERE ITEMNMBR = '" + TmpItemNmbr + "'
Dim cmdSQL1 As New SqlCeComman
cmdSQL1.CommandText = strSQL
cmdSQL1.CommandType = CommandType.Tex
If VerifyDBConnection(GenericDBconnection) = False The
Retur
End I
cmdSQL1.Connection = GenericDBconnectio
Tr
Dim dtr1 As SqlCeDataReader = cmdSQL1.ExecuteReader(CommandBehavior.Default
While (dtr1.Read()
MessageBox.Show(TmpItemNmbr
item1 = New ListViewItem(TmpItemQty.ToString
item1.SubItems.Add(dtr1.GetString(0).ToString
item1.SubItems.Add(TmpItemQty
lvSumItem.Items.Add(item1
End Whil
dtr1.Close(
Catch ex As SqlCeExceptio
DisplaySQLCEErrors(ex
Finall
cmdSQL1.Dispose(
End Tr
End Whil
'lvSumItem.Items.Add(item
End Whil
dtr.Close(
Catch ex As SqlCeExceptio
DisplaySQLCEErrors(ex
Finall
cmdSQL.Dispose(
End Tr
End Sub

 

[William Ryan eMVP]

Definitely don't do it this way. YOu have two while dr.read's on the same
reader. Have you stepped through this already? Nesting while dr reads
would explain the proble. If you had two records returned, the first pass
through would advance the postion by one. Before it can iterate, you call
another one, advancing it again. Then there's nothing left in either so it
just kicks out. Why not loop through one and add the values you need to an
array list and then use those values to reference whatever you need in the
second query so you get out of nesting. or use a DataSet and load different
tables in there, and then apply a datarelation to them if it's applicable?

Guys, I am trying to use a nested While dtr.Read() over 2 databases(.sdf)

in order to retrieve 2 rows(prob bad design - i know!) from a table named
OrderDtl. I then fill my ListView based on the results. The code i use is as
below. However, the 1st datareader seems to only return 1 row even though my
SQLCE select returns 2 rows. I am stumped.

I may goto dataset, adapter and row but would love to use the direct SQLCE

command to do this. Can anybody help me please.

Private Sub btnByItem_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles btnByItem.Click

 

[Guest]

William Ryan wrote "Why not loop through one and add the values you need to a
array list and then use those values to reference whatever you need in th
second query so you get out of nesting. or use a DataSet and load differen
tables in there, and then apply a datarelation to them if it's applicable?

William, what do you think of the technique in general. I'm not really using datasets just direct to .sdf DML
I have it working now, will there be a speed issue when i have a larger number of records to iterate. Do you know of anyway to make it 'Super' fast