Updating an ordered dataset in DBGrid 
Author Message
 Updating an ordered dataset in DBGrid

I have problem when trying to update data on an ordered dataset (a query
with an ORDER BY clause) in DBGrid.  When I remove the ORDER BY clause,
everything is fine.  Anyway, I'm using DBF files.

Can anyone please assist me.

Thanks

Kiok Teck



Mon, 16 Mar 1998 03:00:00 GMT  
 Updating an ordered dataset in DBGrid

Quote:

>I have problem when trying to update data on an ordered dataset (a query
>with an ORDER BY clause) in DBGrid.  When I remove the ORDER BY clause,
>everything is fine.  Anyway, I'm using DBF files.

This is true. There are a number of conditions under which a local SQL
query (one that is based on dBASE or Paradox tables) will result in a
read-only data set. These circumstances include: use of an ORDER BY clause
in a query, use of a LIKE operator, and linking two or more tables in a
SELECT query.

You can programmatically detect these read-only queries by testing the
CanModify property of the TQuery component. If this property contains a
False value, the data set will be read-only. Some circumstances (this
particular one not included) can be changed to produce a read-write result
set by setting the RequestLive property to True. However, this may produce
a performance degradation in many situations, the degree of performance
loss varying in each situation.

You can still use read-only data sets retruned by a TQuery, but with some
change in how the data is used or accessed. In many cases, read-only data
would be used purely for row selection, with another TQuery data set linked
to it for editing the data in the row selected. This is done by
parameterizing the second TQuery SQL statement to filter the data to one
row that matches the current row in the first (read-only) TQuery result
set, setting the DataSource property to the TDataSource component connected
to the first TQuery, and by setting this second TQuery component's
RequestLive property to True.

For example, take the sample dBASE table HOLDINGS.DBF. A TQuery (here,
Query1) could be used to display all or a portion of the rows from the
table, ordered by the SYMBOL field, the ordering accomplished via an ORDER
BY clause in the Query1 SQL statement. This result set would be read-only,
and used exclusively to select a row to edit.

  SELECT *
  FROM "HOLDINGS.DBF" H
  ORDER BY H."SYMBOL"

A second TQuery component (Query2) would be used to obtain the row selected
in Query1, but in editable form. The DataSource property would be set to
DataSource1 (the TDataSource connected to Query1) so that a parameter in
the SQL property will automatically use a value coming from DataSource1 for
its filtering criteria. The RequestLive property would be set to True to
return an editable data set. No ORDER BY clause would be needed in this
query as ordering is not applicable when only one record is to be returned.
A parameter of the same name as the filtering field (ACCT_NBR) is used in a
WHERE clause, the value for which will come automatically through the
setting of the DataSource property. Note that the parameter need not be
manually set up in the Params property for Query2, this is automatic.

  SELECT *
  FROM "HOLDINGS.DBF" H
  WHERE H."ACCT_NBR" = :acct_nbr

As the row pointer in Query1 is moved (presumably in a multi-record TDBGrid
component), the local SQL for Query2 is re-executed to filter its data to
just a single row, the row with the same value for the ACCT_NBR field as
that in the current row in Query1. Single-record editing controls (such as
the TDBEdit and TDBMemo) can now be connected to Query2 (through a
TDataSource component) to effect the actual editing.

Linking two (or more) TQuery components in this manner is described in more
detail in the Database Application Developer's Guide, Chapter 5 "Using SQL
in applications," starting on page 123 in the section "Using the DataSource
property."

**************************************************************************
Steve Koterski
Local InterBase Server Technical Support
Borland International, Inc.



Mon, 16 Mar 1998 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Ordering a linked dataset

2. Updating read only dataset

3. Updating a dataset

4. row in dataset from row in dbgrid

5. Use of DBGrid without dataset

6. DBGrid with vertical scrollbar not scrolling the dataset???

7. Q:Order Sequence in DBgrid ??

8. DBGrid - Changing order of fields

9. Problem with DBGrid (changing the column order)

10. Help needed storing the custom column order of a dbGrid

11. Delphi 1.0 - DBGrid field order descending

12. DBGrid Column order (not SQL)

 

 
Powered by phpBB® Forum Software