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

MORE INFORMATION

Step-By-Step Example

1. 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.
2. 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.
3. 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
   
   						

4. 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.