TADO5 Tutorial

The TADO5 object allows you to work with any Relational Database from APL+Win. TADO5 exploits the Microsoft ADO™ (ActiveX Data Objects) technology.

With TADO5, you may:

l connect to a Relational Database (MS Access, SQL Server, Oracle, etc.)

l query information about existing Tables, Columns, Stored Procedures in your database

l execute any SQL statement on this database

l execute Stored Procedures (only available for Microsoft SQL Server)

l retrieve "last error" information

Connecting to the Database

The first thing to do after intantiating the TADO5 object is to connect to a database, using the Open method:

'ado'Œwi'*Create' 'TADO5'

ado

Before doing so, you may specify a Provider. If you don't do so, the MSDASQL Provider will be used, i.e. you will use an ODBC connection to the database. You may alternatively specify the Provider in the Open method Connection String argument.

Note about Providers

The default provider is MSDASQL:

'ado'Œwi'*xProvider'

MSDASQL

To know about the existing Providers, query the providers property:

'ado'Œwi'providers'

MSDASQL Microsoft ODBC Provider (the default Provider)

MSIDXS Microsoft OLE DB provider for Microsoft Indexing Service

ADSDSOObject Microsoft OLE DB provider for Microsoft Active Directory Service

Microsoft.Jet.OLEDB.4.0 Microsoft OLE DB provider for Microsoft Jet

SQLOLEDB Microsoft OLE DB provider for SQL Server

MSDAORA Microsoft OLE DB provider for Oracle

MSDAIPP.DSO Microsoft OLE DB provider for Internet Publishing

MSDataShape Microsoft Data Shaping Service for OLE DB (ADO Service Provider)

MSPersist Microsoft OLE DB Persistence Provider (ADO Service Provider)

MS Remote Microsoft OLE DB Remoting Provider (ADO Service Provider)

Then you may specify a provider with the provider property:

'ado'Œwi'provider' 'SQLOLEDB'

'ado'Œwi'provider'

SQLOLEDB

You can use the Open method in 2 ways:

l by specifying a Connection String argument

l by specifying a DSN (Data Source Name) as registered in the ODBC Data Source Administrator

Examples:

Connect using Connection Strings:

'ado'Œwi'Open' 'driver={SQL Server};server=Dell8300;database=SIMV1_DEV01'

'ado'Œwi'Open' 'Provider=sqloledb;Data Source=Dell8300;Initial Catalog=SIMV1_DEV01;Integrated Security=SSPI;'

Connect using a DSN

'ado'Œwi'Open' 'SIMV01_DEV01 SQL Server Database'

In that last case you should have defined SIMV01_DEV01 SQL Server Database as one of your entries in the System DSN tab of the ODBC Data Source Administrator panel.

TADO5_1.gif

The above Open method argument is a Connection String. Connection strings may take various forms and may include the Provider which indicates the type of Data Source you want to use. The default Provider if not specified is MSDASQL.

Since Connection Strings may be quite complex and vary depending on the Provider you are chosing, we have developed a method called ConnectionString which accept a connection mode as its 1st argument and a "database" as its 2nd second argument. ConnectionString returns the various possible templates you may use to build your own Connection String.

The various possible connection modes are: 'ODBC' 'OLEDB' 'DSN'

The various possible "database" names are: 'dBASE' 'Excel' 'Text' 'FoxPro' 'Access' 'SQL Server' 'Oracle' 'MySQL' 'AS400' 'Sybase' 'Sybase Sql Anywhere' 'Active Directory' 'DB2' 'Jet' 'Exchange Mail' 'OLAP' 'Index Server'

Example:

'ado'Œwi'ConnectionString' 'ODBC' 'SQL Server'

For Standard Security:

Driver={SQL Server};Server=ServerName;Trusted_Connection=no;Database=DatabaseName;Uid=UserName;Pwd=Password;

For Trusted Connection security (Microsoft Windows NT integrated security):

Driver={SQL Server};Server=ServerName;Database=DatabaseName;Uid=;Pwd=;

Driver={SQL Server};Server=ServerName;Database=DatabaseName;Trusted_Connection=yes;

If Sql Server is running on the same computer:

Driver={SQL Server};Server=(local);Database=DatabaseName;Uid=UserName;Pwd=Password;

If Sql Server is running on a Remote Computer:

Driver={SQL Server};Server=130.120.110.001;Address=130.120.110.001,1052;Network=dbmssocn;Database=DatabaseName;Uid=Username;Pwd=Password;

'ado'Œwi'ConnectionString' 'OLEDB' 'Access'

Connect using standard security:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\DatabasePath\\MmDatabase.mdb;User Id=admin;Password=;

Connect using a Workgroup (System database):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\DataBasePath\\mydb.mdb;Jet OLEDB:System Database=MySystem.mdw;User Id=myUserName;Password=myPassword;

Displaying the SQL Server Version

When using SQL Server you can get version information about the currently running instance of SLQ Server using the sqlserverversion read-only property

'ado'Œwi'sqlserverversion'

Microsoft SQL Server 2000 - 8.00.534 (Intel X86)

Nov 19 2001 13:23:50

Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)

½'ado'Œwi'sqlserverversion'

4 65

This property returns an empty matrix if you query it when the opened database is not an SQL Server database:

'ado'Œwi'Open' 'ISAPS Members Access Database'

'ado'Œwi'sqlserverversion'

½'ado'Œwi'sqlserverversion'

0 0

Getting Information About Tables

Let's connect to the Northwind Access database for which a System DSN has been defined:

'ado'Œwi'Open' 'Northwind Access Database'

You may query what tables this database contains, using the Tables method:

'ado'Œwi'Tables'

C:\APLWin\Objects\NWIND MSysACEs SYSTEM TABLE

C:\APLWin\Objects\NWIND MSysCmdbars SYSTEM TABLE

C:\APLWin\Objects\NWIND MSysIMEXColumns SYSTEM TABLE

C:\APLWin\Objects\NWIND MSysIMEXSpecs SYSTEM TABLE

C:\APLWin\Objects\NWIND MSysModules SYSTEM TABLE

C:\APLWin\Objects\NWIND MSysModules2 SYSTEM TABLE

C:\APLWin\Objects\NWIND MSysObjects SYSTEM TABLE

C:\APLWin\Objects\NWIND MSysQueries SYSTEM TABLE

C:\APLWin\Objects\NWIND MSysRelationships SYSTEM TABLE

C:\APLWin\Objects\NWIND Categories TABLE

C:\APLWin\Objects\NWIND Customers TABLE

C:\APLWin\Objects\NWIND Employees TABLE

C:\APLWin\Objects\NWIND Order Details TABLE

C:\APLWin\Objects\NWIND Orders TABLE

C:\APLWin\Objects\NWIND Products TABLE

C:\APLWin\Objects\NWIND Shippers TABLE

C:\APLWin\Objects\NWIND Suppliers TABLE

C:\APLWin\Objects\NWIND Category Sales for 1995 VIEW

C:\APLWin\Objects\NWIND Current Product List VIEW

C:\APLWin\Objects\NWIND Customers and Suppliers by City VIEW

C:\APLWin\Objects\NWIND Employee Sales by Country VIEW

C:\APLWin\Objects\NWIND Invoices VIEW

C:\APLWin\Objects\NWIND Invoices Filter VIEW

C:\APLWin\Objects\NWIND Order Details Extended VIEW

C:\APLWin\Objects\NWIND Order Subtotals VIEW

C:\APLWin\Objects\NWIND Product Sales for 1995 VIEW

C:\APLWin\Objects\NWIND Products Above Average Price VIEW

C:\APLWin\Objects\NWIND Quarterly Orders VIEW

C:\APLWin\Objects\NWIND Quarterly Orders by Product VIEW

C:\APLWin\Objects\NWIND Sales by Category VIEW

C:\APLWin\Objects\NWIND Sales by Year VIEW

C:\APLWin\Objects\NWIND Ten Most Expensive Products VIEW

The Tables method returtns information about the database internal System Tables (which names start with MSys) as well as about the user data tables.

You may restrict the output to certain type of tables by passing either 'TABLE' 'SYSTEM TABLE' or 'VIEW' as an argument:

'ado'Œwi'Tables' 'TABLE'

C:\APLWin\Objects\NWIND Categories TABLE

C:\APLWin\Objects\NWIND Customers TABLE

C:\APLWin\Objects\NWIND Employees TABLE

C:\APLWin\Objects\NWIND Order Details TABLE

C:\APLWin\Objects\NWIND Orders TABLE

C:\APLWin\Objects\NWIND Products TABLE

C:\APLWin\Objects\NWIND Shippers TABLE

C:\APLWin\Objects\NWIND Suppliers TABLE

Or you may restrict the output to tables which contain a given character string in their names:

'ado'Œwi'Tables' 'order'

C:\APLWin\Objects\NWIND Order Details TABLE

C:\APLWin\Objects\NWIND Orders TABLE

C:\APLWin\Objects\NWIND Order Details Extended VIEW

C:\APLWin\Objects\NWIND Order Subtotals VIEW

C:\APLWin\Objects\NWIND Quarterly Orders VIEW

C:\APLWin\Objects\NWIND Quarterly Orders by Product VIEW

Or you can combine both of the last 2 types of queries by specifying the type of table first and a character string to be contained in their names, as in:

'ado'Œwi'Tables' 'table' 'order'

C:\APLWin\Objects\NWIND Order Details TABLE

C:\APLWin\Objects\NWIND Orders TABLE

Getting Information About Columns

You may use the Columns method to retrieve information about all the columns contained in the database or about columns of a specific table.

'ado'Œwi'Columns'

TableName TableType Name Type Attributes DefinedSize NumericScale Precision PrimaryKey Unique IndexNulls Clustered

Categories TABLE CategoryID 3 1 0 10

Categories TABLE CategoryName 202 2 15 0 1 1 17740848 1

Categories TABLE Description 201 2 2147483647 0

Categories TABLE Picture 205 2 1073741823 0

Customers TABLE CustomerID 202 2 5 0

Customers TABLE CompanyName 202 2 40 0 1 0 17740848 1

Customers TABLE ContactName 202 2 30 0

Customers TABLE ContactTitle 202 2 30 0

Customers TABLE Address 202 2 60 0

Customers TABLE City 202 2 15 0 1 0 17740848 1

Customers TABLE Region 202 2 15 0 1 0 17740848 1

Customers TABLE PostalCode 202 2 10 0 1 0 17740848 1

...

You may also specify a given table:

'ado'Œwi'Columns' 'Categories'

TableName TableType Name Type Attributes DefinedSize NumericScale Precision PrimaryKey Unique IndexNulls Clustered

Categories TABLE CategoryID 3 1 0 10

Categories TABLE CategoryName 202 2 15 0 1 1 17740848 1

Categories TABLE Description 201 2 2147483647 0

Categories TABLE Picture 205 2 1073741823 0

or part of a column name:

'ado'Œwi'Columns' 'id'

TableName TableType Name Type Attributes DefinedSize NumericScale Precision PrimaryKey Unique IndexNulls Clustered

Categories TABLE CategoryID 3 1 0 10

Customers TABLE CustomerID 202 2 5 0

Employees TABLE EmployeeID 3 1 0 10

Order Details TABLE OrderID 3 3 0 10 1 0 17740848 1

Order Details TABLE ProductID 3 3 0 10 1 0 17740848 1

Orders TABLE OrderID 3 1 0 10

Orders TABLE CustomerID 202 2 5 0 1 0 17740848 1

Orders TABLE EmployeeID 3 3 0 10 1 0 17740848 1

Products TABLE ProductID 3 1 0 10

Products TABLE SupplierID 3 3 0 10 1 0 17740848 1

Products TABLE CategoryID 3 3 0 10 1 0 17740848 1

Shippers TABLE ShipperID 3 1 0 10

Suppliers TABLE SupplierID 3 1 0 10

Running Any SQL Command

Once you know about your database table and column names, you can run SQL commands, using the Exec method.

The Exec method accepts one argument which is the database command you want to run.

Examples:

0 0½'ado'Œwi'*Create' 'TADO5'

'ado'Œwi'Open' 'Northwind Access Database'

'ado'Œwi'Exec' 'select * from shippers'

1 Speedy Express (503) 555-9831

2 United Package (503) 555-3199

3 Federal Shipping (503) 555-9931

'ado'Œwi'Exec' 'insert into shippers(ShipperID,CompanyName,Phone) values(99,''UPS'',''(503) 555-5555'')'

'ado'Œwi'Exec' 'select * from shippers'

1 Speedy Express (503) 555-9831

2 United Package (503) 555-3199

3 Federal Shipping (503) 555-9931

99 UPS (503) 555-5555

'ado'Œwi'Exec' 'update shippers set CompanyName=''United Parcels Service'' where ShipperID = 99'

'ado'Œwi'Exec' 'select * from shippers'

1 Speedy Express (503) 555-9831

2 United Package (503) 555-3199

3 Federal Shipping (503) 555-9931

99 United Parcels Service (503) 555-5555

'ado'Œwi'Exec' 'delete from shippers where ShipperID = 99'

'ado'Œwi'Exec' 'select * from shippers'

1 Speedy Express (503) 555-9831

2 United Package (503) 555-3199

3 Federal Shipping (503) 555-9931

'ado'Œwi'Close'

Running Stored Procedures

Stored Procedures have currently been tested with TADO5 only on Microsoft SQL Server.

First let's connect to an SQL Server Database:

'ado'Œwi'*Create' 'TADO5'

ado

'ado'Œwi'Open' 'SIMV1_DEV01 SQL Server Database'

or:

'ado'Œwi'Open' 'driver={SQL Server};server=Dell8300;database=SIMV1_DEV01'

or:

'ado'Œwi'Open' 'Provider=sqloledb;Data Source=Dell8300;Initial Catalog=SIMV1_DEV01;Integrated Security=SSPI;'

Querying Existing Stored Procedures

Use the StProcs method to know about all available Stored Procedures in the database.

'ado'Œwi'StProcs'

sp_cci_d_05

sp_cci_d_06

sp_pds_s_03

sp_cci_d_07

sp_cci_i_01

sp_cci_s_01

sp_sim_s_02

sp_typ_s_01

sp_usr_s_01

...

You may also return only those Stored Procedures which name contain a given character string:

'ado'Œwi'StProcs' 'i_01'

sp_apc_i_01

sp_cci_i_01

sp_con_i_01

sp_dcc_i_01

sp_dgu_i_01

sp_dmj_i_01

sp_dsh_i_01

sp_dsi_i_01

sp_fcd_i_01

sp_for_i_01

sp_nan_i_01

sp_pdh_i_01

sp_pds_i_01

sp_per_i_01

sp_pri_i_01

sp_sih_i_01

sp_sim_i_01

sp_sis_i_01

sp_sta_i_01

sp_sui_i_01

sp_tdc_i_01

sp_tes_i_01

sp_typ_i_01

sp_usr_i_01

sp_ver_i_01

Displaying a Summary of All Existing Stored Procedures

The StoredProcs method, when used with no arguments, retrieves a 2-column nested array containing:

l the Stored Procedures names in column 1

l the Stored Procedures code in column 2

for all Stored Procedures saved in the database.

If the StoredProcs argument is a Stored Procedure name, the result is a 1 row by 2 columns nested array containing the name and the code of the specified Stored Procedure.

Example:

'ado'Œwi'StoredProcs' 'sp_usr_s_02'

sp_usr_s_02 /* Create Procedure */ CREATE PROCEDURE sp_usr_s_02 -- ( -- @USR_ID VarChar(8) = NULL /* User Identifier */ -- ) -- AS -- /* Procedure Body */ SELECT USR

_ID, -- DGU_ID, -- USR_ADRMAIL, -- USR_PCS_ID -- FROM USR_UTILISATEUR -- WHERE USR_ID = @USR_ID -- AND USR_TOPSUPLOG = 0 -- /* Procedure End */

NOTE

Note that unlike the StProc method (described hereafter), StoredProcs retrieve the whole Stored Procedure code including its comments, -- markers,etc.

Displaying a Stored Procedure Code

Given a Stored Procedure name, you can display the SQL code of this Stored Procedure using the StProc method.

'ado'Œwi'StProc' 'sp_typ_s_01'

CREATE PROCEDURE sp_typ_s_01

(

@TYP_ID VarChar(7) = NULL

)

SELECT TYP_LIBCOURT,

TYP_LIBLONG

FROM TYP_TYPESIMULATION

WHERE TYP_ID = @TYP_ID

AND TYP_TOPSUPLOG = 0

NOTE

The StProc extracts only the "useful" part of the Stored Procedure code: it starts with the words CREATE PROCEDURE and all comments are removed. Additionnally, for the StProc method to work ok you must end each line of useful code in your Stored Procedure with --.

For example the source code for the sp_typ_s_01 Stored Procedure is:

Procedure Name: sp_typ_s_01

Description: Select a Simulation Type

Author: T.TREPIED

Created: 15apr2002

/* Remove Stored Procedure if already exists */

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_typ_s_01' AND type = 'P') --

DROP PROCEDURE sp_typ_s_01 --

/* Create Stored Procedure */

CREATE PROCEDURE sp_typ_s_01 --

( --

@TYP_ID VarChar(7) = NULL /* Simulation Type ID */ --

) --

AS --

/* Stored Procedure Body */

SELECT TYP_LIBCOURT, --

TYP_LIBLONG --

FROM TYP_TYPESIMULATION --

WHERE TYP_ID = @TYP_ID --

AND TYP_TOPSUPLOG = 0 --

/* End of Stored Procedure */

Sample Call:

EXECUTE sp_typ_s_01

@TYP_ID = ''

In the above code, all sections starting with /* and ending */ are comments. Otherwise the only lines which are shown in the StProc output are those ending with --.

Displaying the Stored Procedure Argument String

When calling a Stored Procedure, you may pass arguments to it as a nested vector, each element of this nested vector being one argument, or as a character string just following the Stored Procedure name.

For example, the sp_cli_s_03 Stored Procedure accepts 2 arguments: a client code (numeric) and a client type (character). Here is the sp_cli_s_03 procedure code:

'ado'Œwi'StProc' 'sp_cli_s_03'

CREATE PROCEDURE sp_cli_s_03 ( @CLI_ID Numeric(5) = NULL,

@TDC_ID VarChar(4) = NULL

) AS SELECT CLI_ID, TDC_ID, CLI_NOM FROM CLI_CLIENT

WHERE CLI_ID = @CLI_ID AND (@TDC_ID IS NULL OR TDC_ID = @TDC_ID)

Assume we have a cli_id and a tdc_id APL variables containing values to pass to the Stored Procedure, as follows:

cli_id„20112

tdc_id„'AGAI'

then, there are 2 ways we can run the Stored Procedure with these arguments:

l    first by passing them as a nested vector of arguments to the Stored Procedure:

      'ado'Œwi'StoredProc' 'sp_cli_s_03
CLI_ID TDC_ID CLI_NOM
  20112 AGAI   DANONE

l    second, by passing them immediately following the Stored Procedure name in one single string: the StProcArg helps us build the right string to catenate to the Stored Procedure name:

      'ado'Œwi'StProcArg' 'cli_id' 'tdc_id'
@cli_id=20112,@tdc_id="AGAI"

      'ado'Œwi'StoredProc' 'sp_cli_s_03 @cli_id=20112,@tdc_id="AGAI"'
CLI_ID TDC_ID CLI_NOM
  20112 AGAI   DANONE

or:

      'ado'Œwi'StoredProc'('sp_cli_s_03 ','ado'Œwi'StProcArg' 'cli_id' 'tdc_id')
CLI_ID TDC_ID CLI_NOM
  20112 AGAI   DANONE

Note: if the specified Stored Procedure does not exist, the StProc method returns an empty character matrix:

½'ado'Œwi'StProc' 'sp_usr_i_02'

0 0

Executing a Stored Procedure

You may execute any given Stored Procedure by using the StoredProc method followed by the Stored Procedure name and its arguments:

Example:

'ado'Œwi'StoredProc' 'sp_cli_s_03' 20112 'AGAI'

CLI_ID TDC_ID CLI_NOM

20112 AGAI DANONE

Or you may, as described in the previous paragraph, pass only a single character string argument to the StoredProc method, including both the Stored Procedure name and its arguments with the following syntax:

'ado'Œwi'StoredProc' 'sp_cli_s_03 @cli_id=20112,@tdc_id="AGAI"'
CLI_ID TDC_ID CLI_NOM
20112 AGAI DANONE

Note that the StoredProc method always returns a nested matrix and that the first column of this matrix contains the column names

For example, the sp_pds_s_03 Stored Procedure retrieves a record from the PDS_PROPOSITIONSDESIMULATIONS table, given the record number. Here is the procedure:

'ado'Œwi'StProc' 'sp_pds_s_03'

CREATE PROCEDURE sp_pds_s_03

(

@PDS_ID Numeric = NULL

)

SELECT PDS.PDS_ID,

PDS.TDC_ID,

PDS.CLI_ID,

PDS.NAN_ID,

PDS.PDS_TOPAUTSIM,

PDS.PDS_LIB,

PDS.USR_ID_AUTEUR,

PDS.USR_ID_MODIF,

PDS.PDS_DTCREA,

PDS_DTMODIF

FROM PDS_PROPOSITIONDESIMULATIONS PDS

WHERE PDS.PDS_ID = @PDS_ID

AND PDS.PDS_TOPSUPLOG = 0

Let's call it:

'ado'Œwi'StoredProc' 'sp_pds_s_03'339

PDS_ID TDC_ID CLI_ID NAN_ID PDS_TOPAUTSIM PDS_LIB USR_ID_AUTEUR USR_ID_MODIF PDS_DTCREA PDS_DTMODIF

339 GAI 9283 0 Proposition CGV créée le 15/09/02 - Test ELESCASS ELESCASS 37514.646 37514.646

Now let's call it with a non existing record number:

'ado'Œwi'StoredProc' 'sp_pds_s_03'99999

PDS_ID TDC_ID CLI_ID NAN_ID PDS_TOPAUTSIM PDS_LIB USR_ID_AUTEUR USR_ID_MODIF PDS_DTCREA PDS_DTMODIF

½'ado'Œwi'StoredProc' 'sp_pds_s_03'99999

1 10

Here is another Stored Procedure:

'ado'Œwi'StProc' 'sp_pds_i_01'

CREATE PROCEDURE sp_pds_i_01

(

@TDC_ID VarChar(4) = NULL,

@CLI_ID Numeric = NULL,

@NAN_ID Numeric = NULL,

@USR_ID VarChar(8) = NULL,

@PDS_TOPAUTSIM TinyInt = NULL,

@PDS_LIB VarChar(80) = NULL

)

DECLARE @PDS_ID Numeric

DECLARE @NOW DateTime

SELECT @NOW = GetDate()

INSERT INTO PDS_PROPOSITIONDESIMULATIONS

(TDC_ID,

CLI_ID,

NAN_ID,

USR_ID_AUTEUR,

USR_ID_MODIF,

PDS_TOPAUTSIM,

PDS_DTCREA,

PDS_DTMODIF,

PDS_TOPSUPLOG,

PDS_LIB

)

VALUES (@TDC_ID,

@CLI_ID,

@NAN_ID,

@USR_ID,

@PDS_TOPAUTSIM,

@NOW,

@PDS_LIB

)

SELECT @PDS_ID = @@IDENTITY

EXECUTE SP_PDH_I_01 @PDS_ID = @PDS_ID

SELECT @PDS_ID AS 'PDS_ID'

This Stored Procedure adds a new record into the PDS_PROPOSITIONDESIMULATIONS table and return the record ID (PDS_ID) of the inserted record. Let's call this Stored Procedure:

'ado'Œwi'StoredProc' 'sp_pds_i_01' 'AGAI' 20112 'NULL' 'ELESCASS' 1 ''

PDS_ID

340

Note About NULL Arguments

When you need to pass an argument for which you do not know the value:

l pass it as 'NULL' if the argument is a numeric argument (see in the above example where we passed 'NULL' for the NAN_ID argument)

l pass it as '' if the argument is a character argument (see in the above example where we passed '' for the PDS_LIB argument)

Let's check the record we added a while before in the PDS_PROPOSITIONDESIMULATIONS table, using the sp_pds_s_03 procedure:

'ado'Œwi'StoredProc' 'sp_pds_s_03'340

PDS_ID TDC_ID CLI_ID NAN_ID PDS_TOPAUTSIM PDS_LIB USR_ID_AUTEUR USR_ID_MODIF PDS_DTCREA PDS_DTMODIF

340 AGAI 20112 1 NULL ELESCASS ELESCASS 37765.765 37765.765

Note that the PDS_LIB which was passed as '' is returned as a NULL value.

Handling Errors

It may happen that the Stored Procedure fails to execute for some reason: in that case, a specific and detailed error message is displayed in a Message Box and the Stored Procedure returns ¯1.

Example:

'ado'Œwi'StoredProc' 'sp_pds_i_01' '' 20112 'NULL' 'ELESCASS' 1 ''

¯1

TADO5_2.gif

This call failed because we did not specify a valid value for the first argument (TDC_ID) which is a Foreign Key and should be an exsiting TDC_ID value in the TDC_TYPEDECLIENT table.

Whenever an error has occured you may also use the Error method to return more detail about the last errors.

Example:

'ado'Œwi'Error'

ADO Erreur ¯2147217900:

Description: [Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement conflicted

with TABLE FOREIGN KEY constraint 'FK_PDS_PROP_CLI_PDS_Q_CLI_CLIE'.

The conflict occurred in database 'SIMV1_DEV01', table 'CLI_CLIENT'.

Source: Microsoft OLE DB Provider for ODBC Drivers

SQL State: 23000

Native Error: 547

No Help File available!

ADO Erreur ¯2147217900:

Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

Source: Microsoft OLE DB Provider for ODBC Drivers

SQL State: 01000

Native Error: 3621

No Help File available!

Closing the Connection to the Database

In order to spare resources on your machine, you should always close the TADO5 object whenever you are done using it, with the Close method.

'ado'Œwi'Close'