Configure a data source by using a data source control

  • Article

Microsoft Expression Web enables you to connect to data from a database by using an ASP.NET data source control. For a list of ASP.NET data source controls, see Using databases.

Note

If you want to configure a data source by using a data bound control, see Configure a data source by using a data bound control.

With Expression Web, you can connect to a variety of data sources, including Microsoft Access databases, Microsoft SQL databases, Oracle databases, and databases that are accessible by using OLE DB or ODBC protocols.

Important

Microsoft Access databases (.mdb files) are less secure than Microsoft SQL Server databases. If possible, consider moving your data to a SQL database such as SQL Express. However, if you need to use an .mdb file as part of your web application, follow the guidelines in the "Microsoft Access Databases" section in Securing Data Access Cc295428.xtlink_newWindow(en-us,Expression.40).png in the MSDN library.

This topic shows you how to insert an ASP.NET data source control and then shows you how to connect the data source control to a database. This topic also shows you how to configure a SELECT statement to display data from your data source in a web page after you have configured the data source connection.

To configure a data source

  1. Do one of the following:

    • On the Panels menu, click Data Source Library. In the Data Source Library panel, under ASP.NET Connections, click New ASP.NET Connection.

      Tip

      If the ASP.NET Connections heading is collapsed, click the plus sign (+) to expand it.

    • In Design view, in your .aspx page, from the Toolbox panel, drag one of the ASP.NET data source controls such as SQLDataSource onto the page.

      Note

      You can save the .aspx page before continuing by clicking File, and then clicking Save.

    Right-click the control, and then click Configure Data Source. In the Configure Data Source dialog box, click New Connection.

  2. In the Choose Data Source dialog box, click one of the following:

    • Microsoft Access Database File   Use this option to connect to an Access database using the native Jet provider through the .NET Framework Data Provider for OLE DB.

    • Microsoft ODBC Data Source   Use this option to specify an ODBC user or system data source name to connect to an ODBC driver through the .NET Framework Data Provider for ODBC.

    • Microsoft SQL Server   Use this option to connect to Microsoft SQL Server 2000 or 2005 using the .NET Framework Data Provider for SQL Server.

    • Microsoft SQL Database File   Use this option to attach a database file to a local instance of SQL Server (which can be an instance of Microsoft SQL Express) using the .NET Framework Data Provider for SQL Server.

    • Oracle Database   Use this option to connect to Oracle 7.3, 8i or 9i using the .NET Framework Data Provider for Oracle.

    • Other   Use this option to connect to a type of database not described earlier in this list.

  3. Select the Always use this selection box if you want to always connect to the same type of data source, or clear the Always use this selection box if you want to connect to a different type of data source each time you configure a data source. Click OK.

    Note

    When you select Other, the Data provider list displays the data provider appropriate for your data source.

  4. Now that you have selected the type of data source to which you want to connect, you are ready to connect the data source to the ASP.NET control. For more information, locate the type of data that you are using in the following chart.

    If you specified See this section

    Microsoft Access Database File

    To configure an Access database

    Microsoft ODBC Data Source

    To configure an ODBC data source

    Microsoft SQL Server

    To configure an SQL Server data source

    Microsoft SQL Database File

    To configure an SQL database

    Oracle Database

    To configure an Oracle database

    Other

    To configure another kind of database

To configure an Access database

  1. On the Connection Properties dialog box, in the Database file name box, type the relative path of the Microsoft Access database file or click Browse to locate the file.

  2. In the Log on to the Database section, type the user name and password in the User name and Password boxes. If you want to save the password, select the Save my password check box.

    Note

    To view more information about the connection, click Advanced. When you have finished, click OK.

  3. Click Test Connection to verify that your connection has been established, and then click OK.

  4. In the Configure Data Source dialog box, click Next.

  5. To save the connection string, under Do you want to save the connection in the application configuration file, select Yes, save this connection as, and then type a name for the connection string, or keep the string that Expression Web provides for you. Otherwise, clear the check box. Click Next.

  6. Now that you have selected the type of data source to which you want to connect, you are ready to configure the SELECT statement. For more information, see the "To configure a SELECT statement" section later in this topic.

To configure an ODBC data source

  1. In the Connection Properties dialog box, under Data source specification, do one of the following:

    • Select Use user or system data source name, and then locate the data source in the drop-down list.

    • Select Use connection string, and then type the connection string or click Build to locate the data source and build the connection string.

  2. In the Login information section, type the user name and password in the User name and Password boxes.

    Note

    To view more information about the connection, click Advanced. When you have finished, click OK.

  3. Click Test Connection to verify that your connection has been established, and then click OK.

  4. Now that you have selected the type of data source to which you want to connect, you are ready to configure the SELECT statement. For more information, see the "To configure a SELECT statement" section later in this topic.

To configure an SQL Server data source

  1. In the Connection Properties dialog box, in the Server name box, type the name of the server on which your database resides.

  2. Under Log on to the server box, do one of the following:

    • Select Use Windows Authentication.

    • Select Use SQL Server Authentication, and then type the user name and password in the User name and Password boxes.

  3. In the Connect to a database section, do one of the following:

    • Select Select or enter a database name, and then locate the data source in the drop-down list.

    • Select Attach a database file, and then click Browse to locate the file.

    Note

    To view more information about the connection, click Advanced. When you have finished, click OK.

  4. Click Test Connection to verify that your connection has been established, and then click OK.

  5. Now that you have selected the type of data source to which you want to connect, you are ready to configure the SELECT statement. For more information, see the "To configure a SELECT statement" section later in this topic.

To configure an SQL database

  1. In the Connection Properties dialog box, in the Database file name box, type the name of your database, or click Browse to locate it.

  2. Under Log on to the server box, do one of the following:

    • Select Use Windows Authentication.

    • Select Use SQL Server Authentication, and then type the user name and password in the User name and Password boxes. If you want to save the password, select the Save my password check box.

    Note

    To view more information about the connection, click Advanced. When you have finished, click OK.

  3. Click Test Connection to verify that your connection has been established, and then click OK.

  4. Now that you have selected the type of data source to which you want to connect, you are ready to configure the SELECT statement. For more information, see the "To configure a SELECT statement" section later in this topic.

To configure an Oracle database

  1. In the Connection Properties dialog box, in the Server name box, type the name of the server on which your database resides.

    Note

    The ODBC Driver for Oracle must be installed if you want to connect to the server on which your database resides. For more information, see the ODBC Driver for Oracle User's Guide Cc295428.xtlink_newWindow(en-us,Expression.40).png in the MSDN Library.

  2. In the Log on to the Database section, type the user name and password in the User name and Password boxes. If you want to save the password, select the Save my password check box.

    Note

    To view more information about the connection, click Advanced. When you have finished, click OK.

  3. Click Test Connection to verify that your connection has been established, and then click OK.

  4. Now that you have selected the type of data source to which you want to connect, you are ready to configure the SELECT statement. For more information, see the "To configure a SELECT statement" section, later in this topic.

To configure another kind of database

  1. In the Connection Properties dialog box, in the Server name box, type the name of the server on which your database resides.

  2. In the Log on to the Database section, type the user name and password in the User name and Password boxes. If you want to save the password, select the Save my password check box.

    Note

    To view more information about the connection, click Advanced. When you have finished, click OK.

  3. Click Test Connection to verify that your connection has been established, and then click OK.

  4. Now that you have selected the type of data source to which you want to connect, you are ready to configure the SELECT statement. For more information, see the "To configure a SELECT statement" section later in this topic.

To configure a SELECT statement

In the Configure the Select Statement dialog box, under How would you like to retrieve data from your database, do one of the following:

  • Select Specify a custom SQL statement or stored procedure and see the "To specify a custom SQL statement or stored procedure" section later in this topic.

  • Select Specify columns from a table or view and see the "To specify columns from a table or view" section later in this topic.

To specify a custom SQL statement or stored procedure

  1. In the Define Custom Statements or Stored Procedures dialog box, under Click a tab to create a SQL statement for that operation, click one of the following:

    • Select   To retrieve data from a table.

    • Update   To modify data in a table.

    • Insert   To insert new rows into a table.

    • Delete   To delete rows from a table.

    Alternately, select one of the stored procedures from the Stored procedure list.

    Note

    The Stored Procedure list displays all of the procedures that are available in the database that you are connected to. If there are no stored procedures in the database, the list is unavailable.

  2. After you have created your SQL statement, you can further customize the query by clicking one of the following:

    • Where   To filter data by defining certain conditions.

    • Order by   To define the sort order of the data.

    • Advanced   To generate INSERT, UPDATE, and/or DELETE statements.

  3. Click OK. In the Test Query dialog box, click Test Query to test your SQL statement. A preview of your data appears in the Test Query box, and a preview of your SQL statement appears below the Test Query box.

  4. Click Finish.

To specify columns from a table or view

  1. In the Name list, select the table from which you want to retrieve and display data.

  2. In the Columns box, select the columns from which you want to retrieve and display data.

  3. After you have created your SQL statement, you can further customize the query by clicking one of the following:

    • Where   To filter data by defining certain conditions.

    • Order by   To define the sort order of the data.

    • Advanced   To generate INSERT, UPDATE, and/or DELETE statements.

  4. Click OK. In the Test Query dialog box, click Test Query to test your SQL statement. A preview of your data appears in the Test Query box, and a preview of your SQL statement appears below the Test Query box.

  5. Click Finish.

See also

Concepts

ASP.NET Data controls
Working with data
Using databases
Configure a data source by using a data bound control

Send feedback about this topic to Microsoft. © 2011 Microsoft Corporation. All rights reserved.