Maximum Fields in SELECT statement

  • Thread starter Thread starter Anatoly
  • Start date Start date
A

Anatoly

What is the maximum number of individual fields I can request in SELECT
statement.

I.E.
 
Hi Anatoly,

For what kind of database? May I ask why you are wanting to know? Are you
experiencing problems? Please reply to this post.

Thanks
 
I'm working on a project that was originally designed in Access.

I'm building a .NET program to read some of the data in Access Database.

The original programmer of the database was a starter, so he used reserved
keywords such as "Authorization".

When I tried to SELECT * (all fields using the Designer) I got errors and
the SELECT statement was not generated. So, I realized there are a few
Columns use reserved keynames. I tried enclosing those columns in '[ ]'
brackets but designer wouldn't take it when I click on 'Next' in the wizard.
So, I tried hand-picking and selecting individual columns that don't use
reserved keywords.

That's when I ran into another errors which I troublshot to be the limit of
fields SELECT can handle.

Anatoly
 
Anatoly,

Thanks for the info. In Access the max number of fields that you can have
in a recordset is 255...and the maximum length of characters that you can
have in a Sql Statement is 64000. If you have a lot of fields in the Select
list you are probably hitting the 64k limit before you hit the 255 limit.

I hope this helps.
----------------------------
 
Thanks for info.

Would you happen to know why I can't enclose certain column names that use
reserved keywords in "[ ]" in the designer?

Anatoly
 
Hi Anatoly,

I am glad that worked. As for selecting reserved keyword you should be able
to do something like (using the reserved word SELECT as an example)

SELECT MyTable.[SELECT] FROM MyTable

If you are using the designer to choose the column names you shouldn't have
to enclose the reserved words in []. It will do that for you (look at the
Sql View once you have your query complete).

If this does not help and you are having a specific problem can you post
more details about what you are trying to do? For instance the table
structure (partial - the part that is giving you problems) and the query that
is giving you problems.

I have posted below a couple of links from the MSDN listing of the reserved
words for different versions of Access.

I hope this helps.
--------------------------
Access 2000
http://support.microsoft.com/kb/209187/EN-US/

Access 2002 and 2003
http://support.microsoft.com/?id=286335
 
This is the Select Statement in Query Builder when I'm using the Designer
Wizard:

SELECT [Assessed Value], [Billing Cycle], CallClient, Comments,
[Contract?], [ContractSigned?], [Corner Stake Details], [Date Received],
[Date Sent],
[DepositPaid?], [Disputes or Encroachments Flag],
Estimate, FieldCrewInstructions, [GaveROSRequirements?], [Geo?], [Go Date],
[Hourly Billing?],
[Hourly Deposit], [Includes Record of Survey], [Info
Notes], [Initial Contact Date], [Initial Contact Name], [Job Description],
[Job Notes], [Job Notes Flag],
[JobSetup?], Jurisdiction, LastBillingDate, [Legal
Description], [Legal Received?], [Line Stake Details], [Lot Size], [Lot Size
Type], NextBillingDate,
[Not to Exceed], [Office Due Date], [Paid Barry],
[Party ChiefID], [Payment Notes Flag], [Payment Plan],
[PaymentArrangements?], [Percent Complete],
[Percent Complete2], [PickupContract?],
[PickupDeposit?], PickupLegal, [PO#], Price, ProjectID, [Project Name],
[Business Name], contact1,
[Current Status], [# of Line Stakes], [# of Stake
Corners], [1], [2], [3], Priority, [Project ManagerID], [Project Referance],
[Projected Field Manhours],
[Projected Office Manhours], [Reason for Project
Flag], [Reason for Survey], [Received Back], [Required Due Date], [ROS
Paid], ROSCost, ROSStatus,
ROSStatusNotes, ROSType, [Scheduled Date], [Schedule
Office?], ScheduleNotes, [Site 1st 1/4], [Site 1st 1/4_2], [Site 2nd 1/4],
[Site 2nd 1/4_2],
[Site Access?], [Site Address], [Site Area], [Site
Block #], [Site Brush?], [Site City], [Site County], [Site Cross Reference],
[Site Disputes/Encroachments?],
[Site Grid], [Site Grid_2], [Site Improvements?],
[Site Legal Description], [Site Lot #], [Site Page], [Site Plat Name], [Site
Range], [Site Range_2],
[Site Section], [Site Section_2], [Site Slope?], [Site
Special Conditions], [Site State], [Site Tax #], [Site Thomas Guide], [Site
Township],
[Site Township_2], [Site Trees?], [Site Volume], [Site
Wetlands?], [Site Zip], SpecificJobInstructions, [Status Notes], [Survey
Type], Title,
[Total Office Manhours Billed], [Total Field Crew
Hours Billed]
FROM tblProjects

I can't add more fields. I get an error saying "There were errors
configuring the data adapter."
As soon as I delete the added field I get no more errors. I know the fields
I'm trying to add are not using reserved keywords and there are nothing
wrong with them.

Thanks,
Anatoly

Brian Brown said:
Hi Anatoly,

I am glad that worked. As for selecting reserved keyword you should be
able
to do something like (using the reserved word SELECT as an example)

SELECT MyTable.[SELECT] FROM MyTable

If you are using the designer to choose the column names you shouldn't
have
to enclose the reserved words in []. It will do that for you (look at the
Sql View once you have your query complete).

If this does not help and you are having a specific problem can you post
more details about what you are trying to do? For instance the table
structure (partial - the part that is giving you problems) and the query
that
is giving you problems.

I have posted below a couple of links from the MSDN listing of the
reserved
words for different versions of Access.

I hope this helps.
--------------------------
Access 2000
http://support.microsoft.com/kb/209187/EN-US/

Access 2002 and 2003
http://support.microsoft.com/?id=286335
 
Hi Anatoly,

Sorry for the late reply but I decided to test this myself. I created a
table with all of the fields that you have below. Then I created a test
Windows Application and used the wizards to configure an oleDbConnection and
an oleDbDataAdapter. When I first pulled the table over to my form I
received the same error that you are getting. Then I expanded the
SelectCommand node and invoked the Query Designer on the CommandText
property. I unchecked the * and went down the list and checked every column.
Then I went in to the form_load and assigned hooked up the datagrid on the
form to the dataset filled by the dataadapter. It ran fine with no problems.
At this point I went back to the database and added 4 more fields to it.
Then went back to the app and redid everything I have just written (being
sure to delete the old dataadapter and placing a new one on the form) and ran
it again with no problems.

Can you create a test form as I have descibed above and test it in the same
manner? If you still get an error please reply to this post describing
exactly what the error is and the exact steps that you have taken, so I can
repeat it.

I hope this helps.
 
Hi Brian,
Here is what I did.
This time to configure DataAdapter instead of right-clicking on DataAdapter
and selecting "Configure Data Adapter", I clicked on the 'SELECT' statement
in the 'Properties' panel
and modified SELECT statement from there.

It seems to work fine if I do that.
Now I've added more fields than I could originally and the app works.

Brian, thank you for all of your help.

Anatoly
 
Ok, I hope you guys can help me too. I seem to be having a similar problem, using an access DB and VB.net when I try to create the OleDBDataAdaptor using the wizard I get the following error on trying to generate the select statement.

'Generated Select Statement There were errors configuring the data adaptor'

It has only occurred since the customer has requested more fields (Columns), I have now got 100 fields and this is the point the error occurrs. Is this error caused due to hitting a limit of max fields (column). Seems to be strange if I am hitting the 64k limit character limit.

What would be the best way around this as it would be a huge amount of work to split the table, just to add 10 more fields or so or would I have to generate the statements manually as already mentioned

Thanks

David

From http://developmentnow.com/g/7_2005_2_0_0_49615/Maximum-Fields-in-SELECT-statement.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
 
Ok, I hope you guys can help me too. I seem to be having a similar problem, using an access DB and VB.net when I try to create the OleDBDataAdaptor using the wizard I get the following error on trying to generate the select statement.

'Generated Select Statement There were errors configuring the data adaptor'

It has only occurred since the customer has requested more fields (Columns), I have now got 100 fields and this is the point the error occurrs. Is this error caused due to hitting a limit of max fields (column). Seems to be strange if I am hitting the 64k limit character limit.

What would be the best way around this as it would be a huge amount of work to split the table, just to add 10 more fields or so or would I have to generate the statements manually as already mentioned

Thanks

David

From http://developmentnow.com/g/7_2005_2_0_0_49615/Maximum-Fields-in-SELECT-statement.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
 
Ok, I hope you guys can help me too. I seem to be having a similar problem, using an access DB and VB.net when I try to create the OleDBDataAdaptor using the wizard I get the following error on trying to generate the select statement.

'Generated Select Statement There were errors configuring the data adaptor'

It has only occurred since the customer has requested more fields (Columns), I have now got 100 fields and this is the point the error occurrs. Is this error caused due to hitting a limit of max fields (column). Seems to be strange if I am hitting the 64k limit character limit.

What would be the best way around this as it would be a huge amount of work to split the table, just to add 10 more fields or so or would I have to generate the statements manually as already mentioned

Thanks

David

From http://developmentnow.com/g/7_2005_2_0_0_49615/Maximum-Fields-in-SELECT-statement.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
 
First, it is better to query in the body, not in the subject (keep subject
short, ask question in body).

Lots of questions:
1. Server type and version
2. OS type and version
3. Processor (esp. whether 32 or 64 bit)

The number of fields is not really the issue, at least not directly, but the
max size of a single row in a result set. The bigger question is not just the
number, but type(s) of fields you are reporting on.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Back
Top