datasets

[Noob]

I am trying to use either a datagrid or a datalist to list all entries in a table. The problem I am having is when I use the fill() method the local machine runs out of memory. The table is a list of names that the users want to scroll through in alpha order and is just under 2 million records. Any suggestions?

[Pirate]

2billion , I mean 2million ??? . What database are you using ?

[Noob]

It's a MSSQL database of prospects.

[Pirate]

Originally posted by Noob
It's a MSSQL database of prospects.

Would this help you out !http://asia.cnet.com/builder/archite...94272-2,00.htm

[Noob]

Thanks Pirate but that just told me that I can't trap the error.
This is how I got around it in VB 6.0:
On form load I filled in the first 13 records as follows:

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open "Select lastname, firstname, phone1, prospectid, prospectnumber, prospectstatus, prospecttype From t_prospect order by lastname,firstname", frmMain.dbConn, adOpenDynamic, adLockBatchOptimistic
lngscrollValue = 0
VScroll1.Value = lngscrollValue
Do While Not rs.EOF
lngProsPos = lngProsPos + 1
Set itmNew = ListView1.ListItems.Add(, "A" & rs("prospectid"), rs("LastName") & ", " & rs("firstname"))
itmNew.SubItems(1) = rs!phone1 & ""
itmNew.SubItems(2) = rs!prospectid & ""
itmNew.SubItems(3) = rs!prospectnumber & ""
itmNew.SubItems(4) = rs!prospectStatus & ""
itmNew.SubItems(5) = rs!prospectType & ""
Set itmNew2 = ListView2.ListItems.Add(, "A" & rs("prospectid"), rs("LastName") & ", " & rs("firstname"))
itmNew2.SubItems(1) = rs!phone1 & ""
itmNew2.SubItems(2) = rs!prospectid & ""
itmNew2.SubItems(3) = rs!prospectnumber & ""
itmNew2.SubItems(4) = rs!prospectStatus & ""
itmNew2.SubItems(5) = rs!prospectType & ""
Set itmNew3 = ListView3.ListItems.Add(, "A" & rs("prospectid"), rs("LastName") & ", " & rs("firstname"))
itmNew3.SubItems(1) = rs!phone1 & ""
itmNew3.SubItems(2) = rs!prospectid & ""
itmNew3.SubItems(3) = rs!prospectnumber & ""
itmNew3.SubItems(4) = rs!prospectStatus & ""
itmNew3.SubItems(5) = rs!prospectType & ""
Set itmNew4 = ListView4.ListItems.Add(, "A" & rs("prospectid"), rs("LastName") & ", " & rs("firstname"))
itmNew4.SubItems(1) = rs!phone1 & ""
itmNew4.SubItems(2) = rs!prospectid & ""
itmNew4.SubItems(3) = rs!prospectnumber & ""
itmNew4.SubItems(4) = rs!prospectStatus & ""
itmNew4.SubItems(5) = rs!prospectType & ""
rs.MoveNext
DoEvents
If lngProsPos >= 13 Then
Exit Do
End If
Loop
If ListView1.ListItems.Count = 0 Then
cmdChange.Enabled = False
Else
cmdChange.Enabled = True
ListView1.ListItems(1).Selected = True
ListView2.ListItems(1).Selected = True
ListView3.ListItems(1).Selected = True
ListView4.ListItems(1).Selected = True
End If
lngProsPos = 1

Then I had to maintain the users position so that I could scroll as they did

Private Sub Pros_Movenext(intSteps As Integer)
If intSteps = 1 Then
ListView1.ListItems.Clear
ListView2.ListItems.Clear
ListView3.ListItems.Clear
ListView4.ListItems.Clear
lngProsPos = lngProsPos + 1
For i = 1 To 12
rs.MovePrevious
If rs.BOF Then
rs.MoveFirst
Exit For
End If
Next i
Fill_Views "DOWN"
End If
End Sub

Private Sub Pros_Moveprev(intSteps As Integer)
If intSteps = 1 Then
ListView1.ListItems.Clear
ListView2.ListItems.Clear
ListView3.ListItems.Clear
ListView4.ListItems.Clear
lngProsPos = lngProsPos - 1
If lngProsPos < 1 Then lngProsPos = 1
For i = 1 To 14
If Not (rs.BOF) Then rs.MovePrevious
If rs.BOF And Not (rs.EOF) Then
rs.MoveFirst
Exit For
End If
Next i
Fill_Views "UP"
End If
End Sub

Private Sub Pros_Scroll(lngPosition As Long, bolForward As Boolean)
If bolForward Then
Position_Recordset lngPosition
Fill_Views
ListView1.ListItems(13).Selected = True
ListView2.ListItems(13).Selected = True
ListView3.ListItems(13).Selected = True
ListView4.ListItems(13).Selected = True
lngProsPos = lngProsPos + 12
Else
Position_Recordset 0
Fill_Views
ListView1.ListItems(1).Selected = True
ListView2.ListItems(1).Selected = True
ListView3.ListItems(1).Selected = True
ListView4.ListItems(1).Selected = True
End If
End Sub

Private Sub Position_Recordset(Direction As Long)
If Direction > 0 Then
rs.MovePrevious
Else
Dim i As Integer
For i = 1 To 26
rs.MovePrevious
If rs.BOF Then
'MsgBox rs!prospectid
rs.MoveFirst
Exit For
End If
Next i
If i >= 26 Then
lngProsPos = lngProsPos - 12
Else
lngProsPos = 1
End If
End If
End Sub

I know this wasn't the best way to handle this but it worked.

I want to, at the least, find a similar solution ado.net. But the detached datasets completely fill before you can use them and this uses all available memory.

How does microsoft do it in Access or SQL manager when you open the table? (Ideal solution)

[Pirate]

Sorry dude , I can't help anymore at this area . You got huge data , you need to be careful , as to achieve the best solution that goes on in critical times . Luck ..

[Lunatic3]

There is an overload method of Fill Statement like this:

mydatadapter.Fill(dataset as Dataset, startRecord as integer, MaxRecords As integer, srcTable as String)

Maybe this can help you to load data step by step.

[DevGrp]

If you are trying to list all 2 million records at once you are gonna have problems.

[Noob]

Thanks all for your responses. One more question for Lunatic3. Can I use sql queries as the srcTable? or do I need to use views and/or Stored Procedures to apply filters as needed?

[Iamthewalrus15]

hmmmm,, can ado.net do interdataset joins?? create 2 datasets perhaps? off of 2 views from the SQL server(2 views that divide the data)

[Lunatic3]

Well the srcTable is the name of the table inside your Dataset, you can use sql queries as source of your sqldataadapter. and map its name to a datatable in the dataset or you can choose a name of your own.

[xordinary]

That's right DevGrp

[DevGrp]

If you must list all the records at once and you wont be editing the data, use a datareader. If you gonna be changing the data, then you should use a dataset and just get the records that you need instead of all.