Figure 2-2
(13) The last action is to display data from MDB query instead of a table. Close the browser and return to VWD. Ch02_tio_2_displayaccessdata-query.aspx Save the file with the new name. Select the GridView and open its common Task Menu by clicking the small arrow at the top right of the control. Click Configure Data source and next to skip the selection of the MDB file. On the interface of Configure Select statement, change the name to "Sales category" and click the asterisk to get all the fields. This query uses joins to combine four tables, limit the number of records used in one table, and obtain totals by grouping and aggregating operations on some data. Click Next, and then Test the query and finish. The data source control should look like the following:
(14) Open the browser and view the page.
Example Description #2--connects to an MDB and displays data in GridView
You have two controls on the page. The first is AccessDataSource, which performs all the work connected to the Ado.net object, and these objects connect to the jet engine that interacts with the MDB file. The second is the GridView, which is used to get the data and convert it into the HTML format that the page can display. Note that it is important to have a meaningful name (ID) for each control. You must then determine that the data-bound control (GridView) uses its DataSourceID property to refer to the AccessDataSource's ID so that AccessDataSource can be used as its data source.
Displaying data from a query is not very difficult; in Access you can use the query name without the table name. Here are two instructions. First, if a table or query has spaces between the names, you must enclose the entire name in square brackets. Second, the techniques that require user input (for example, "Sales in a specified year" need to know which year) are not covered in this book.
After you complete the previous steps, you can see the data from the AccessDataSource control displayed on the page. The GridView is described in more detail in later chapters, but this chapter will continue to focus on data source controls.
Select a variable in a statement
When you create a data source in vWD, the wizard will ask you to specify the columns you want to display or to create a custom SQL statement. In the previous "Try it," you simply select a few columns. You can specify more complex SQL statements in several different ways:
Use an interactive dialog box
Enter a custom SQL statement in the AccessDataSource Wizard (called by the Configure Data source shortcut task in the control)
Enter statements in the property grid
Enter statements directly in the markup of the source view
Selecting a column in the Access DataSource Wizard is a preferred technique for simple queries that return one or more columns from a separate table, because it reduces the errors caused by typography and grammar (see Figure 2-3). In this wizard, you can select the name of a table or query from the name Drop-down list. You can then select all Columns (*), or any set of columns, to choose the columns you want in the table or query. If you click the Order by button, you can sort by any column in the data source. If the first column has a constraint, the field selected in then by will be used. When you select an option from the wizard, note that the actual SQL syntax for SelectCommand also appears in a read-only text box.
Figure 2-3
Use the Where button in the wizard to create an SQL statement with parameters, which will be covered in chapter 9th. We will now skip this choice, but it is important that you use the parameters that will be discussed later in this book before you deploy. In a deployed site, avoid connecting the user's input directly to the SQL statement. Ignoring parameter problems will expose the site to the risk of SQL injection attacks. This attack technique uses false characters that are entered from the user to invalidate the original SQL statement and then replaces it with a destructive statement. You can use the parameter collection to send user input to the Ado.net parameter collection, which reduces the problem of SQL injection by leveraging the functionality of the collection.
Although the Configure Data Source Wizard has many options to help you create SQL statements quickly, there are times when you need to enter (or modify) SQL statements directly in the markup's SelectCommand. These operations are allowed by the AccessDataSource Wizard. In the Select table, query, and Column wizard pages, you can select the radio button "Specify a custom SQL statement or stored procedure." When you select this option in the wizard page, clicking the Next button will go to another page where you can enter a custom statement directly into the text area. You can also use visual Studio QueryBuilder to visually create custom statements, and the tools you use are very similar to those used by Access QueryBuilder.
If you do not want to use the wizard, you can choose to enter a custom SQL statement in the property grid of the AccessDataSource control, or you can switch to the source view and enter the statement in the SelectCommand property of the AccessDataSource control's markup.
There are a lot of books about SQL (like this book type andbeginning SQL Programming, ISBN 1-861001-80-0), this book provides a brief introduction in the Appendix. If you plan to learn SQL syntax, you can begin by learning the commands that return a portion of the record (top and distinct), the syntax for renaming one field (as), and the technique (JOIN) that returns fields from two associated tables. The following exercise explores some of the variables in the SQL statement.
Try #3--change the selection statement in AccessDataSource
In this exercise, you will display specific columns and specific records from the Northwind Products table. A page is also created to display the data that is obtained from the query. Please note that in this exercise, the selection rule is already in the source code. Currently, user input has not been selected as an option.
(1) Create a file named Ch02_tio_3_alternateselect-commands.aspx in C:\Websites\BegAspNet2Db\ch02\.
(2) Add a AccessDataSource control with Northwind as the ID and set the MDB to \app_data\northwind.mdb. In a series of dialog boxes, set the Select command to get all the fields ("name" =products) from the Products table. Look at the source view and note the statement you created, as follows:
SELECT * FROM Products
(3) Add the GridView, set the ID to Northwind, and set the data source to Northwind. As shown below is the entire page so far:
<%@ page language= "VB"%>
ch02 TIO 3 alternate Select conimands
ch02 TIO 3 alternate Select coinmands
(4) Now open the page in the browser and note the interface shown in Figure 2-4 (no columns are displayed in the screenshot).
Figure 2-4
(5) Return to Design view and select AccessDataSource (not GridView). Open its Convenience task pane and click Configure Data Source. In the Configure Select Stat ement window, select Specify a custom SQL statement and click Next. Modify the SQL statement in the editor as follows:
SELECT * FROM [Products] WHERE (categroyid=3)
(6) Click Next and test the query. Click OK to close the dialog box. Look at the page in Source view and note the following code:
Selectcommand= "SELECT * from [Products] WHERE (CategoryID = 3)"
Datafile= "~app_data/northwind.mdb" >
(7) Try using a dialog box to create some of the following select commands, or you can enter them in the source view, or use them together.
SELECT * FROM products WHERE ProductID = 12
SELECT * from the products WHERE ProductName = ' northwoods cranberry sauce '
SELECT * from the products WHERE ProductID <11
SELECT * from the products WHERE ProductID <11 ORDER by ProductName Ascending
SELECT * from the products WHERE SupplierID = 6 OR SupplierID = 8
SELECT * FROM products WHERE SupplierID = UnitPrice > 10
Example Description #3--Change the selection statement in AccessDataSource
In the first few steps, a AccessDataSource control and a GridView are created as before. But then we started experimenting with the SELECT statement. Adding and removing columns by check boxes is most intuitive. You can also enter (or edit) the SQL statement directly in the editor window.
Added a WHERE clause to restrict the records returned from the MDB file. If the column name does not contain spaces, you can omit the square brackets. If you include a space in the column name, you must use these brackets.
the variables in the MDB file location
The MDB file is saved in various physical locations on your hard disk: the same folder as the Web page, a subfolder under a Web page, or another folder in your machine. In the visual Web developer, these files are often browsed in the designer, and the correct path to the MDB file has been entered. However, if you intend to enter your own code, you will also need to follow the syntax described in this section.
The DataFile property of the AccessDataSource control contains a page path that can be fully qualified (for example, starting with a drive letter), or it can be specified relative to the location of the page containing the AccessDataSource. The path can also be relative to the application, that is, using the URL syntax to refer to the path. This syntax replaces the symbol (~) with the application root directory, for example: ~\app_data\produces.mdb. Using a path relative to an application makes it easier to move a page from one location to another without breaking references to the database, so it is recommended that you use a path relative to the application whenever possible.
First, let's look at the fully qualified path syntax, which contains the entire path of the MDB file, beginning with the root directory of the computer drive where the file resides:
<asp:accessdatasource ...
Datafile= "C:\WebSites\WebApplication\App_Data\MyMdb.mdb" >
Although this syntax can be used, its disadvantage is that it reduces the portability of the application. If you want to move the application to another machine, the fully qualified path to the drive or file will be different, and you will need to modify the DataFile property each time you move the page.
An improvement to a fully qualified path is the use of relative path syntax, which only needs to specify a part that is not the same as the fully qualified path of the page containing the AccessDataSource. The following is the path syntax for the database file in the same folder as the page, specified as a relative path:
<asp:accessdatasource ... datafile= "Mymdb.mdb"
Because the pages and MDB files are in one directory, the paths to these files are not different; therefore, you only need to specify their own file names. If the MDB is in the next or more level directory of the folder, the following syntax is very similar to the preceding. In such cases, you only need to specify the subfolder name and then add the slash and MDF file name:
<asp:accessdatasource ... datafile= "Mydaughterfolder/mymdb.mdb"
If the MDB file is in the parent directory of the page directory, you can also use the double-period syntax to specify the top level of the current location:
<asp:accessdatasource ... datafile= ". /mymdb.mdb ">
By using relative path syntax, you can easily move an application from one place to another without changing the path of the MDB file. Because pages and MDB files are always inseparable (if the MDB file is part of the application), the relative positions of these files are always kept consistent. But what happens when you plan to move a path within your application, such as moving a page into a subdirectory? In this case, the relative position between the page and the MDB file has changed, so the relative path syntax is not correct.
The application-relative syntax solves the problem. At this point, the path is always specified relative to the application root directory, not the page itself. The root is represented as a symbol (~):
<asp:accessdatasource ... datafile= "~app_data/mymdb.mdb>
You can now easily move the application, or move the page within the application without changing the reference to the database. Therefore, you should use the application-relative path whenever possible.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
