Returning a sorted recordset



Hi. I am using a recordset along with a collection to display sorted files in a FlexGrid. However, my sort is not working and I can't understand why. Any help would be appreciated, thanks much.
JavaJen



Public Sub Sort(key As String) 'in class module
'On Error Resume Next
Set mrstSorted = New ADODB.Recordset
Dim pcon As New ADODB.Connection
Dim pcmd As New ADODB.Command
Dim STRSQL As String
Dim aCoin As coin, i As Integer


STRSQL = "SELECT * FROM Coin ORDER BY " & key
pcon.Mode = adModeShareDenyNone
pcon.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"Data Source=" & App.Path & "dbCoins.mdb"
pcon.Open

pcmd.CommandType = adCmdText
pcmd.CommandText = STRSQL
Set pcmd.ActiveConnection = pcon
i = 1

mrstSorted.LockType = adLockOptimistic
mrstSorted.CursorLocation = adUseClient
mrstSorted.CursorType = adOpenKeyset
mrstSorted.Open pcmd


If Not (mrstSorted.BOF And mrstSorted.EOF) Then
mrstSorted.MoveFirst '
While Not mrstSorted.EOF
Set aCoin = New coin
With aCoin
.Catalog = mrstSorted.Fields("Catalog").Value
.Num = mrstSorted.Fields("Number").Value
.ColName = mrstSorted.Fields("Collection").Value
.Size = mrstSorted.Fields("Size").Value
.Shape = mrstSorted.Fields("Shape").Value
.Composition = mrstSorted.Fields("Composition").Value
.Year = mrstSorted.Fields("Year").Value
.PurchasedFrom = mrstSorted.Fields("PurchasedFrom").Value
.Cost = mrstSorted.Fields("Cost").Value
.recDate = mrstSorted.Fields("Date").Value
.StruckBy = mrstSorted.Fields("StruckBy").Value
.Engraver = mrstSorted.Fields("Engraver").Value
.Sculptor = mrstSorted.Fields("Sculptor").Value
.PlatedWith = mrstSorted.Fields("PlatedWith").Value
.remarks = mrstSorted.Fields("remarks").Value
.CategoryIssue = mrstSorted.Fields("categoryissue").Value
.CurrentValue = mrstSorted.Fields("CurrentValue").Value
.condition = mrstSorted.Fields("Condition").Value
.Designer = mrstSorted.Fields("Designer").Value
.Index = i
i = i + 1
End With
colSorted.Add aCoin
mrstSorted.MoveNext
Wend
End If
End Sub

Comments

  • What exactly is the problem?

    : Hi. I am using a recordset along with a collection to display sorted files in a FlexGrid. However, my sort is not working and I can't understand why. Any help would be appreciated, thanks much.
    : JavaJen
    :
    :
    :
    : Public Sub Sort(key As String) 'in class module
    : 'On Error Resume Next
    : Set mrstSorted = New ADODB.Recordset
    : Dim pcon As New ADODB.Connection
    : Dim pcmd As New ADODB.Command
    : Dim STRSQL As String
    : Dim aCoin As coin, i As Integer
    :
    :
    : STRSQL = "SELECT * FROM Coin ORDER BY " & key
    : pcon.Mode = adModeShareDenyNone
    : pcon.ConnectionString = _
    : "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    : "Persist Security Info=False;" & _
    : "Data Source=" & App.Path & "dbCoins.mdb"
    : pcon.Open
    :
    : pcmd.CommandType = adCmdText
    : pcmd.CommandText = STRSQL
    : Set pcmd.ActiveConnection = pcon
    : i = 1
    :
    : mrstSorted.LockType = adLockOptimistic
    : mrstSorted.CursorLocation = adUseClient
    : mrstSorted.CursorType = adOpenKeyset
    : mrstSorted.Open pcmd
    :
    :
    : If Not (mrstSorted.BOF And mrstSorted.EOF) Then
    : mrstSorted.MoveFirst '
    : While Not mrstSorted.EOF
    : Set aCoin = New coin
    : With aCoin
    : .Catalog = mrstSorted.Fields("Catalog").Value
    : .Num = mrstSorted.Fields("Number").Value
    : .ColName = mrstSorted.Fields("Collection").Value
    : .Size = mrstSorted.Fields("Size").Value
    : .Shape = mrstSorted.Fields("Shape").Value
    : .Composition = mrstSorted.Fields("Composition").Value
    : .Year = mrstSorted.Fields("Year").Value
    : .PurchasedFrom = mrstSorted.Fields("PurchasedFrom").Value
    : .Cost = mrstSorted.Fields("Cost").Value
    : .recDate = mrstSorted.Fields("Date").Value
    : .StruckBy = mrstSorted.Fields("StruckBy").Value
    : .Engraver = mrstSorted.Fields("Engraver").Value
    : .Sculptor = mrstSorted.Fields("Sculptor").Value
    : .PlatedWith = mrstSorted.Fields("PlatedWith").Value
    : .remarks = mrstSorted.Fields("remarks").Value
    : .CategoryIssue = mrstSorted.Fields("categoryissue").Value
    : .CurrentValue = mrstSorted.Fields("CurrentValue").Value
    : .condition = mrstSorted.Fields("Condition").Value
    : .Designer = mrstSorted.Fields("Designer").Value
    : .Index = i
    : i = i + 1
    : End With
    : colSorted.Add aCoin
    : mrstSorted.MoveNext
    : Wend
    : End If
    : End Sub
    :

    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

  • It does not reflect any changes in my Grid. When the form loads, it loads from the database non-sorted. When I sort there is no change. I am using two recordsets... one to initially load from the db, and one to sort. Could this be the problem??? Should I use only one recordset??
    Thanks.
    JavaJen
  • : It does not reflect any changes in my Grid. When the form loads, it loads from the database non-sorted. When I sort there is no change. I am using two recordsets... one to initially load from the db, and one to sort. Could this be the problem??? Should I use only one recordset??

    It shouldn't be a problem if you are handling them properly. What code are you using to load the grid? All I see in the code you posted is querying the database and building a collection of objects from it.

    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]



  • Hey, I figured it out. Turned out I had to use a separate collection class. Thanks for your help though! :-)
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories