boringnaa
asked on
Recordset.Find Problem ?
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.OL
"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
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.OL
"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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If would be better if you explain me about what is wrong and why can't I do it that way.
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.
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.
ObjRs2.Find "AlbumKey=" & "'" & strTypeName & "'"
You must delimit strings sith the single quote whend performing a find.