Parameters Object

SQL*XL home

SQLXL
	Database
		Parameters
			Parameter

Description:

The parameters object is owned by the Database object. It encapsulates all SQLXL functionaly around variables/parameters. It is e.g. used to extract the parameters from SQL and to manage the parameters list.

Properties:

Name	Data Type	Description
Count	Long	Returns the number of parameters in the collection

Methods:

Name	Data Type	Description
BindVariables(Index As Variant)	Object	Returns the Parameter object for a bind variable.
SubstVariables(Index As Variant)	Object	Returns the Parameter object for a subsitution variable

Example:

Connect to the database, define a query with a parameter, set basic properties of the parameter and execute the query before logging off:

SQLXL.InitialiseSQLXL
SQLXL.Database.Connect UserName:="scott", Password:="tiger", DBAlias:="", ConnectionType=litOO4O
SQLXL.Sql.SetText "select * from authors where au_id< :max_au_id"
Set SQLXL.Sql.Statements(1).Target= Targets( litExcel )
With Targets(litExcel)
    .AutoFilter = False
    .AutoFit = True
    .Headings = True
    .Sort = False 
    .StartFromCell = "$A$1"
    .Transpose = False
    .SQLInNote = True
End With
SQLXL.Database.Parameters.BindVariables("max_au_id").Value = "10"
SQLXL.Database.Parameters.BindVariables("max_au_id").Mode = litTypeNumber
SQLXL.Database.Parameters.BindVariables("max_au_id").Mode = litParamIn
With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
    .Execute
End With
SQLXL.Database.DisConnect