If you create a Select script for a table that calls a stored procedure, you may have to prompt the user for the value of a parameter to pass to the procedure. This method allows you to do that. It's similar to GetValuesForField, except the parameter isn't associated with any field; that is, you have to specify the caption, data type, length, and decimals, and the Values button the user normally sees when entering a filter value isn't available.
Syntax
GetValuesForParameter(Caption as String, DataType as Character,
Length as Integer, Decimals as Integer, Operator as String
[, Values as Collection]) as Object
Parameters
Caption
The caption to display to the user.
DataType
The data type for the value. See the Field Object topic for a list of valid data type values.
Length
The maximum size of the value.
Decimals
The number of decimal places.
Operator
The operator to use; one of the following:
- equals
- not equals
- is not equal
- begins with
- not begins with
- does not begin with
- contains
- not contains
- does not contain
- greater than
- is greater than
- greater than or equal
- is greater than or equal
- less than
- is less than
- less than or equal
- is less than or equal
- between
- is between
- not between
- is not between
- not is between
- is one of
- is not one of
- not is one of
- is blank
- is not blank
- not is blank
- is known
- is unknown
- is yes
- is no
Values
A collection of values used as the default values for the dialog. Use the GetValuesCollection method to create the collection and its AddItem method to add the default values. Supply two values for the between and not between operators, up to ten values for the is one of and is not one of operators, and only one value for the rest of the operators. This parameter is optional; if it isn't specified, blank values are used as defaults.
Return Value
A collection of value objects. A value object has a single property, Value, which contains the value the user entered for the field value. If the user chooses Cancel rather than entering a value, the collection Count property is 0. Otherwise, the collection contains two members for the between and not between operators, up to ten members for the is one of and is not one of operators, and one member for the others.
Example
This example, used for the Select script for a table, calls a stored procedure named SalesByCategory to get the data for the table. This code calls the GetValuesForParameter method to get the date parameter.
Visual FoxPro
lparameters toApplication as SQApplication, toDatabase as Database, ;
tcSelect, tcCursor
local lcField, loValues, loValue, lcCategory, lcYear, ;
lcSelect, llReturn
lcField = 'Categories.CategoryName'
loValues = toApplication.DataEngine.GetValuesForField(lcField, ;
'equals')
if loValues.Count > 0
loValue = loValues.Item(1)
lcCategory = ['] + loValue.Value + [']
loValues = toApplication.DataEngine.GetValuesForParameter('Year', ;
'C', 4, 0, 'equals')
if loValues.Count > 0
loValue = loValues.Item(1)
lcYear = ['] + loValue.Value + [']
lcSelect = 'exec SalesByCategory ' + lcCategory + ', ' + lcYear
llReturn = not empty(toDatabase.ExecuteSQLStatement(lcSelect, ;
.NULL., tcCursor))
endif
endif
return llReturn
VBScript
function Main(Application, Database, SelectStatement, CursorName)
dim Values, Value
Main = ""
Field = "Categories.CategoryName"
set Values = Application.DataEngine.GetValuesForField(Field, _
"equals")
if Values.Count > 0 then
set Value = Values.Item(1)
Category = "'" & Value.Value & "'"
set Values = Application.DataEngine.GetValuesForParameter("Year", _
"C", 4, 0, "equals")
if Values.Count > 0 then
set Value = Values.Item(1)
Year = "'" + Value.Value + "'"
SelectStmt = "exec SalesByCategory " & Category & ", " & Year
Main = Database.ExecuteSQLStatement(SelectStmt)
end if
end if
end function
JavaScript
function Main(Application, Database, SelectStatement, CursorName) {
var XMLResult, Field, Values, Value, Category, SelectStmt, Year ;
XMLResult = "" ;
Field = 'Categories.CategoryName' ;
Values = Application.DataEngine.GetValuesForField(Field,
'equals') ;
if (Values.Count > 0) {
Value = Values.Item(1) ;
Category = "'" + Value.Value + "'" ;
Values = Application.DataEngine.GetValuesForParameter('Year',
'C', 4, 0, 'equals') ;
if (Values.Count > 0) {
Value = Values.Item(1) ;
Year = "'" + Value.Value + "'" ;
SelectSmt = 'exec SalesByCategory ' + Category + ', ' + Year ;
XMLResult = Database.ExecuteSQLStatement(SelectStmt +
Category + ', ' + Year) ;
}
}
return XMLResult ;
}
C#
public static string Salesbycategory_Select(SFQApplication sfqApplication,
Database database, string selectStatement, string cursorName)
{
string resultSet = String.Empty;
string field = "Categories.CategoryName";
Values values = sfqApplication.DataEngine.GetValuesForField(field, "equals");
if (values.Count > 0)
{
ValueItem value = values.Item(1);
string category = "'" + value.Value + "'";
values = sfqApplication.DataEngine.GetValuesForParameter("Year", "C", 4, 0,
"equals");
if (values.Count > 0)
{
value = values.Item(1);
string year = "'" + value.Value + "'";
string selectStmt = "exec SalesByCategory " + category + ", " + year;
resultSet = database.ExecuteSQLStatement(selectStmt);
}
}
// Return a string containing your result data
return resultSet;
}
VB.NET
public shared function Salesbycategory_Select(sfqApplication as SFQApplication,
database as Database, selectStatement as string, cursorName as string) as string
Dim resultSet As String = String.Empty
Dim field As String = "Categories.CategoryName"
resultSet = database.ExecuteSQLStatement(selectStatement)
Dim values As Values = sfqApplication.DataEngine.GetValuesForField(field, "equals")
If values.Count > 0 Then
Dim value As ValueItem = values.Item(1)
Dim category As String = "'" + value.Value + "'"
values = sfqApplication.DataEngine.GetValuesForParameter("Year", "C", 4, 0,
"equals")
If values.Count > 0 Then
value = values.Item(1)
Dim year As String = "'" + value.Value + "'"
Dim selectStmt As String = "exec SalesByCategory " + category + ", " + year
resultSet = database.ExecuteSQLStatement(selectStmt)
End If
End If
' Return a string containing your result data
Return resultSet
End Function
The code starts by calling the GetValuesForField method of the DataEngine object of the passed Application object. This method displays a dialog from which the user can enter a filter value for a field; this value is passed to the stored procedure this code calls. GetValuesForField returns a values collection, so if the user entered any values, the first item in the collection is retrieved. Next, the GetValuesForParameter method is called. This method is similar to GetValuesForField, except it prompts the user for a parameter value that isn't associated with a field. If the user chose a value, the first item in the values collection is retrieved, and the two parameter values are passed to the SalesByCategory stored procedure by calling the ExecuteSQLStatement method of the passed Database object. The VBScript and JavaScript code returns the result set as XML and the Visual FoxPro code creates a cursor.
See also
DataEngine Object | GetValuesForField | Select© Stonefield Software Inc., 2023 • Updated: 06/06/16
Comment or report problem with topic