HOWTO: RDO Prepared Statements/Search Crit. to Fill Listbox (142836)

SUMMARY

RDO does not have a FindFirst or a Seek method. This sample uses an RDO prepared statement to provide this kind of functionality. The code included below allows the user to type in search criteria in a text box, click a command button and the list box will be filled with any records that satisfy the search criteria. You may also want to use a stored procedure once you know that your query is working the way that you want it to. Keep in mind that RDO is available in Visual Basic 4.0 32-bit and Visual Basic 5.0 Enterprise editions only.

MORE INFORMATION

Step-By-Step Example

Start a new project in Visual Basic. Form1 is created by default. Add a CommandButton, a text box and a list control. Accept the default names for all.
Make sure a reference to the Microsoft Remote Data Object library has been established. In Visual Basic 4.0, you can establish the reference by clicking References from the Tools menu. In Visual Basic 5.0, click References from the Project menu.

Add the following code to the General Declarations section of Form1:

    Option Explicit
    Dim ps As rdoPreparedStatement
    Dim conn As rdoConnection
    Dim rs As rdoResultset

    Private Sub Form_Load()
        With rdoEnvironments(0)
          .CursorDriver = rdUseOdbc
    Set conn = .OpenConnection("", rdDriverNoPrompt, False, _
    "driver={SQL Server};server=Myserv;uid=MyUID;pwd=Mypwd;database=pubs")
     End With
        Set ps = conn.CreatePreparedStatement("", " select au_lname " & _
          "from authors where au_lname like ?")
    End Sub

    Private Sub Command1_Click()
      list1.Clear
      ps.rdoParameters(0) = Text1.Text
      If rs Is Nothing Then
          Set rs = ps.OpenResultset(rdOpenKeyset)
      Else
          rs.Requery
      End If
      While Not rs.EOF
          list1.AddItem rs!au_lname
          rs.MoveNext
      Wend
      End Sub

    Private Sub Form_Unload(Cancel As Integer)
      rs.Close
      conn.Close
    End Sub

Make sure you have an appropriate ODBC data source, user ID, and password, then press the F5 key to run the program. Type your search criteria in Text1 and click the CommandButton.