Delphi Clinic C++Builder Gate Training & Consultancy Delphi Notes Weblog Dr.Bob's Webshop
Bob Swart (aka Drs.Bob) Dr.Bob's Delphi Clinics Dr.Bob's Delphi Courseware Manuals
View Bob Swart's profile on LinkedIn Drs.Bob's Delphi Notes
These are the voyages using Delphi Enterprise (and Architect). Its mission: to explore strange, new worlds. To design and build new applications. To boldly go...
Title:

SQL duplicate fields and TClientDataSet read-only "gotcha"

Author: Bob Swart
Posted: 11/16/2009 6:25:01 PM (GMT+1)
Content:

Today was the first day of a 2.75-day Delphi 2010 training event I'm doing in Stockholm, Sweden for 17 people. One of the topics we covered today was DBX4 and DataSnap. The first demo (always live!) showed some strange behaviour: a TSQLConnection component pointing to a database, with a TSQLDataSet component containing some SQL SELECT query, then a TDataSetProvider and a TClientDataSet, followed by TDataSource and TDBGrid - all should be well, except that the fields in the grid were read-only. There was no way I could edit them. Huh? This never happened to me before. Saving the contents of the TClientDataSet to a briefcase file and loading them into another TClientDataSet also didn't help. I gave up, and rebooted my machine (from XP to Windows 7), where the same demo - rebuild from scratch - worked as a charm this time.

Back in the hotel, I loaded the original project, and it would still fail. I explicitly listed all fields in the TClientDataSet in the Fields Editor, and they would all have their "ReadOnly" property set to True. I could not see why, until I noticed two DEPT_NO fields (DEPT_NO and DEPT_NO2).

It turns out, the SQL SELECT statement I had written for the original project was:

select DEPT_NO, EMP_NO, DEPT_NO, FIRST_NAME, LAST_NAME from EMPLOYEE

The field DEPT_NO accidentally appeared twice in the select list. This actually caused the ClientDataSet to turn all fields "read-only", so I also couldn't edit them in the DBGrid.

I'm sure it's documented somewhere, but in the meantime this left me puzzled (and I'm sure the people in Stockholm tomorrow will get a good laugh when I tell them all about it).

Back  


3 Comments

AuthorPostedComments
Anonymous 09/11/16 19:42:49What if field names are coming from different tables but same name (using a join)?
Didier 09/11/17 11:00:31You need to alias the fields in that case; Any SQl based system I know of will have an issue with that!
jevans 09/11/18 18:05:05The reason this occurs is MSSql creates a read-only record set when ever there is data returned that has no way to be resolved back to the table.


New Comment (max. 2048 characters, no HTML):

Name:
Comment:



This webpage © 2005-2019 by Bob Swart (aka Dr.Bob - www.drbob42.com). All Rights Reserved.