Problems with OnCalcFields() Event!

[jovicat]

Parameters:
Delphi6.0, DB- MSSQL, ADO Connection;

I have table with > 3000000 records and ADODataSet (SELECT * FROM PTABLE WHERE LONG='Parameter') that return recordset with 1 up to 200 records. The result is displayed in DBGrid. I need to have numbers on every record in DBGrid. Example: If RecordSet have 20 records, they have to be numbered from 1,2 .. to 20. My aproach was to make calculated field and write onCalcField event where ADODataSet.FieldByName('CountNo').Value:=ADODataSet.RecNo.
Since RecNo is documented as property what is the actual position in resulting record set. I have result -1 for first record and OK for others, but when I add new record (with INSERT) and requery the ADODataSet, the numbers are not OK.
For example: Items in Invoice, and in DBgrid I want to numerate every item from 1 to N.

Anyone have same problem ?

 

[lespaul]

Here's a query that adds a "row number"

Select count(*) as rownum,B.id
From PTABLE A
inner join PTABLE B
On A.id <= B.id
group by B.id

where A.id and B.id is the PK of PTABLE

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jovicat]

To laspaul, Thank You for the query.
This query do the job, but problem is that my table has >3000000 records, and the query has to be executed on every delete/append, and it is a little bit slow.
I tried to solve the problem in Delphi (not in MSSQL), with oncalcfields(). The property AdoDataSet.RecNo work fine in any other place but not in OnCalcFields().
I try this with setting DataProvider (with DataSet pointing to my AdoDataSet) and ClientDataSet , and put calculated field in ClinetDataSet, than RecNo in OnCalcFields() work fine, but this solution is also slow.

 

[LucieLastic]

hi

It sounds like you would be better creating a VIEW in the database from your query which includes an extra (autoinc) column. Would this be possible?