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.