Getting a Specific Object from a Collection

You must get a specific object from a collection before you can use it.

Visual Basic

Because the Item method is the default method of a collection object, you can get a specific object in a collection by specifying one of the following:

• Object name
• Ordinal value (1-based) within the collection

in parentheses after the collection object. You can also use the Item method explicitly. These use the following syntax:

Parent.Objects( {strName | lOrdinal} )

Or

Parent.Objects.Item( {strName | lOrdinal} )

For example, to refer to the pubs database of the SQLServer.Databases collection:

oSQLServer.Databases("pubs")

To refer to the first database of the SQLServer.Databases collection:

oSQLServer.Databases(1)

A parent object has a default collection. You can use the ! default collection specifier instead of the default collection name and item name specifier. Use the following syntax:

Parent!name

The following table lists the default collections for parent SQL-DMO objects.

For example, to refer to the pubs database of the default Databases collection of the SQLServer object:

oSQLServer!pubs

Some SQL-DMO collections support the ItemByID method. This allows you to get a specific object using that object's unique SQL Server identifier. Use the following syntax:

Parent.Objects.ItemByID( lID )

For example, to refer to the database of the SQLServer.Databases collection that has a SQL Server database ID of 1:

oSQLServer.Databases.ItemByID(1)

C++

When you have a pointer to a collection object, you can get a pointer to a specific object in that collection by using the GetItemByOrd or GetItemByName function, or (for collections that support it) by using the GetItemByID function.

To use GetItemByOrd, pass an ordinal value (0-based) and the address of an object variable (use the address of & operator) of the appropriate type (LPSQLOLEOBJECT) to the function. It will write the specific object pointer to your object variable. Use the following syntax:

pObjects->GetItemByOrd ( lOrdinal, &pObject );

To use GetItemByName, pass the name of the specific object and the address of an object variable of the appropriate type (LPSQLOLEOBJECT) to the function. It will write the specific object pointer to your object variable. Use the following syntax:

pObjects->GetItemByName ( strName, &pObject );

Some SQL-DMO objects support the GetItemByID function. This allows you to get a specific object using that object's unique SQL Server identifier. To use GetItemByID, pass the unique object ID and the address of an object variable of the appropriate type (LPSQLOLEOBJECT) to the function. It will write the specific object pointer to your object variable. Use the following syntax:

pObjects->GetItemByID ( lID, &pObject );

For example, to get the pubs database from the SQLServer.Databases collection:

LPSQLOLEDATABASES pDatabases;
pSQLServer->GetDatabases (&pDatabases);
LPSQLOLEDATABASE pDatabase;
pDatabases->GetItemByName (TEXT("pubs"), &pDatabase);

The SQL-DMO custom interfaces also include "shortcut" functions that allow you to get a specific object in a collection directly from the parent object. These single functions can be used instead of getting the collection object first, then getting the specific object. Use the following syntax:

pParent->GetObjectByName ( strName, &pObject );

pParent->GetObjectByOrd ( lOrdinal, &pObject );

pParent->GetObjectCount ( &lCount );

For example, to get the pubs database from the SQLServer.Databases collection:

LPSQLOLEDATABASE pDatabase;
pSQLServer->GetDatabaseByName (TEXT("pubs"), &pDatabase);