I discovered the Data Environement Object, and I can't really get it to work my way.
First what I want it to get the Max Value of a field, and I was hoping to accomplish this with a single call to the command like:
MySort = MyDE.MyCommand (MyParent)
MyTable (table name) has the following fields:
MyId, MyName, MyParent, MySort
I would like to get the maximum value of the MySort field grouped by MyParent. I would like to do this all in the Data Environement (Command object) and not in the database directly. I will tell you what I did, and you please tell me where I went wrong ...
1. I created a Command Object and used a SQL-statement like:
SELECT MyTable.* FROM MyTable ORDER BY MyParent, MySort
MyId MyName Parent Sort
0 MyGroups 0 0
6 Main1 0 1
7 Main2 0 2
1 Main3 0 4
5 Main4 0 5
2 Main5 0 6
3 Sub1 1 1
4 Sub2 1 2
2. I grouped it by MyParent.
3. I put the aggregate to Max (field: MySort)
Now the result is:
My parent / My Sort
0 / 6
1 / 2
4. In order to get a parameter I change the SQL to:
SELECT tblGroups.* FROM tblGroups WHERE GrpParent = ? ORDER BY GrpParent, GrpSort
Now the code looks like:
MyDE = Data Environement Object name
ByParent = Command objects - grouping name
MyParent = Variable that contains a parameter value
That's what I want. Then I thought I need only 1 value to be returned, not a recordset. I was thinking of adding a output parameter, and uncheck the "recordset returning" checkbox on the advanced tab of the command object properties.
How can I put an output parameter at design-time?
How can I access this value now? In code I mean.
How can I set the default value to "1" in case there are no records that match the criteria?
How can I add the value "1" to the returned value in the command object itself?
The command name is MyCommand.
The Grouping name is ByParent
The Aggregate name is MaxSort
I hope you understand.