Recordset.Find Problem ?

boringnaa
boringnaa used Ask the Experts™
on
I'm Trying to use Recordset.Find but it doesn't seem to be success. Could Anybody help me? Here is my code

Public Function InitiatListView()
Dim ObjCn As New ADODB.Connection
Dim ObjRs As New ADODB.Recordset    
'This one is for Table : AlbumInfo
Dim ObjRs2 As New ADODB.Recordset  
'This one is for Table : AlbumType
Dim ObjItem As ListItem
Dim Datalocation As String
Dim strTypeName As String

    ObjCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & App.Path & "\Data\CUMusicLibrary.mdb" & _
               ";Persist Security Info=False"
    Set ObjRs = ObjCn.Execute("SELECT * FROM AlbumInfo")
    Set ObjRs2 = ObjCn.Execute("SELECT * FROM AlbumType")
   
    Do While Not ObjRs.EOF
        Set ObjItem = ListView1.ListItems.Add(, , ObjRs.Fields("AlbumID"))
        ObjItem.SubItems(1) = ObjRs("AlbumTitle")
        ObjItem.SubItems(2) = ObjRs("Media_Type")
        strTypeName = ObjRs("Album_Type")
        'strTypeName is the thing that I want to find
        'and I'm sure that it's in "AlbumKey" in
        '"AlbumType" table
        ObjRs2.MoveFirst
        ObjRs2.Find "AlbumKey=" & strTypeName
        'The error happen here(The line above)
        If ObjRs2.EOF Then
            ObjRs2.MoveFirst  'Actually I would like to
                              'do nothing here
        Else
            ObjItem.SubItems(3) = ObjRs2("Type_Name")
        End If
        If ObjRs("Available") = True Then
            ObjItem.SubItems(4) = "Yes"
        Else
            ObjItem.SubItems(4) = "No"
        End If
        ObjRs.MoveNext
    Loop
ObjRs.Close
ObjRs2.Close
ObjCn.Close

End Function



Thanl
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ObjRs2.Find "AlbumKey=" & strTypeName should be
ObjRs2.Find "AlbumKey=" & "'" & strTypeName & "'"
You must delimit strings sith the single quote whend performing a find.

Commented:
Please try this, hopefully can solve your problem.

Public Function InitiatListView()
Dim ObjCn As New ADODB.Connection
Dim ObjRs As New ADODB.Recordset    
'This one is for Table : AlbumInfo
Dim ObjRs2 As New ADODB.Recordset  
'This one is for Table : AlbumType
Dim ObjItem As ListItem
Dim Datalocation As String
Dim strTypeName As String

   ObjCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & App.Path & "\Data\CUMusicLibrary.mdb" & _
              ";Persist Security Info=False"

   'use client side cursor location
   '-------------------------------
   ObjRs.CursorLocation = adUseClient
   ObjRs2.CursorLocation = adUseClient

   'Change this part
   '----------------
   'Set ObjRs = ObjCn.Execute("SELECT * FROM AlbumInfo")
   'Set ObjRs2 = ObjCn.Execute("SELECT * FROM AlbumType")
   ObjRs.Open "SELECT * FROM AlbumInfo", ObjCn
   ObjRs2.Open "SELECT * FROM AlbumType", ObjCn

   Do While Not ObjRs.EOF
       Set ObjItem = ListView1.ListItems.Add(, , ObjRs.Fields("AlbumID"))
       ObjItem.SubItems(1) = ObjRs("AlbumTitle")
       ObjItem.SubItems(2) = ObjRs("Media_Type")
       strTypeName = ObjRs("Album_Type")
       'strTypeName is the thing that I want to find
       'and I'm sure that it's in "AlbumKey" in
       '"AlbumType" table
       ObjRs2.MoveFirst
       ObjRs2.Find "AlbumKey=" & strTypeName
       'The error happen here(The line above)
       If ObjRs2.EOF Then
           ObjRs2.MoveFirst  'Actually I would like to
                             'do nothing here
       Else
           ObjItem.SubItems(3) = ObjRs2("Type_Name")
       End If
       If ObjRs("Available") = True Then
           ObjItem.SubItems(4) = "Yes"
       Else
           ObjItem.SubItems(4) = "No"
       End If
       ObjRs.MoveNext
   Loop
ObjRs.Close
ObjRs2.Close
ObjCn.Close

End Function

Author

Commented:
If would be better if you explain me about what is wrong and why can't I do it that way.

Commented:
Here is my answer.

Firstly, The returned Recordset object for Connection.execute is always a read-only, forward-only cursor. If you need a Recordset object with more functionality, first create a Recordset object with the desired property settings, then use the Recordset object's Open method to execute the query and return the desired cursor type. For more information, please refer to :-

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthcnnexecute.asp

Secondly, in order to use more function in recordset, like AbsolutePosition, Find, Filter function, your must use client side location instead of server side location.

A server-side cursor is stored on the system that runs the database server. A client-side cursor is stored on the system that initiated the recordset.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial