Results 1 to 4 of 4

Thread: Help in vb

  1. #1
    Join Date
    Mar 2009
    Posts
    4

    Unhappy Unanswered: Help in vb

    How can I find a record in datatbase through find method in vb & after find it put it in listview box.....? my code is............


    Private Sub cmdSearch_Click()
    Dim Cnx As New ADODB.Connection
    Dim Res As New ADODB.Recordset
    Dim strCnx As String
    Dim strColum As String
    Dim Str As String
    Dim row, i As Integer
    On Error GoTo ErrorHandler

    Set Cnx = New ADODB.Connection
    strCnx = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path _
    & "\" & "digital.mdb;Persist Security Info=False;MODE=READ|WRITE;Jet OLEDBatabase Password="
    Cnx.Open strCnx

    Set Res = New ADODB.Recordset
    strColum = "select * from digital"
    Res.Open strColum, Cnx, adOpenStatic, adLockOptimistic, adCmdText

    row = Res.RecordCount

    Str = cmbDid.Text
    Res.MoveFirst

    Res.Requery

    Res.Find Res!did Like Str, 1, adSearchForward, adBookmark



    ErrorHandler:
    If Err <> 0 Then
    MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
    End Sub
    Last edited by mohit_kiya_apne; 03-20-09 at 02:30.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    First, you are declaring your recordset and connection as NEW. This implicitly creates a new instance, so you should not create another new object instance with the SET objname = NEW ADODB.obj_type... Or, better yet, lose the NEW keyword in your declaration, and explicitly declare and instantiate the objects...i.e.
    Code:
    ' Declare
    Dim res as ADODB.Recordset
    ' ...
    ' Instantiate
    Set RES = New ADODB.Recordset
    ' use the object
    '...
    ' Release
    Set res = Nothing  'when you're done with it
    ' ...
    End Sub
    "Why wouldn't I always use implicit instantiation," you might wonder? When you declare the object as new (Dim obj as New ObjectType,) the implicit instantiation saves source code on your part, but, it results in less efficient object code every time the object is referenced, as the compiler adds object code to verify that the referenced object is, in fact, a valid object. While this inefficiency is small, it can add up, especially when objects are referenced from inside loops.

    I would also explicitly set the recordset cursorlocation to adUseClient before opening it. You may also want to use the recordset's FILTER method. It will 'shrink' the recordset's 'view' to only those records which meet the filter criteria. By this, I mean that if the original recordset held 1000 records, and 7 records will match the filter criteria, the rs.recordcount would change from 1000 to 7 after applying the filter, assuming you're using a cursorlocation which support the recordcount property

    be sure to remove filtering when done (rs.filter = adFilterNone)
    Last edited by loquin; 03-20-09 at 15:36.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Mar 2009
    Posts
    4

    Cool Help in VB

    Now I got the answer with easy coding as follows

    I have delete the code.....
    Res.Find Res!did Like Str, 1, adSearchForward, adBookmark

    and use itration as follows......

    For i = 1 To Res.RecordCount
    If Res!Title = cmbTitle.Text Then
    With Res
    Set X = lv1.ListItems.Add(, , .Fields!did)
    X.SubItems(1) = .Fields!catid
    X.SubItems(2) = .Fields!Title
    X.SubItems(3) = .Fields!size1
    X.SubItems(4) = .Fields!creator
    X.SubItems(5) = .Fields!gener
    X.SubItems(6) = .Fields!timeline
    X.SubItems(7) = .Fields!Collection
    X.SubItems(8) = .Fields!medium
    X.SubItems(9) = .Fields!pro
    X.SubItems(10) = .Fields!cat
    X.SubItems(11) = .Fields!subcat
    X.SubItems(12) = .Fields!State
    End With
    End If
    Res.MoveNext
    next i


    & it is easy & run easily.....

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    and, it is very, VERY inefficient.

    iterating through a recordset is about the slowest means of performing a search or find... Use the filter property, instead.
    Code:
    Res.Filter = "Title = " & cmbTitle.Text 
    With Res
      ' ...
     
    ' when done transferring the data you found
    '
    res.Filter = adFilterNone
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •