VN
|
Posted: 06/03/2005, 8:49 PM |
|
Hi
There have been many excellent suggestions for making a great product even greater, and I support them all... but this would be my number one wish:
I have so many situations where customers demand to be able to update many rows at once (like in Editable Grid) but the columns are not known in advance.
Like for example when editing stock levels (this is not a real example but illustrates what I'm talking about). The 'Stock' table would have columns Store, Item, Stock:
Sample data:
Boston, Shirts, 530
Boston, Jeans, 678
Boston, Sox, 789
Chicago, Shirts, 432
Chicago, Jeans, 729
Chicago, Sox, 121
Denver, Shirts, 876
Denver, Jeans, 551
Denver, Sox, 25
It would be great to have an editable grid that looks like this:
-| Store |-------| Shirts |-----------| Jeans |---------| Sox|
Boston 530 678 789
--------------------------------------------------------------
Chicago 432 729 121
--------------------------------------------------------------
Denver 876 551 25
--------------------------------------------------------------
and I can edit all the values. I can guarantee that the stock items are the same for each store (eg every store would have items 'Shirts', 'Jeans, and 'Sox' ), as the data in this table would come from two other tables - Stores and Items.
My clients are loosing patience with me having to put this off...
Many thanks in anticipation (or is there a way to already do this in CCS?)
Voya
|
|
|
blankenb
Posts: 125
|
Posted: 08/11/2005, 10:42 PM |
|
I'd like to second this request
|
|
|
Michael Mikkelsen
|
Posted: 08/26/2005, 12:33 PM |
|
This can already be done without too much trouble. You just need a write your SQL query to pull the data out of the database and a custom insert and custom update. Depending on how your data is stored you may need to use a UNION in your SQL query. When you do your inserts and updates you will have multiple SQL statements seperated by ;'s. You may even need to you some kind of transation lock to insure data integrety.
|
|
|
VN
|
Posted: 08/30/2005, 6:47 PM |
|
Michael
Can you please elaborate. Any sample sql code would be appreciated. The point is to display an editable grid where the number of columns is not known in advance. AFAIK the editable grid builder requires you to specify the columns when designing the grid.
VN
|
|
|
Imtiaz Qamar
|
Posted: 10/26/2005, 8:54 PM |
|
VN
Did you get the solution. If yes, will you please share the solution with me. Me also having problem in showing editable data grid. Some code sample would be appreciated.
Imtiaz - imtiazqamar_ilm@hotmail.com
|
|
|
dragoon
Posts: 173
|
Posted: 10/27/2005, 12:08 AM |
|
here is the SQL (MS SQL) to get the data prepared for that grid
select S.Store,I1.Shirts,I2.Jeans,I3.Sox from
(select Store from Table1 group by Store) S left outer join
(select Store, Stock as Shirts, '' as Jeans, '' as Sox from Table1 where Item = 'Shirts'
group by Store,Stock) I1 on S.Store=I1.Store left outer join
(select Store, '' as Shirts, Stock as Jeans, '' as Sox from Table1 where Item = 'Jeans'
group by Store,Stock) I2 on S.Store=I2.Store left outer join
(select Store, '' as Shirts, '' as Jeans, Stock as Sox from Table1 where Item = 'Sox'
group by Store,Stock) I3 on S.Store=I3.Store
order by S.Store
|
|
|
VN
|
Posted: 10/27/2005, 10:44 PM |
|
This works fine if you know the 'items' in advance... What if they aren't known, e.g. I1, I2, I3, ..., In where 'n' is not known in advance?
Quote dragoon:
here is the SQL (MS SQL) to get the data prepared for that grid
select S.Store,I1.Shirts,I2.Jeans,I3.Sox from
(select Store from Table1 group by Store) S left outer join
(select Store, Stock as Shirts, '' as Jeans, '' as Sox from Table1 where Item = 'Shirts'
group by Store,Stock) I1 on S.Store=I1.Store left outer join
(select Store, '' as Shirts, Stock as Jeans, '' as Sox from Table1 where Item = 'Jeans'
group by Store,Stock) I2 on S.Store=I2.Store left outer join
(select Store, '' as Shirts, '' as Jeans, Stock as Sox from Table1 where Item = 'Sox'
group by Store,Stock) I3 on S.Store=I3.Store
order by S.Store
|
|
|
dragoon
Posts: 173
|
Posted: 10/28/2005, 3:14 AM |
|
then you'll need a more complicated schema ...
the only way I can think it right now is using some cursors
I'll look into it ....
|
|
|
|