{ewc HLP95EN.DLL,DYNALINK,"See Also":"vbconQuerySQLServerDatabaseC"}
Visual Basic lets you easily create applications that use the Internet or a corporate intranet to interact with data in a distributed relational database system such as SQL Server. For example, you can create an application that uses an HTML page for data entry or to query data in your database.
This scenario shows you how to create a DHTML application that interacts with data in the Pubs sample database in SQL Server. It is designed for use on an intranet and requires that you view HTML pages using Microsoft Internet Explorer 4.x. The scenario demonstrates:
· Adding a table to an SQL Server databasevbconConnectToSQLServerDatabase
· Creating a data entry HTML pagevbconCreateDataInputForm
· Creating an HTML page for viewing and updating datavbconCreateSQLTableUsingDesigner
· Creating an HTML page that runs a stored procedurevbconCreateNewQueryUsingDesigner
{ewc HLP95EN.DLL,DYNALINK,"See Also":"vbconConnectToSQLServerDatabaseC"}
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 DatavbconQuerySQLServerDatabase.
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 ApplicationsvbconDevelopingClientInternetApplications in Building Internet Applications in the Component Tools Guide.
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 DesignervbconUsingDataEnvironmentDesigner. For more information on connections and connection properties, see Connection ObjectsvbconConnectionObjects in "About the Data Environment Designer." And for more information on Data Links, see Data Link API Overview!ALink("DataLinkAPIOverview") in "Database and Messaging Services" in the Platform SDK.
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:
|
Column Name |
Datatype |
Length |
|
CustomerID |
char |
5 |
|
CompanyName |
varchar |
20 |
|
Address |
varchar |
40 |
|
City |
varchar |
20 |
|
Region |
varchar |
10 |
|
PostalCode |
varchar |
10 |
|
Country |
varchar |
20 |
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 Managing Databases in Data View!ALink("dvovrWorkingInDataView"). For more information on using the Data View window to create a table, see Tables!ALink("dvconTables").
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.
|
To |
See |
|
Go to the next step |
Create a Data Entry HTML PagevbconCreateDataInputForm |
|
Start from the beginning |
Creating a DHTML Application that Interacts with SQL Server DatavbconQuerySQLServerDatabase |
{ewc HLP95EN.DLL,DYNALINK,"See Also":"vbconCreateDataInputFormC"}
Visual Basic lets you easily create an HTML page that you can use to enter records in an SQL Server database. You can use the DHTML Page designer to create the HTML page and a data environment Command object to establish a connection to the database. You can write code that uses ActiveX Data Objects (ADO) to handle data processing.
This topic shows how to create an HTML page to enter data in the
Customers table you created in the "Adding a Table to an SQL Server
Database" topic. It shows how to use the data environment and
To create a data entry HTML page
1. Add labels and TextField elements to an HTML page.
2. Create a data environment Command object.
3. Add a Button element to the page to append records to a table.
4. Load the HTML page.
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 DatavbconQuerySQLServerDatabase.
You can use the DHTML Page designer to design your HTML page. Double-click the DHTML Page designer icon in the Project Explorer to bring up the designer. To add a label to a page, simply click in the right pane of the designer window and type the label text. To add a TextField element to the page, select the element from the toolbox and drag it to the right pane of the designer window.
For example, you can create a data entry HTML page that lets you enter address information in the new Customers table in the Pubs sample database. First, type "Customer ID:" in the right pane of the designer window to serve as a label for a Customer ID TextField element, then drag a TextField element next to the label, set its Name and ID properties to CustomerID, and clear its Value property. Repeat the same process for CompanyName, Address, City, Region, PostalCode, and Country elements.
For More Information For more information on using the DHTML Page designer, see Designing Pages for DHTML ApplicationsvbconDesigningPagesForClientInternetApplications in Chapter 2, "Developing DHTML Applications" of Building Internet Applications in the Component Tools Guide.
As you saw in the "Interact with Data in a Microsoft Jet/Microsoft Access Database" scenario, a data environment Command object can give you access to a table's data. In a DHTML application, a data environment Command object can serve as a logical "middle tier," linking a data source with data displayed on an HTML page.
Create the Command object by clicking the Add Command button on the Data Environment toolbar, or by right-clicking the connection in the Data Environment designer and selecting Add Command from the shortcut menu. You can then specify the Command object's name, the connection it uses, and the source of its data in the Command Properties dialog box. To display this dialog box, right-click the Command object in your data environment and then choose Properties from the menu..
For example, you can create a data environment Command object that provides access to the new Customers table in the Pubs sample database. Set the following properties, using the connection to the Pubs sample database that you created in the "Adding a Table to an SQL Server Database" topic:
|
Property |
Setting |
|
Command Name |
Customers |
|
Connection |
Connection1 |
|
Database Object |
Table |
|
Object Name |
dbo.customers |
To enable updating of records in the Customers table, change the default lock type used by the command. Click the Advanced tab in the Command Properties dialog box and set the Lock Type property to Optimistic.
For More Information For more information on creating a data environment Command object, see Command ObjectsvbconCommandObjects in "About the Data Environment Designer."
To provide a way to append the data entered on the HTML page to a table, you can add a Button element to the HTML page by dragging a Button element to the right pane of the DHTML Page designer. For example, you can add a Button element that appends address information to the Customers table. First, drag a Button element to the right pane of the designer window, and then set its name and id properties to AddCustomer.
Using the data environment and
For example, to enable the data entry HTML page for the new Customers table in the Pubs sample database, add the following code to the onclick event procedure for the DHTML Page object's AddCustomer Button element:
Private Function AddCustomer_onclick() As Boolean
' Run the Customers Command object.
MyDataEnvironment.Customers
' Append values from the DHTML Page object's elements
' to the command's underlying recordset.
With MyDataEnvironment.rsCustomers
.AddNew
!CustomerID = CustomerID.Value
!CompanyName = CompanyName.Value
!Address = Address.Value
!City = City.Value
!Region = Region.Value
!PostalCode = PostalCode.Value
!Country = Country.Value
.Update
.Close
End With
' Clear the elements.
CustomerID.Value = ""
CompanyName.Value = ""
Address.Value = ""
City.Value = ""
Region.Value = ""
PostalCode.Value = ""
Country.Value = ""
End Function
For More
Information For more information on using
To view the finished page in Internet Explorer, press F5 or click the Start button on the toolbar. You can also explicitly make a dynamic-link library and HTML page for the project by setting properties on the Make tab of the Project Properties dialog box, then selecting Make .dll from the File menu.
For More Information For more information on testing your DHTML applications, see Testing Your DHTML ApplicationvbconTestingYourClientInternetApplication in Chapter 2, "Developing DHTML Applications" in Building Internet Applications in the Component Tools Guide.
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.
|
To |
See |
|
Go to the next step |
Creating an HTML Page for Viewing and Updating DatavbconCreateSQLTableUsingDesigner |
|
Start from the beginning |
Creating a DHTML Application that Interacts with SQL Server DatavbconQuerySQLServerDatabase |
{ewc HLP95EN.DLL,DYNALINK,"See Also":"mdproCursorType;mdproLockType;vbconCreateSQLTableUsingDesignerC"}
To work with existing data in a SQL Server database, you can
create an HTML page that lets you view and edit the data. You can use the DHTML
Page designer to create a new HTML page or modify an existing page. Then write
code that uses
This topic shows how to create an HTML page to view and edit data
in the Customers table you created and populated in the previous topic in this
scenario. It shows how to use a data environment Command object,
To create an HTML page for viewing and update data
1. Add labels and TextField elements to a new HTML page.
2. Modify the data environment Command object to allow data updating.
3. Add code to display records on the HTML page.
4. Add Button elements to navigate through records.
5. Load the HTML page.
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 DatavbconQuerySQLServerDatabase.
The first step in creating an HTML page for viewing and updating data is to open a new HTML page, by selecting Add DHTML Page from the Project menu.
You can use the DHTML Page designer to add labels and TextField elements to the HTML page. For example, to create an HTML page for viewing and updating data in the Customers table in the Pubs sample database, follow the steps in the Creating a Data Entry HTML PagevbconCreateDataInputForm topic.
When you create a Command object using the Data Environment designer, the Command object by default allows you to read but not update the Command object's data source. To allow data updating, you must change the Command object's CursorType, CursorLocation, and LockType properties.
For example, you can change the Customers Command object's CursorType, CursorLocation, and LockType properties to allow data in the Customers table to be updated directly from your HTML page. In the Data Environment designer, select the Customers Command object and click the Properties button on the Data Environment toolbar. Click the Advanced tab, and then set the CursorLocation property to Use server-side cursors, the CursorType property to Keyset, and the LockType property to Optimistic.
Using the data environment,
First, add a reference to the BindingCollection object's type library to your project. To add the reference, select References on the Project menu, then select Microsoft Data Binding Collection in the References dialog box. Then declare a BindingCollection object variable in the HTML Page object's Declarations section:
Dim colBind As BindingCollection
To bind the elements to the table fields when the HTML page loads in your browser, add code to the DHTML Page object's Load event procedure. As you saw in the "Creating a Data Entry HTML Page" topic, your data environment can provide the "middle tier" between the table and the HTML page.
Use the BindingCollection object to bind the elements on the HTML page to fields in the recordset:
Private Sub DHTMLPage_Load()
' Create a BindingCollection object, then set its
' DataSource property to your data environment and its
' DataMember property to the Customers command object.
Set colBind = New BindingCollection
With colBind
Set .DataSource = MyDataEnvironment
.DataMember = "Customers"
' Bind the Value property of elements on the HTML page
' to fields in the Customers recordset.
.Add CustomerID, "Value", "CustomerID"
.Add CompanyName, "Value", "CompanyName"
.Add Address, "Value", "Address"
.Add Region, "Value", "Region"
.Add PostalCode, "Value", "PostalCode"
.Add Country, "Value", "Country"
End With
End Sub
For More Information For more information see BindingCollection object!ALink("vbobjBindingCollectionCollections") in the Language Reference.
After you've established data binding between your data environment and the object on the HTML page, you can easily create Button elements for navigating through records by using the recordset's MoveNext and MovePrevious methods. For example, to create a Next button for your HTML page, use the DHTML Page designer to add a Button element to the HTML page. Change its id and Name properties to Next. Then add the following line to the Button element's Next_onclick event procedure:
MyDataEnvironment.rsCustomers.MoveNext
Similarly, you can create a Previous button by adding a Button element to the HTML page and change its id and Name properties to Previous. Then add the following code to the Button element's Previous_onclick event procedure:
MyDataEnvironment.rsCustomers.MovePrevious
When you load the HTML page, Visual Basic displays the records in the Customers table and lets you move forward and backward through the recordset.
To view the finished page in Internet Explorer, press the F5 key or click the Start button on the Standard toolbar. You can also explicitly make a dynamic-link library and HTML page for the project by setting properties on the Make tab of the Project Properties dialog box, then selecting Make .dll from the File menu.
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.
|
To |
See |
|
Go to the next step |
Creating an HTML Page that Runs a Stored ProcedurevbconCreateNewQueryUsingDesigner |
|
Start from the beginning |
Creating a DHTML Application that Interacts with SQL Server DatavbconQuerySQLServerDatabase |
{ewc HLP95EN.DLL,DYNALINK,"See Also":"vbconCreateNewQueryUsingDesignerC"}
You can increase the power and flexibility of your HTML applications by enabling them to run SQL Server stored procedures. You can create a stored procedure in the Data View window, then create Command objects and elements that use the stored procedure to view or update data.
This topic shows how to create a stored procedure that returns customer address records by country from the Customers table created earlier in this scenario. It shows how to add a Select element to the HTML page you created in the previous topic. The Select element uses the stored procedure to display address records based on the selected country.
To create an HTML page that runs a stored procedure
1. Add a stored procedure to the database.
2. Create a data environment Command object based on the stored procedure.
3. Add Labels and TextField Elements to a new HTML Page.
4. Add a Select element to the HTML page.
5. Add code to run the stored procedure and display the results.
6. Load the HTML page.
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 DatavbconQuerySQLServerDatabase.
You can add a stored procedure to the database by right-clicking the Stored Procedures folder in the Data View window and selecting the New Stored Procedure command. The Data View window displays the New Stored Procedure window, where you enter the SQL Create Procedure statement that defines the stored procedure.
For example, you can add a spCustByCountry stored procedure to the Pubs database that accepts an input parameter, Country, and returns customer address information for the specified country. Enter the following Create Procedure statement in the New Stored Procedure window:
CREATE PROCEDURE spCustByCountry @Country varchar(20)
AS
SELECT * FROM Customers
WHERE Country = @Country
When you save the stored procedure, it is added to the database.
For More Information For more information on using the Data View window to create a stored procedure, see Stored Procedures in the SQL EditorvbconStoredProcSQLEditor.
In the "Create a Data Entry HTML Page" topic, you created a data environment Command object based on the Customers table in the Pubs sample database. You can also create a data environment Command object based on a stored procedure.
For example, you can create a data environment Command object based on the spCustByCountry stored procedure. Follow the steps in the "Interacting with Data in a Microsoft Jet/Microsoft Access Database" scenario, Chapter 5 of the Data Access Guide, to create the Command object, setting the following properties:
|
Property |
Setting |
|
Command Name |
CustByCountry |
|
Connection |
Connection1 |
|
Database Object |
Stored Procedure |
|
Object Name |
dbo.spCustByCountry |
After you've created the command that will provide the data source for your HTML page, you can create the page. Select Add DHTML Page from the Project menu. As you saw in the previous topics in this scenario, you can use the DHTML Page designer to add labels and TextField elements to the HTML page.
You can use the DHTML Page designer to add a Select element to your HTML page. A Select element, similar to a combo box, lets you select an item from a list.
For example, to add a Select element called SelectCountry to the page you created in the previous topic, drag the element onto the page. Then set the element's Name and id properties to SelectCountry.
You can specify the list items to be displayed in the Select element by adding code to the HTML page's Load event procedure. For example, to display a list of countries included in the address information from the Customers table, add the following code:
Private Sub DHTMLPage_Load()
' Declare Select element, connection, recordset, and string variables.
Dim selElement As HTMLSelectElement
Dim cnn As ADODB.Connection
Dim rsCountries As ADODB.Recordset
Dim strSQL As String
' Assign the list of countries from the Customers table
' to the recordset variable.
strSQL = "SELECT DISTINCT Country FROM Customers;"
Set cnn = New ADODB.Connection
cnn.Open MyDataEnvironment.Connection1
Set rsCountries = New ADODB.Recordset
rsCountries.Open strSQL, cnn1
' Fill the Select element's list with countries from the recordset,
' setting each list element's Text property.
With rsCountries
Do While Not .EOF
Set selElement = Me.Document.createElement("OPTION")
selElement.Text = !Country
SelectCountry.Options.Add selElement
.MoveNext
End With
End Sub
For More Information For more information on creating a Select element, see Working with Lists in the Page DesignervbconWorkingWithListsInPageDesigner in Building Internet Applications in the Component Tools Guide.
Using the data environment,
For example, you can display address records on your HTML page for customers from a country selected in the SelectCountry element. First, declare a BindingCollection object variable in the HTML Page object's Declarations section:
Dim colBind As BindingCollection
Then add code to the element's onchange event procedure to run the cmdCustByCountry Command object using the value of the element as an input parameter:
Private Sub SelectCountry_onchange()
Dim rsResults As ADODB.Recordset
Dim intIndex As Integer
' Run the CustByCountry Command object using the value
' of the SelectCountry element as an input parameter.
intIndex = SelectCountry.selectedIndex
MyDataEnvironment.CustByCountry SelectCountry.Options(intIndex).Text
' Create a BindingCollection object, then set its
' DataSource property to your data environment and its
' DataMember property to the CustByCountry Command object.
Set colBind = New BindingCollection
With colBind
Set .DataSource = MyDataEnvironment
.DataMember = "CustByCountry"
' Bind the Value property of elements on the HTML page
' to fields in the CustByCountry recordset.
.Add CustomerID, "Value", "CustomerID"
.Add CompanyName, "Value", "CompanyName"
.Add Address, "Value", "Address"
.Add Region, "Value", "Region"
.Add PostalCode, "Value", "PostalCode"
.Add Country, "Value", "Country"
End With
' Assign the recordset to the rsResults recordset variable.
Set rsResults = MyDataEnvironment.rsCustByCountry
' Add HTML code to dynamically create a table on the page
' showing each customer record.
Me.Document.body.insertAdjacentHTML "BeforeEnd", "<DIV><HR SIZE=2></DIV>"
With rsResults
Do While Not .EOF
Me.Document.body.insertAdjacentHTML "BeforeEnd", _
"<TABLE BORDER CELLSPACING=1 CELLPADDING=7 ><TR HEIGHT=17>" & _
"<TD WIDTH=70>" & rsResults!CustomerID & "</TD>" & _
"<TD WIDTH=200>" & rsResults!CompanyName & "</TD>" & _
"<TD WIDTH=300>" & rsResults!Address & "</TD>" & _
"<TD WIDTH=100>" & rsResults!City & "</TD>" & _
"<TD WIDTH=50>" & rsResults!Region & "</TD>" & _
"<TD WIDTH=50>" & rsResults!PostalCode & "</TD>" & _
"<TD WIDTH=50>" & rsResults!Country & "</TD></TR>" & _
"</TABLE>"
.MoveNext
.MoveFirst
.Close
End With
End Sub
To view the finished page in Internet Explorer, press F5 or click the Start button on the Standard toolbar. You can also explicitly make a dynamic-link library and HTML page for the project by setting properties on the Make tab of the Project Properties dialog box, then clicking Make .dll on the File menu.
This topic concludes a series that walks you through creating a simple DHTML application that interacts with data in an SQL Server database. To start from the beginning, see Creating a DHTML Application that Interacts with SQL Server DatavbconQuerySQLServerDatabase.
# vbconQuerySQLServerDatabase
T 183992
$ Creating a DHTML Application that Interacts with SQL Server Data
A vbconDevelopingClientInternetApplicationsC;vbconQuerySQLServerDatabase;vbconRetrievingDataFromHTMLPage_C;vbconWritingCodeUsingDynamicHTML_C;
K DHTML data access applications, creating;
# vbconConnectToSQLServerDatabase
T 183993
$ Adding a Table to an SQL Server Database
A vbconConnectToSQLServerDatabase;
K DHTML data access applications, adding a table to SQL database;
# vbconCreateDataInputForm
T 183997
$ Creating a Data Entry HTML Page
A vbconCreateDataInputForm;
K DHTML data access applications, data entry page;
# vbconCreateSQLTableUsingDesigner
T 183994
$ Creating an HTML Page for Viewing and Updating Data
A vbcolBindingCollectionCollections_C;vbconCreateSQLTableUsingDesigner;
K DHTML data access applications, viewing and updating data;
# vbconCreateNewQueryUsingDesigner
T 183995
$ Creating an HTML Page that Runs a Stored Procedure
A vbconCreateNewQueryUsingDesigner;vbconStoredProcSQLEditor_C;
K DHTML data access applications, running stored procedures;