newbee needs help with code

  • Thread starter Thread starter Danny Ni
  • Start date Start date
D

Danny Ni

Hi,

I have the following VB.Net code snippet:

While datareader3.Read

..........

strsql = "select * from InspectionViolations where propTableID=" &
datareader3.Item(4) & " AND inspIDCleared IS NULL"

ConnViolPresent.Open()

CmdViolPresent = New SqlCommand(strsql, ConnViolPresent)

adapterViolPresent.SelectCommand = CmdViolPresent

adapterViolPresent.Fill(dsViolPresent)

ConnViolPresent.Close()

If (dsViolPresent.Tables(0).Rows.Count() > 0) Then

'For some reason, dsViolPresent.Tables(0).Rows.Count() always return 3




As you can see the code inside a while loop will generate a different SQL
statement and send it to SQL server. However no matter what the SQL
statement is, the result dataset stays the same and it's the result
generated by the first SQL statement.

What do I have to do to make dsViolPresent refreshed in every loop ?

TIA
 
First, I must assume that the connection being used by DataReader3 is not
the same as the connection used by the Fill--you can't share a connection
like this until ADO 2.0 (MARS). The next (most obvious) question I have is
"Why are you doing this?". Why not build a correlated query that executes a
JOIN to return the rows wanted instead of looping through a DataReader to
retrieve rows and doing a full-blown Fill for each row? This is very
inefficient. As to why it's not working as expected, I would step through
this code at runtime and see what values are being passed to datareader3 and
possibly turn on the Profiler to see what's getting sent to the server.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Hi Danny,

When looping thru, the first time it fills
dsViolPresent.Tables(0), the second time it fills
dsViolPresent.Tables(1), ... and so on. So it indeed keeps
refreshing dsViolPresent.

However, the code shows it keeps connection frequently
open and close. In order to have good performance, it's
better to get whole data in a DataTable first:

Dim tblViolPresent As New DataTable()
Dim adapterViolPresent As New SqlDataAdapter("select *
from InspectionViolations where inspIDCleared IS NULL",
connectString)
AdapterViolPresent.Fill(tblViolPresent)

Dim dv As DataView = tblViolPresent.DefaltView

While datareader3.Read
dv.RowFilter = "propTableID=" & datareader3.Item(4)
If dv.Count > 0 Then
' ...
End If
' ...
End While

Hope it helps,

Elton Wang
(e-mail address removed)
 
Back
Top