Preparing the Database

Obviously, before you can start working with stored procedures, you'll want to set up a database that enables you to create them.

Creating the Database in SQL Server

In this example, we'll use SQL Server 6.5. To create a new database within SQL Server, you first create a new database device and then create the database itself and assign it to the database device.

Note

---

A database device is one of two types of files in which databases are stored; backup devices are the other type. More than one database can be stored on a device.

Take these steps to create a new database device within SQL Server:

1. Choose Start|Microsoft SQL Server 6.5|SQL Enterprise Manager.
2. Select the server you want to work with from within the Server Manager window, and expand the node.
3. Right-click Database Devices, and choose New Device from the context menu.
4. Enter a name for the new device in the New Database Device dialog box, and specify the location for the device and the device size in MB.
5. Click the Create Now button to create the new device.

Once you have created the new database device, it will appear under the Database Devices node within the Server Manager window. You can later edit the device properties by right-clicking the device and choosing Edit from the context menu. You can edit the size of the device and also specify whether the device is mirrored. Mirroring makes a continuous copy of the information in the device to another device. This can help recovery in the event of a media failure.

Take the following steps to create a new database within SQL Server:

1. Choose Start|Microsoft SQL Server 6.5|SQL Enterprise Manager.
2. Select the server you want to work with from within the Server Manager window, and expand the node.
3. Right-click Databases, and choose New Database from the context menu.
4. Enter a name for the new database in the New Database dialog box, and specify the data device name and log device name for the database.
5. Click the Create Now button to create the new database.

Figure 15-1 shows the SQL Server Enterprise Manager with the New Database dialog box ready to create a new database named VI6Samples. Note that the database device has already been created and is displayed under the Database Devices node.

Figure 15-1. The SQL Server Enterprise Manager showing the New Database dialog box.

Creating the ODBC Connection in Visual InterDev

After you have created the database within SQL Server, the next step is to start Visual InterDev, create your web project, and add a data connection to the database. After opening the Web project you want to work with, right-click the global.asa file within the Project Explorer and choose Add Data Connection from the context menu. This will launch the Select Data Source dialog box, which will allow you to create a new File DSN (data source name) for your database.

To configure the ODBC connection to your SQL Server database, take the following steps:

1. Click the New button in the Select Data Source dialog box.
2. Choose SQL Server as the driver name, and click Next in the Create New Data Source dialog box.
3. Type the name of the File DSN you want to save this connection to, and click Next. Click Finish on the next screen.

4. In the Create A New Data Source To SQL Server dialog box, enter a description for the data source and specify the name of the server that you wish to connect to. (See Figure 15-2.)

   

   Figure 15-2. The Create A New Data Source To SQL Server dialog box, in which you specify the name of the server to connect to.

5. On the next screen, specify how SQL Server should verify the authenticity of the login ID. Choose With SQL Server Authentication Using A Login ID And Password Entered By The User, enter sa as the login ID, and click Next. (See Figure 15-3.)

   

   Figure 15-3. The dialog box in which you specify how SQL Server should verify the authenticity of the login ID.

6. On the next screen, check the Change The Default Database To check box, and select your database name in the drop-down list box. Click Next.
7. Click Next and then Finish to get to the final screen. This screen allows you to test the connection. Click the Test Data Source button, and verify that the connection succeeds. Click OK to complete the ODBC Microsoft SQL Server Setup.

After following the steps outlined above, you have created a File DSN for your SQL Server data source. You can now select this File DSN and click OK to begin establishing the data connection within Visual InterDev. Now click OK in the SQL Server Login dialog box to log in to the database. The next step is to specify a name for the data connection and to specify the authentication information for the data connection both in the design-time and run-time environment.

Follow these steps to complete your definition of the database connection:

1. In the General tab of the Connection Properties dialog box, enter a name for the data connection and choose Use Connection String for the source of the connection.
2. Select the Authentication tab, and enter sa for both the design-time and run-time user name. Check both the Save Design-time Authentication and the Save Run-time Authentication check boxes. (See Figure 15-4.)

Figure 15-4. The Authentication tab of the Data Connection Properties dialog box, where you specify both design-time and run-time authentication information.