You are currently offline, waiting for your internet to reconnect

How To Determine Recordset Field Properties Using ADO

This article was previously published under Q193947
In an application, sometimes it is useful to determine the properties offields in a remote datasource. You can determine information such as thefield names, data types and field lengths by examining the Fieldscollection of the ActiveX Data Objects (ADO) Recordset object.
The following program creates a Recordset from the Authors table of the SQLServer sample Pubs database. It displays the field name, data type, anddefined length of each field in the Authors table.

The data type returns as an integer value. The #DEFINE statements at thetop of the program are included so that this value can be cross- referencedto a character description of the data type.

In order to use this example, you must have the Microsoft Data AccessComponents (MDAC) version 2.x or later installed, which is included in thedata components of Visual Studio 6.0 or can be downloaded from thefollowing Web address:NOTE: Substitute values for the SERVER, user id (UID) and password (PWD)values appropriate to your SQL Server installation in the oRecordset.OPENstatement.

Sample Code

   * DEFINEs for field types - provided for reference only.   #DEFINE ADEMPTY               0   #DEFINE ADTINYINT            16   #DEFINE ADSMALLINT            2   #DEFINE ADINTEGER            3   #DEFINE ADBIGINT            20   #DEFINE ADUNSIGNEDTINYINT      17   #DEFINE ADUNSIGNEDSMALLINT      18   #DEFINE ADUNSIGNEDINT         19   #DEFINE ADUNSIGNEDBIGINT      21   #DEFINE ADSINGLE            4   #DEFINE ADDOUBLE            5   #DEFINE ADCURRENCY            6   #DEFINE ADDECIMAL            14   #DEFINE ADNUMERIC            131   #DEFINE ADBOOLEAN            11   #DEFINE ADERROR               10   #DEFINE ADUSERDEFINED         132   #DEFINE ADVARIANT            12   #DEFINE ADIDISPATCH            9   #DEFINE ADIUNKNOWN            13   #DEFINE ADGUID               72   #DEFINE ADDATE               7   #DEFINE ADDBDATE            133   #DEFINE ADDBTIME            134   #DEFINE ADDBTIMESTAMP         135   #DEFINE ADBSTR               8   #DEFINE ADCHAR               129   #DEFINE ADVARCHAR            200   #DEFINE ADLONGVARCHAR         201   #DEFINE ADWCHAR               130   #DEFINE ADVARWCHAR            202   #DEFINE ADLONGVARWCHAR         203   #DEFINE ADBINARY            128   #DEFINE ADVARBINARY            204   #DEFINE ADLONGVARBINARY         205   #DEFINE ADCHAPTER            136   oRecordSet = CREATEOBJECT("ADODB.Recordset")   lcSQL = "select * from authors"   oRecordSet.OPEN(lcSQL, ;      "DRIVER={SQL Server};" + ;      "SERVER=YourServerName;" + ;      "DATABASE=pubs;" + ;      "UID=YourUserID;" + ;      "PWD=YourPassword")   lcFieldInfo = ""   FOR EACH FIELD IN oRecordSet.FIELDS      ? "Field name: ", FIELD.NAME, ;         " Type: ", LTRIM(STR(FIELD.TYPE)), ;         " Defined size: ", LTRIM(STR(FIELD.DEFINEDSIZE)), ;         " Actual size: ", LTRIM(STR(FIELD.ACTUALSIZE))      ?   NEXT				
kbVFp600 kbActiveX kbSQL kbADO kbCtrl kbMDAC definedsize actualsize ADO

Article ID: 193947 - Last Review: 07/01/2004 16:56:40 - Revision: 4.3

  • Microsoft Visual FoxPro 6.0 Professional Edition
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • kbdatabase kbhowto KB193947