I have a generic form that will be used to browse records in a single table or for parent / child tables. The form uses an UltraGrid and will dynamically select the table(s) to browse. I define a subset of database columns to display in the Grid and I'd like to use the FIELDS option to limit the columns in the query to those fields as well. I also want to do this without using datasets as the binding source.
I can get this to work fine for a single level query just fine. See the attached file for the basic steps I'm using (pseudo code).
This displays the grid with the proper columns (in the order specified in the properties we set rather than in the database table / column order) and with data. The correct columns display with or without the FIELDS phrase.
However, this code chokes on a Parent/child query at the bindingSource1:HANDLE = hQuery statement with the following error message:
System Exception: You must set the binding source’s handle property to a dataset if you specified DataSet schema with the TableSchema property.
The grid does display with the proper columns for each band, just no data.
If I remove Progress.Data.TableDesc from the code, and re-run the test for a single table using the FIELDS phrase, I get error 8826
(Field ) was missing from FIELDS phrase.)
for each field in the database table not in the FIELDS phrase. All columns in the database table display in the grid, essentially ignoring the GridBand columns.
If I then remove the FIELDS phrase, the error 8826 goes away, but all columns in the database table still display.
Without the Progress.Data.TableDesc and FIELDS phrase for the multi-level query, I get the error
System.ArgumentException: Key already exists.
when the WAIT-FOR THIS-OBJECT:ShowDialog( ) is executed. I am unable to figure out what key the error might me referencing.
Any help in this area would be greatly appreciated. Again, the goal is to do this without using datasets and a subset of the database table columns.
Thanks,
Jim
[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/SampleGridCode.txt:550:0]
If you have ambiguous field names did you qualify the fields with the appropriate buffer name? I was just reading page 4-9 of the Advanced GUI Programming right when you posted this.
BTW
You cannot use the optional include-fields and except-fields lists of BindingSource()?
Thanks both Roger and Simon for the hint.
Guess I should have done a little more reading rather than playing;>)
I'll give this a try and post back.
JL
Ok, here's where I'm at...
I changed the code to use the include-fields & except-fields options. The single level query works great, but I still have an error with parent/child queries.
The second statement below is throwing the error:
bindingSource1 = NEW Progress.Data.BindingSource(hQuery,cFieldList,"").
ultraGrid1:DataSource = bindingSource1 NO-ERROR.
The error is:
System.ArgumentException: Key already exists.
I get this if creating the GridBands and their columns manually or if that code is commented out. After the error, I get one level in the grid with apparently duplicate records. It may be that a row is being created in the top level grid for each record found in the join tables.
I've double and triple checked the buffers and queries being used and they look fine.
I made sure the include-fields and join fields are qualified w/ the table name. I rechecked the index set when the GridBand is created, but don't think it's the problem because the error is the same when not creating them.
Attached are screen shots of my include-fields and query statement.
Thanks,
JL
Well, I'm not using bands but I think it might have got something to do with duplicate entries with your include-fields list?
See attachment. When I specify include-fields list is "*" then I get your Key already exists error. When I specify "Order.Ordernum,Order.CustNum,Orderline.LineNum,Orderline.ItemNum" then the error disappears...
[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/demo.p:550:0]
Hmmm.
Could be that, internally, Progress is setting the key for a column to the field-name alone, not qualifying it with the table name.
If my query is not using "OF", but specifying the fields for the join such as
"where Orderline.Ordernum eq Order.Ordernum"
(which currently it is) wouldn't we have to include that field in the include-fields for both tables?
If I change the include-fields to "Order.Ordernum,Order.CustNum,Orderline.Ordernum,Orderline.LineNum,Orderline.ItemNum" I get the error because Ordernum (Order and Orderline) is a duplicate.
It looks like you have duplicate entries for oe-line-num (oe-line and oe-exp)...
I was able to run your sample and see the error, but I noticed that when I ran it with the limited field list, there was only one level in the grid.
One thing I may not have mentioned specifically was that each level of the query needs to be in it's own band. (See attached MultiBand.jpg). This example was run using a dataset. I'm trying to perform the same without the dataset.
Just for kicks, in my code that creates the GridBands, I changed the key to include the table-name qualifier (TableName + ColumnName) for each column I create in the GridBand. I ran the test for a single level query and got two sets of columns for each field (See attached QualifiedBands.jpg).
It seems like the problem is that Progress is creating a single level Grid (one GridBand) from a multi-level query against database tables. If there were multiple GridBands, the duplicate fields between each level of the query would be irrelevant. Even if I create multiple Band levels programmatically, it looks like they're being ignored.
Could there be a setting on the grid or datasource I'm missing that tells the Grid to be multi band (one for each table in the query)?
Thanks,
JL
One thing I may not have mentioned specifically was
that each level of the query needs to be in it's own
band. (See attached MultiBand.jpg). This example was
run using a dataset. I'm trying to perform the same
without the dataset.
The query's result set is a single view of the tables in the query. This "flattened" view is not suited for multi-level views (in treeviews or multiple bands).
Could there be a setting on the grid or datasource
I'm missing that tells the Grid to be multi band (one
for each table in the query)?
Rather unlikely, although the grid do have the ability to GroupBy columns in a band and thus create a tree level view from the flat view, but this is entirely different and more similar to using a break-by and you can in fact have summaries, like total, average and count for each group level.