ComponentOne VSView Reporting Edition
RecordSource Property

Sets or returns a string that specifies the source recordset for the report.

Syntax

dataSource.RecordSource[ = value As String ]

Remarks

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.

Data Type

String

 

 


Copyright (c) GrapeCity, inc. All rights reserved.

Product Support Forum  |  Documentation Feedback