>>>>>

>>>>>String cStr = "SELECT  ..... FROM MainTable WHERE Key IN ("
>>>>>loop for each key you have in memory
>>>>>     cStr = cStr + Key.ToString() + ","
>>>>>endloop
>>>>>cStr = LEFT(cStr, LEN(cStr)-1)+")" && This is a VFP code, you should translate it to VB.NET :-)
>>>>>
>>>>>execute cStr 
>>>>>

>>>>
>>>>Not the most elegant solution, but definitely a solution to my problem. Thank you very much. :)
>>>>
>>>>
>>>>
>>>>>>>>I'm looking for a way to speed up a calculation routine that computes the total of a document that can contain up to a few hundred lines. The routine is slow now because for each line I access the db. I'm looking for a way to implement this in a more data oriented way.
>>>>>>>>
>>>>>>>>In VFP I would have dumped the relevant keys into a cursor and then join the tables to the cursor in an sql statement.
>>>>>>>>
>>>>>>>>I could of course simulate this by dumping the keys into a temporary table (which if I'm right would not limit the amount of times I access the db), but I was wondering if there is not a more "elegant" way to do this.
>>>>>>>>
>>>>>>>>TIA.
>>>>>>>
>>>>>>>I'm not sure I understand you right, but you could use derived tables in your SELECT.
>>>>>>>

>>>>>>>SELECT .....
>>>>>>>FROM MainTable
>>>>>>>INNER JOIN (SELECT Keys From SomeTable WHERE ...) DympedKeys
>>>>>>>   ON ....
>>>>>>>

>>>>>>
>>>>>>Right Borislav, I'm sorry, I was not explicit. I'm not working in VFP, but in vb.net. And my keys are for the time being stored in some sort of datatable in memory.
>>>>>>The problem of speed is relevant because I need to recompute the totals each time the user enters or changes a field in the document.
>>>>>
>>>>>I saw you posted in NET forum, so I expected that.
>>>>>But the code I gave you is pure SQL code, it is not Front end related, and it can be executed no matter what DB is used.
>>>>>The only drawback is that you have these keys in memory.
>>>>>Could you build your SELECT on the fly and concatenate these keys?
>>>>>(pseudo code)
>>>>>
>>>
>>>I see that you use ACCESS.
>>>If you use SQL Server 2008 you could pass a Table valued parameter to SP.
>>>http://msdn.microsoft.com/en-us/library/bb675163.aspx
>>
>>Boris, thank you for this. When I grow up I will definitely implement SQL Server :). I thought this would have a rather "evident" solution, but in fact, the one you suggest, i.e. building the select on the fly is perfectly implementable. I have googled around and my first reaction of "do people actually do this?" got a definite "yup" answer.
>>
>>Thanks again and kind regards,
>>
>>Marc
>
>Marc,
>
>(1) Concerning the string concatenation, it may be a good idea to use the StringBuilder http://msdn.microsoft.com/en-us/library/system.text.stringbuilder.aspx since we append a few hundred times
>
>
>(2) If you have all the keys in memory, why not keep the field (you use for the total) in memory as well ?

Gregory,

I am aware of (1).

As far as (2) is concerned, the problem at hand entails submitting a few fields to a set of business rules. It's nor impossible of course to reverse the process in order to "subtract" the ex ante values, but I kind of hesitate to go there.