Board index » delphi » Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling

Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling

Hello,

I have been going nuts for a few days reading the VCL source and a
gazillion messages on the Borland newsgroups in hopes of finding a
strategy to implement accurate DBGrid scrolling for a dataset from a
SQL Server using a TStoredProc.

In the messages I read I have discovered that the DBGrid is not
accurately aware of it's current record number.  The DBGrid.Row
property is only good for the rows that are visible.  The control does
not (nor should it) draw the rows for the entire dataset.

No matter what I always get -1 as the record number in the de{*word*81}.
I suspect this is due to the nTStoredProc returning IsSequenced as
FALSE.  To do this right it seems you would need to alter the VCL or
create a descendant TDBDataset component and override the GetRecNo()
method and declare it public.  Then create a descendant TStoredProc
that returned IsSequenced as TRUE.

Has anyone got this to work right?

I have been successful at hiding the scrollbar and placing a
TScrollbar component next to the grid and navigating up or down.  The
tricky part is synchronizing the TScrollbar when the dataset moves.
The OnDataChange event is the easiest way to tap into the dataset when
the cursor moves but with no indication of direction it is not an
option.

Will I need to use a lower level DBI function to get the cursor
position?

Can someone please offer some insight here?

A DBGrid that incorporates correct scroll bar positioning for SQL
based datasets would be great.

Thank You

--
Reid Roman
Future Generation Software
http://www.futuregenerationsoftware.com

 

Re:Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling


Two questions: 1. what's the database server; 2. why can't you work with PK
field(s) instead. With non-flat file backends, RecNo is not the way to go -
at all!.

rb

Re:Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling


Hi Reid
Im sorry but you are never going to be able to get a TStoredProc to return an accurate RecNo. You could however create a descendant that 'guesstimates' the RecNo which would make the grid behave more like you want, but that might be quite a lot of work and still not give you a close match to what you want.

The only way you are going to be able to get accurate RecNo information is to load the result set returned by the SP into a memory table and browse on that.

John Hair
Designer Systems Ltd

Re:Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling


They are using Microsoft SQL Server 7.0.

The Primary keys are also the Identity columns.

If you are suggesting using the primary key as an indexing number this
would not work in this scenario for positioning the scrollbar because
the user can sort the grid by clicking on the column's title so the PK
would not represent anything but a unique key.

Thanks

--
Reid Roman
Future Generation Software
http://www.futuregenerationsoftware.com

Quote
"rb" <ra...@killspam-videotron.ca> wrote in message

news:3b8174ee$1_1@dnews...
Quote

> Two questions: 1. what's the database server; 2. why can't you work
with PK
> field(s) instead. With non-flat file backends, RecNo is not the way
to go -
> at all!.

> rb

Re:Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling


I have found that yes, the TStoredProc does not hold the key.

The thing that seems funny to me is that considering the amount of
developers using this control and Delphi is now D6, the issue would of
been solved by now ..

I would be happy to find a third party control to buy and be done with
this one ..

Thanks

--
Reid Roman
Future Generation Software
http://www.futuregenerationsoftware.com

Quote
"John Hair" <john.h...@dsystems.co.uk.x> wrote in message

news:3b83a530$2_1@dnews...
Quote

> Hi Reid
> Im sorry but you are never going to be able to get a TStoredProc to

return an accurate RecNo. You could however create a descendant that
'guesstimates' the RecNo which would make the grid behave more like
you want, but that might be quite a lot of work and still not give you
a close match to what you want.
Quote

> The only way you are going to be able to get accurate RecNo

information is to load the result set returned by the SP into a memory
table and browse on that.
Quote

> John Hair
> Designer Systems Ltd

Re:Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling


Quote
"Reid Roman" <re...@futuregenerationsoftware.com> wrote in message

news:3b8431fd_2@dnews...

Quote
> They are using Microsoft SQL Server 7.0.

> The Primary keys are also the Identity columns.

> If you are suggesting using the primary key as an indexing number this
> would not work in this scenario for positioning the scrollbar because
> the user can sort the grid by clicking on the column's title so the PK
> would not represent anything but a unique key.

I'm trying to put together a solid answer for you, but I can't. I can think
of nothing else but questions.

Before anything, here's a solution in style of your statement from the other
reply:

    - Absolutely want RecNo? Get yourself MemData from DevExpress and get
this over with.

Back to other things. I don't think your idea with using RecNo to position
ScrollBar will ever work. Well, it could, but it's doomed :). Think
sorting!! Would you not want your users to have ability to work the grid -
sort and resort, ascending/descending, even multisort (funny, DevExpress
comes to mind again). What would you do in that case? Rescan your dataset
and reassign RecNo's again, just so you can position ScrollBar properly? I
think not! Of course, if this sort of GUI improvement is not an issue, how
come ScrollBar position is? And if it's not an issue now, how long before it
becomes one?

The only thing I can tell you is that without some real component hacking,
you won't be able to solve this problem. Or, you can buy a good suite that
includes grids that suits your needs.

Sorry, have nothing better to tell you.

rb

Re:Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling


Hi Reid!

On Mon, 20 Aug 2001 12:47:52 -0700, "Reid Roman"

Quote
<re...@futuregenerationsoftware.com> wrote:
>A DBGrid that incorporates correct scroll bar positioning for SQL
>based datasets would be great.

The only way I managed to get this was through TGridDataLink class,
there is the code:

  TGridDataLink = class(TDataLink)
  private      
                ...
    // Added
    FRecordNo: longint;
  public
                ...
    // Added
    property RecordNo: longint read FRecordNo;
  end;

constructor TGridDataLink.Create(AGrid: TCustomDBGrid);
begin
  inherited Create;
  ...
        FRecordNo := 0;
end;

procedure TGridDataLink.ActiveChanged;
begin
        FRecordNo := 0;
        ...
end;

procedure TGridDataLink.DataSetChanged;
begin
        FRecordNo := 0;
  ...
end;

procedure TGridDataLink.DataSetScrolled(Distance: Integer);
begin
        FRecordNo := FRecordNo + Distance;
  ...
end;

And then in TDBGrid use GridDataLink.RecordNo ... If it hits under 0
then make it 0.

tomi.

Re:Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling


Thank you for the replies.

As far as the MemData plan,  it would of been a good one.  The
unfortunate thing is that I am a contractor on this project and the
project is in the final phase.   Changing ALL the grids in all the
screens to use an in memory table would take too long to get
authorized.

I think Tomislav K. might have offered an alternative..

Thanks

--
Reid Roman
Future Generation Software
http://www.futuregenerationsoftware.com

Quote
"rb" <ra...@killspam-videotron.ca> wrote in message

news:3b84723b$1_1@dnews...
Quote

> "Reid Roman" <re...@futuregenerationsoftware.com> wrote in message
> news:3b8431fd_2@dnews...
> > They are using Microsoft SQL Server 7.0.

> > The Primary keys are also the Identity columns.

> > If you are suggesting using the primary key as an indexing number
this
> > would not work in this scenario for positioning the scrollbar
because
> > the user can sort the grid by clicking on the column's title so
the PK
> > would not represent anything but a unique key.

> I'm trying to put together a solid answer for you, but I can't. I
can think
> of nothing else but questions.

> Before anything, here's a solution in style of your statement from
the other
> reply:

>     - Absolutely want RecNo? Get yourself MemData from DevExpress
and get
> this over with.

> Back to other things. I don't think your idea with using RecNo to
position
> ScrollBar will ever work. Well, it could, but it's doomed :). Think
> sorting!! Would you not want your users to have ability to work the
grid -
> sort and resort, ascending/descending, even multisort (funny,
DevExpress
> comes to mind again). What would you do in that case? Rescan your
dataset
> and reassign RecNo's again, just so you can position ScrollBar
properly? I
> think not! Of course, if this sort of GUI improvement is not an
issue, how
> come ScrollBar position is? And if it's not an issue now, how long
before it
> becomes one?

> The only thing I can tell you is that without some real component
hacking,
> you won't be able to solve this problem. Or, you can buy a good
suite that
> includes grids that suits your needs.

> Sorry, have nothing better to tell you.

> rb

Re:Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling


Hello,

Thank you for this insight.

I guess the steps would be to ..

1) Create a TGridDataLink class like below.
2) Create a TMyDBGrid class that replaces it's FDataLink member with a
TGridDataLink member.

Is that what you are suggesting?

Thanks

--
Reid Roman
Future Generation Software
http://www.futuregenerationsoftware.com

Quote
"Tomislav Karda?" <nomail@sorry> wrote in message

news:3b8506cb.17474747@newsgroups.borland.com...
Quote
> Hi Reid!

> On Mon, 20 Aug 2001 12:47:52 -0700, "Reid Roman"
> <re...@futuregenerationsoftware.com> wrote:

> >A DBGrid that incorporates correct scroll bar positioning for SQL
> >based datasets would be great.

> The only way I managed to get this was through TGridDataLink class,
> there is the code:

>   TGridDataLink = class(TDataLink)
>   private
> ...
>     // Added
>     FRecordNo: longint;
>   public
> ...
>     // Added
>     property RecordNo: longint read FRecordNo;
>   end;

> constructor TGridDataLink.Create(AGrid: TCustomDBGrid);
> begin
>   inherited Create;
>   ...
> FRecordNo := 0;
> end;

> procedure TGridDataLink.ActiveChanged;
> begin
> FRecordNo := 0;
> ...
> end;

> procedure TGridDataLink.DataSetChanged;
> begin
> FRecordNo := 0;
>   ...
> end;

> procedure TGridDataLink.DataSetScrolled(Distance: Integer);
> begin
> FRecordNo := FRecordNo + Distance;
>   ...
> end;

> And then in TDBGrid use GridDataLink.RecordNo ... If it hits under 0
> then make it 0.

> tomi.

Re:Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling


Quote
"Reid Roman" <re...@futuregenerationsoftware.com> wrote in message

news:3b856cd9_1@dnews...

Quote
> Thank you for the replies.

> As far as the MemData plan,  it would of been a good one.  The
> unfortunate thing is that I am a contractor on this project and the
> project is in the final phase.   Changing ALL the grids in all the
> screens to use an in memory table would take too long to get
> authorized.

> I think Tomislav K. might have offered an alternative..

Tomislav's idea is great, but, following up on what you said about being a
contractor and the stage your project is in, I wouldn't agree that's the
easier way for you to go. Unless I'm missing something, here's what you need
to do

1. Create your TGridDataLink
2. Inherit TDBGrid and teach the new component to use your TGridDataLink
instead.
3. Go replace components in all your units

Hmmm. I don't know. As I said, maybe I'm missing something.

To read MemData, you need DataSource. DBGrid doesn't care what kind of
dataset is linked to its linked datasource. DevExpress MemData allows you to
"copy" records from your original dataset in one line of code. So, unless
you're using "live" editing or cashed updates, my vote is on MemData.

On the final note, let me reiterate what I said before. This seems to be a
GUI issue. Get yourself a good grid (InfoPower, DevExpress) and replace
components once and for all. At the same time, you'd get so many additional
(out of box) features, your clients will love you for them.

Good luck either way.

rb

Re:Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling


Thanks again for the reply..

Just forthe record InfoPower's DBGrids behave the same way.

:0

--
Reid Roman
Future Generation Software
http://www.futuregenerationsoftware.com

Re:Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling


Hi Reid!

On Thu, 23 Aug 2001 14:03:31 -0700, "Reid Roman"

Quote
<re...@futuregenerationsoftware.com> wrote:
>Thank you for this insight.

>I guess the steps would be to ..

>1) Create a TGridDataLink class like below.
>2) Create a TMyDBGrid class that replaces it's FDataLink member with a
>TGridDataLink member.

>Is that what you are suggesting?

You have to change VCL source in dbgrids.pas.

The steps are:

1. Just copy dbgrids.pas unit from a VCL source into your project
directory and include it in your project files (if you don't include
it, it will be used anyway but this is more clear).

2. Change this dbgrids.pas the way you want (just add some code and
define new properties) and then just compile your project with using
this new added things.

This would be quite easy! Isn't it? You don't have to change anything
in the rest of the source of your project (but even that wouldn't be a
problem, I have an automatization for that too).

regards,
tomi.

Re:Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling


Reid,

This problem has been discussed at length over the years. The basic
problem is that with a TQuery (which is the equivalant of your TStoredProc
returning a result set) you NEVER know how many rows are in the result set
until you have fetched all the rows.

The only way to get an accurate scroll bar is to fetch all the rows from
the database. This works for EVERY grid type control in Windows. This has
nothing to do with a particular contorl or Delphi itself. It is inherent
in the nature of C/S applications. Those of you (not just you Reid) who
have written DB apps in the past using database TTables or thier equvilant
are used to having full knowledge of the table and the number of recs.
When you move to C/S apps that mindset is all different. What you learned
before gets you in trouble.

The best way to handle this is to NOT query the entire table if it is
large but return a more limited result set based upon some parameters
entered by the user. Then as the user scrolls thruogh the result set the
scroll bars will become more accurate. All the scroll bars do is position
within the range of values they know. As more rows are fetched from the
query, this range of values increases and with it the accuracy of the
scroll bar.

Best Regards,

Allen.

Quote
Reid Roman wrote:
> Hello,

> I have been going nuts for a few days reading the VCL source and a
> gazillion messages on the Borland newsgroups in hopes of finding a
> strategy to implement accurate DBGrid scrolling for a dataset from a
> SQL Server using a TStoredProc.

> In the messages I read I have discovered that the DBGrid is not
> accurately aware of it's current record number.  The DBGrid.Row
> property is only good for the rows that are visible.  The control does
> not (nor should it) draw the rows for the entire dataset.

> No matter what I always get -1 as the record number in the de{*word*81}.
> I suspect this is due to the nTStoredProc returning IsSequenced as
> FALSE.  To do this right it seems you would need to alter the VCL or
> create a descendant TDBDataset component and override the GetRecNo()
> method and declare it public.  Then create a descendant TStoredProc
> that returned IsSequenced as TRUE.

> Has anyone got this to work right?

> I have been successful at hiding the scrollbar and placing a
> TScrollbar component next to the grid and navigating up or down.  The
> tricky part is synchronizing the TScrollbar when the dataset moves.
> The OnDataChange event is the easiest way to tap into the dataset when
> the cursor moves but with no indication of direction it is not an
> option.

> Will I need to use a lower level DBI function to get the cursor
> position?

> Can someone please offer some insight here?

> A DBGrid that incorporates correct scroll bar positioning for SQL
> based datasets would be great.

> Thank You

> --
> Reid Roman
> Future Generation Software
> http://www.futuregenerationsoftware.com

Re:Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling


Quote
"Reid Roman" <re...@futuregenerationsoftware.com> wrote in message

news:3b86c04c_2@dnews...

Quote
> Thanks again for the reply..

> Just forthe record InfoPower's DBGrids behave the same way.

> :0

Yeah, :0 me too. I'm a bit surprised though. InfoPower 2000 had some really
cool stuff going. Me, I'm DevExpress fan.  I didn't want to sound as if I
work for them, which is why I mentioned InfoPower's one as well. DevExpress
grid has things to blow your mind. Check their demo. Whatever you find in
the demo - it's true. And IMHO, it's even better.

rb

Re:Gettting the Record Number from a Stored Procedure -and- DBGrid Scrolling


Hi Reid!

I forgot something ...

Make a public readonly property RecordNo at TCustomDBGrid and here is
the read method for it:

function TCustomDBGrid.GetRecordNo: longint;
begin
        result := FDataLink.RecordNo + Row;
end;

FDataLink.RecordNo would be record number for the first row displayed
in the grid so we have to add grid row to get the position of current
row in dataset.

This will work while you navigate through DBGrid with record up/down
and page up/down, but! Record position maintained like that will be
lost if you position dataset from the outside of grid. In the code I
posted earlier this will just put RecordNo to 0. Also moving to last
record with Ctrl+PgDn would also loose count. Maybe something can be
made in those datalinks but it's out of my knowledge. I still think
that dataset when moved should know where it is moved from, how many
records have been skipped but the developers didn't care to build that
into the thing.

I used my approach for maintaing information if the row is odd or even
for alternate row display color and it served for that purpose.

The faniest thing in the world is that dataset fetched with TQuery
from MSSQL server is internaly cached in memory/disk (I am not talking
about cached updates here) and when once fetched the cursor is closed
on the server side - and people still keep on convincing us that no
way we know the record number we are on. Well, maybe in a theory we
should not know, but how the things are made we very well could know
but, it's not implemented. To bad.

tomi.

Go to page: [1] [2]

Other Threads