| VSView Reporting Edition Reference > DataSource Object > DataSource Properties > RecordSource Property |
Sets or returns a string that specifies the source recordset for the report.
dataSource.RecordSource[ = value As String ]
The string can be either a table name, a query (or stored procedure), or an SQL statement.
Table Names
Using table names and queries is straightforward. For example, the NorthWind database that ships with Microsoft Visual Studio contains the following tables: Categories, Orders, Shippers, Customers, Employees, Order Details, Products, and Suppliers. When the ConnectionString property is set to the NorthWind database, setting RecordSource to any of these values will retrieve the corresponding table. For example:
vsr.DataSource.ConnectionString = "DSN=NorthWind"
vsr.DataSource.RecordSource = "Employees"
SQL Statements
Using SQL statements is just as simple, but creating the SQL statement can be complicated. For this task, you will normally use a query-building tool that generates the SQL statement for you. Among others, you can use Microsoft Access or ComponentOne's TrueDBQuery. For example:
Dim strSQL$
strSQL = "SELECT DISTINCTROW * FROM Categories " & _
"INNER JOIN Products " & "
"ON Categories.CategoryID = Products.CategoryID " & _
"WHERE ((Products.Discontinued)=No);"
vsr.DataSource.ConnectionString = "DSN=NorthWind"
vsr.DataSource.RecordSource = strSQL
Parameter Queries
When using SQL statements, you have the option of creating Parameter Queries, which prompt the user for criteria used to select the records. The syntax used to create parameter queries is based on SQL, and is the same as that used by Microsoft Access.
The easiest way to create a parameter query is to start with a plain SQL statement with one or more items in the WHERE clause, then manually replace the fixed values in the clause with parameters. For example, starting with the plain SQL statement:
strSQL = "SELECT DISTINCTROW * FROM Employees " & _
"INNER JOIN (Orders INNER JOIN [Order Subtotals] " & _
"ON Orders.OrderID = [Order Subtotals].OrderID) " & _
"ON Employees.EmployeeID = Orders.EmployeeID " & _
"WHERE (((Orders.ShippedDate) " & _
"Between #1/1/1994# And #1/1/2001#));"
The next step is to identify the parts of the SQL statement that will be turned into parameters. In this example, the parameters are the dates in the WHERE clause, shown above in boldface. Let's call these parameters Beginning Date and Ending Date. Since these names contain spaces, they need to be enclosed in square brackets:
strSQL = "SELECT DISTINCTROW * FROM Employees " & _
"INNER JOIN (Orders INNER JOIN [Order Subtotals] " & _
"ON Orders.OrderID = [Order Subtotals].OrderID) " & _
"ON Employees.EmployeeID = Orders.EmployeeID " & _
"WHERE (((Orders.ShippedDate) " & _
"Between [Beginning Date] And [Ending Date]));"
Finally, the parameters must be identified in the beginning of the SQL statement with a PARAMETERS clause that includes the parameter name, type, and default value:
strSQL = "PARAMETERS [Beginning Date] DateTime 1/1/1994, " & _
"[Ending Date] DateTime 1/1/2001;" & _
"SELECT DISTINCTROW * FROM Employees " & _
"INNER JOIN (Orders INNER JOIN [Order Subtotals] " & _
"ON Orders.OrderID = [Order Subtotals].OrderID) " & _
"ON Employees.EmployeeID = Orders.EmployeeID " & _
"WHERE (((Orders.ShippedDate) " & _
"Between [Beginning Date] And [Ending Date]));"
When this statement is executed, the control shows a dialog box prompting the user for Beginning Date and Ending Date values. The values supplied by the user are plugged into the SQL statement and the report is generated only for the period selected by the user.
The syntax for the PARAMETERS clause consists of a comma-separated list of items, ending with a semi-colon. Each item describes one parameter, and includes the following information:
· Parameter name.
If the name contains spaces, it must be enclosed in square brackets (for example, [Beginning Date]). The parameter name appears in the dialog used to collect the information from the user, and it also appears in the WHERE clause of the SQL statement, where it is replaced with the value entered by the user.
· Parameter type.
The following types are recognized by the control:
|
Type Name |
ADOType |
|
Date |
adDate |
|
DateTime |
adDate |
|
Bit |
adInteger |
|
Byte |
adInteger |
|
Short |
adInteger |
|
Long |
adInteger |
|
Currency |
adCurrency |
|
Single |
adSingle |
|
Double |
adDouble |
|
Text |
adBSTR |
|
String |
adBSTR |
|
Boolean |
adBoolean |
|
Bool |
adBoolean |
|
YesNo |
adBoolean |
· Default value.
This is the value initially displayed in the dialog box.
Note that you could also write code in Visual Basic or in C++ to create a dialog box, get the information from the user, and fix the SQL statement or set the Filter property as needed. The advantage of using Parameter Queries is that it places the parameter logic in the report itself, and is independent of the viewer application.
String