In this practice, you will add ADO code to an existing project to connect to a Microsoft SQL Server database and create a recordset.

To complete the exercises in this practice section, you must have the required files. For detailed information about practices and setup for the practices, see the Practice Downloads section in this course.

Create a connection to a SQL Server database

1. Start Microsoft Visual Basic and open the Publishers.vbp project located in the <install folder>\Practices\Chapter05\Creating a Recordset folder.
   
   
2. Open the code window for the cmdConnect click event.
   
   
3. Review the code that already appears in this event procedure.
   
   
4. Add the following code inside the With statement to connect to the database :

.Provider = "SQLOLEDB"
.ConnectionString = "Data Source=MSERIES1;" & _
                    "Initial Catalog=StateUBookstore;" & _
                    "User ID=sa"
.Open

---

Note  In the connection string, change the data source name of MSERIES1 to that of your SQL Server.

---

Create a recordset

• Add the following code to create a default recordset:

rsPublishers.Open "SELECT pub_id, pub_name, city, state FROM Publishers", cnStateUBookstore

Bind a control to the recordset

1. Add the following lines of code to bind the Recordset object to the State text box control:

   Set txtPubState.DataSource = rsPublishers
   txtPubState.DataField = "state"
     

2. Run the application and test your work. Since you used the default recordset, you will only be able to navigate using the Next command button.