Microsoft KB Archive/300184

= PRB: Error in ADO Recordset with 255 Fields from Visual FoxPro Table =

Article ID: 300184

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1
 * Microsoft Data Access Components 2.6
 * Microsoft Visual FoxPro 6.0 Professional Edition

-



This article was previously published under Q300184



SYMPTOMS
If you try to access a field of an ADO Recordset whose source is a SQL SELECT statement that retrieves 255 columns from a Visual FoxPro table, you receive the following error message:

Run-time error '-2147467259(80004005)': [Microsoft][ODBC Visual FoxPro Driver] Invalid column number



CAUSE
This is a known limitation of the ODBC Driver for Visual FoxPro. You can only use the ODBC Driver for Visual FoxPro to successfully open and manipulate ADO Recordsets that contain up to 254 fields.



RESOLUTION
To fix this problem, upgrade to the version of OLE DB Provider for Visual FoxPro that ships with Visual FoxPro version 7.0.

To work around this problem, when you use the ODBC Driver for Visual FoxPro, you must to restrict your query to retrieve only the columns that need to be manipulated in your ActiveX Data Objects (ADO) code instead of running a &quot;SELECT * from &quot; query against a Visual FoxPro table with 255 columns. If your code needs to access all of the 255 columns in a Visual FoxPro table, consider opening and manipulating two distinct ADO Recordsets, each of which contains a subset of the columns in the base table.



STATUS
Microsoft has confirmed that this is a problem in the ODBC Driver for Visual FoxPro that ships with the Microsoft products that are listed at the beginning of this article.

This bug was corrected in the OLE DB Provider for Visual FoxPro that ships only with Visual FoxPro version 7.0.



MORE INFORMATION
Theoretically, a Visual FoxPro table can have a maximum of 255 columns when all of the 255 columns have a NOT NULL constraint enforced on them and cannot be assigned a NULL value. The number of permitted columns in a Visual FoxPro table is reduced by one (to 254 columns) even if one or more of the columns permit NULL values.

Steps to Reproduce Behavior
 Create a new folder on your hard disk named VFPTest (for example, C:\VFPTest). Create a Free table directory Visual FoxPro ODBC system data source name (DSN) named FOXTEST by specifying the path to the C:\VFPTest folder. Open a new Standard EXE project in Visual Basic. Form1 is created by default. Set a project reference to the Microsoft ActiveX Data Objects 2.x Library. Place a Command button on Form1.</li>  Copy and paste the following code in the Click event procedure of the command button: Dim cn As New ADODB.Connection cn.Open &quot;FOXTEST&quot;

Dim sqlstr As String Dim fldcount As Integer

'Code to create a Visual FoxPro table with 255 columns

sqlstr = &quot;Create Table c:\vfptest\Repro255 (&quot; For fldcount = 1 To 255 sqlstr = sqlstr & &quot;Field&quot; & fldcount & &quot; int NOT NULL,&quot; Next

sqlstr = Mid(sqlstr, 1, Len(sqlstr) - 1) sqlstr = sqlstr & &quot;)&quot;

cn.Execute sqlstr

'Code to insert a dummy record into the sample table

sqlstr = &quot;Insert into c:\vfptest\Repro255 values(&quot; For fldcount = 1 To 255 sqlstr = sqlstr & fldcount & &quot;,&quot; Next sqlstr = Mid(sqlstr, 1, Len(sqlstr) - 1) sqlstr = sqlstr & &quot;)&quot;

cn.Execute sqlstr

'Code to run a SELECT statement against the dummy table

Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open &quot;Select * from c:\vfptest\repro255.dbf&quot;, cn, adOpenKeyset, adLockOptimistic

'Code to construct a SELECT query that retrieves the first 254 columns from the sample table

'sqlstr = &quot;Select &quot; 'For fldcount = 1 To 254 ' sqlstr = sqlstr & &quot;field&quot; & fldcount & &quot;,&quot; 'Next 'sqlstr = Mid(sqlstr, 1, Len(sqlstr) - 1) 'sqlstr = sqlstr & &quot; from c:\vfptest\repro255.dbf&quot; 'rs.Open sqlstr, cn, adOpenKeyset, adLockOptimistic

'Display value of the first field in the recordset Debug.Print rs.Fields(0)

rs.Close cn.Close </li> The preceding code generates and runs dynamic SQL statements to create a Visual FoxPro table named Repro255 with 255 NOT NULL columns and inserts a dummy record into it. Then, an ADO Recordset is opened by specifying the query Select * from Repro255 as the source SQL. The Debug.Print statement is included to write out the value of the first field in the first record (the dummy record that is inserted by running the Insert statement) of the recordset to the Visual Basic Immediate window.</li> Save and run the project. Click the command button on the form to run the preceding Visual Basic code. The above-mentioned error occurs as the Debug.Print statement attempts to access and write out the value of the first field in the first record of the ADO Recordset to the Visual Basic Immediate window.</li> Stop running the project.</li> Comment out the currently active rs.Open statement, and uncomment the segment of code that constructs a dynamic SELECT statement to retrieve the first 254 columns from the Repro255 table.</li> Save and run the project. Click the command button when the form is displayed to run the modified Visual Basic code. Notice that the value of the first field is written out to the Visual Basic Immediate window as expected.</li></ol>

Keywords: kbprb KB300184

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.