Getting info from SQL DB

[Bill Schanks]

I have this code, that will take what is in a listview and go get more
info. Howerver on a large number of listview records this will produce
a problem (Say if I had 30,000 Listview entires).

What would be the recommended alternative? Have it load the listview
items to a table and then have my query join to that table, Put in a
check and if there are >1000 listview entires don't allow the sql
lookup?

<<snip>>
Try
sSQL = "SELECT PreferredID, LastName, FirstName, Phone,
Email, ManagerPreferredID, JobFamily, " _
& "CostCenterNbr, CostCenterName FROM dbo.Employee "

'Construct Where Clause
For Each item As ListViewItem In lvMembers.Items
If Not sb.Length = 0 Then sb.Append(", ")
sb.Append("'" & item.Text & "'")
Next
sSQL = sSQL & "WHERE PreferredID IN (" & sb.ToString & ")
" _
& "ORDER BY LastName, FirstName"

'Clear list view out
Me.lvMembers.Clear()

'Fetch new results
ShowBusy("Querying database...")
iCount = 0
Using connection As New SqlConnection(g_sSQL_CONN)
Dim command As New SqlCommand(sSQL, connection)
connection.Open()

Dim reader As SqlDataReader = command.ExecuteReader

'Setup columns
For i = 0 To reader.FieldCount - 1
lvwColumn = New ColumnHeader
lvwColumn.Text = reader.GetName(i)
lvwColumn.Width = 80
Me.lvMembers.Columns.Add(lvwColumn)
Next

Do While reader.Read
itmListItem = New ListViewItem
itmListItem.Text = CStr(reader(0))

For i = 1 To reader.FieldCount - 1
If reader.IsDBNull(i) Then
itmListItem.SubItems.Add("")
Else

itmListItem.SubItems.Add(reader.GetString(i))
End If
Next i

lvMembers.Items.Add(itmListItem)
iCount = iCount + 1

Loop

'Close connection
reader.Close()
End Using
<<Snip>>

 

[Armin Zingler]

I have this code, that will take what is in a listview and go get
more info. Howerver on a large number of listview records this will
produce a problem (Say if I had 30,000 Listview entires).

What would be the recommended alternative? Have it load the listview
items to a table and then have my query join to that table, Put in a
check and if there are >1000 listview entires don't allow the sql
lookup?

Try to execute

lvMembers.beginupdate

before filling and

lvMembers.endupdate

afterwards. Better now?


And try this version: (an attempt)

Dim Items As String()

ReDim Items(reader.FieldCount - 1)

Do While reader.Read
Items(0) = CStr(reader(0))

For i = 1 To reader.FieldCount - 1
If reader.IsDBNull(i) Then
Items(i) = "" 'or String.Empty
Else
Items(i) = reader.GetString(i)
End If
Next i

itmListItem = New ListViewItem(Items)
lvmembers.Items.Add(itmListItem)
icount = icount + 1

Loop




Armin

 

[Bill Schanks]

The error is coming because sSQL is too large, and I am getting the
error on SQL Excecution when there are 30000 entries in the in clause
of the sql command.

 

[Lloyd Sheen]

The error is coming because sSQL is too large, and I am getting the
error on SQL Excecution when there are 30000 entries in the in clause
of the sql command.

What you want to do is if you are using ListView , make it a virtual
listview. I have samples if you need them. Can fill and display 30K items
very quickly.

LS

 

[Armin Zingler]

The error is coming because sSQL is too large, and I am getting the
error on SQL Excecution when there are 30000 entries in the in clause
of the sql command.

Reading this statement

I thought it takes too long.


What does "SQL is too large" mean? Do you get an exception? Which one?



Armin

 

[Bill Schanks]

The error is not filling the listview, it doesn't even get to that
point. The SQL Command is the problem. Here is the error I get:

system.data.sqlclient.sqlexception: Internal query processor error:
The query processor ran out of stack space during query optimization

This is due to the IN Clause have 30000 items. My question was should
I have the program load the list view items to a table and then join
to that table to my query, or just not allow the command if the IN
Clause would have more than x number of items.

 

[Bill Schanks]

This is the error:
system.data.sqlclient.sqlexception: Internal query processor error:
The query processor ran out of stack space during query optimization

This is because the in clause would have 30,000+ items in it.

 

[Bill Schanks]

The application pulls users that are in an Active Directory Group. And
allows users to pull add'l data from the HR Records that is not avail
from Active Directory.

And it doesn't allow 30,000 records in the in clause it errors out. I
don't know what the limit is. Now 30,000 is an extreme example. Users
most likely won't want to pull info on 30,000 People. But I'd like the
app to work regardless.

 

[Mike Williams]

What you want to do is if you are using ListView , make it a virtual
listview. I have samples if you need them. Can fill and display 30K
items very quickly.

I don't know whether anyone from Microsoft regularly reads the Visual Basic
groups but if they do then I am very surprised that they permit one of their
own MVPs to engage in such outrageous long term trolling activities in one
of their own public newsgroups, such as the activity that the person who
purports to be Bill McCarthy has engaged in on the
microsoft.public.vb.general.discussion group for many months. If this man
belongs to you:

https://mvp.support.microsoft.com/profile=B2D0BB02-3E35-4293-B4B9-25680609CCB8

.. . . then perhaps you might like to look at his activity in that group.
Here for example is one of his very latest offerings: