Visual Basic Concepts

Adding a Table to an SQL Server Database

When you create a DHTML application that interacts with a SQL Server database, you may want to add new tables to the database. You can use a data environment to create a connection to the SQL Server database, then create your tables in the Data View window.

This topic shows how to create a connection to the Pubs sample database in SQL Server, and how to add a new Customers table to the database.

To add a table to an SQL database

1. Open a new DHTML Application DLL project.

2. Create a connection to an SQL Server database.

3. Add a table to the database.

Note   This topic is part of a series that walks you through creating a simple database application that interacts with data in an SQL Server database. It begins with the topic Creating a DHTML Application that Interacts with SQL Server Data.

Open a DHTML Application DLL Project

The first step in creating a DHTML application is to open a new DHTML Application DLL project. A DHTML Application DLL project is an ActiveX DLL project template that automatically loads the DHTML Page designer and its HTML toolbox tab and HTML controls, which are known as elements. Select New Project from the File menu, then select DHTML Application in the New Project dialog box.

Visual Basic automatically adds a DHTML Page designer to your project. For every HTML page in your application, you need a corresponding designer. Open the Designers folder in the Project Explorer and double-click DHTMLPage1. The DHTML Page designer displays the page's interface in the right pane of the designer window, and a hierarchical list of the objects on the page in the left pane of the window.

For More Information   For more information on creating a DHTML application, see Developing DHTML Applications in Building Internet Applications in the Component Tools Guide.

Create a Connection to an SQL Server Database

A data environment provides an easy way to create a connection to a database. To add a data environment to your project, select Add Data Environment from the Project menu. Visual Basic loads a data environment, and adds a Connection object to the data environment.

You establish the connection to your data source by setting properties in the Data Link Properties dialog box. To display this dialog box, right-click the Connection object in your data environment and then choose Properties from the menu. Select an OLE DB provider on the Provider tab of the dialog box. Then click Next and enter connection information on the Connection tab of the dialog box. The layout of the Connection tab varies, depending on the OLE DB provider you select on the Provider tab.

For example, to connect to an SQL Server database such as the Pubs sample database, select the Microsoft OLE DB Provider for SQL Server on the Provider tab. On the Connection tab, enter the server name, log on information, and the database name or SQL database file name.

You set additional connection properties by clicking the Advanced or All tabs, or test the connection by clicking Test Connection.

For More Information   For more information on using the Data Environment, see Chapter 3, About the Data Environment Designer. For more information on connections and connection properties, see Connection Objects in "About the Data Environment Designer." And for more information on Data Links, see in "Database and Messaging Services" in the Platform SDK.

Add a Table to the Database

The Data View window lets you easily add a table, view, or stored procedure to your database. Open the Data View window by clicking the Data View Window button on the Standard toolbar. The Data View window displays a hierarchical view of any database connections you've created. For example, if you've created a connection to the Pubs sample database, the Data View window shows the database and its diagrams, tables, views, and stored procedures.

Add a table to the database by right-clicking the Tables folder in the Data View window and selecting the New Table command. The Data View window displays the Choose Name dialog box, where you enter a name for the table. When you click OK, the Data View window displays the New Table window, where you enter the table definition — the name of each column in the table, its datatype, and other information about the column. To specify a primary key for the table, disable the Allow Nulls box, right-click the cell, and select Set Primary Key from the shortcut menu.

For example, to add a Customers table to the Pubs database that stores customer address information, enter Customers in the Choose Name dialog box and enter the following table definition:

Set CustomerID as the primary key. When you save the table definition, the table is added to the database.

For More Information   For more information on using the Data View window, see . For more information on using the Data View window to create a table, see .

Step by Step

This topic is part of a series that walks you through creating a simple DHTML application that interacts with data in an SQL Server database.