String to TString... is that possible??

[RobPouwelse]

I need to sort a database (OnButtonClick) i'm now using query's to do the job, but for some reason it doesn't take this, it wants a Tstring instead of a string. When i used a Memo before to insert my own SQL query it worked perfectly, but when i do it like this (so the user doesn't have to know SQL to sort the records in the database) it just won't work.

Query1.SQL := 'Select * from Aangeboden Order by Part';

any Tips form the Tek's ?

 

[Nordlund]

Yeps.

Try This:

Query1.SQL.Text := 'Select * from Aangeboden Order by Part';
Andreas Nordlund
Software developer

 

[RobPouwelse]

Now why didn't i think of that ..

 

[RobPouwelse]

Huh??..
Now i don't get my DbGrid anymore, it doesn't show any records.. !?!

This is (roughly speaking) my program:

DBGrid1.Datasource := DataSource1;
with Table1 do
begin
Close;
TableName := Db_Aangeboden;
Open;
Form1.Caption := TableName;
end;
DataSource1.DataSet := Table1;
Query1.SQL.Text := 'Select * from Aangeboden Order by Part';
Query1.DatabaseName := ExtractFilePath(Db_Aangeboden);
try
Query1.Open;
Table1.Close;
Datasource1.DataSet := Query1;
except
on E:Exception do
begin
Showmessage(E.Message);
DataSource1.DataSet := Table1;
end;
end;
Query1.Close;
Table1.Open;

after that you should be able to : Alter the records and Order them by parts (with buttons).

But i don't get any records on my dbgrid
when i delete the last Table1.Open i get NO records
when i delete the last Query1.Close i get the records but i can't alter them
when i delete the last Table1.Open & Query1.Close i get the records but i can't alter them
when i delete nothing, i get NO records

so, for me to c the records i have to have Query1 Open.. The strange thing is, before i made the SQL statement using a button+Memo in which the Memo.Lines would bethe statement.. (and meaning that the Query wouldn't be made at the start of the DBgrid) and it worked just fine..

What am i doing wrong here??

 

[Elkari]

Instead of the line -
Query1.SQL.Text := 'Select * from Aangeboden Order by Part';
Try -
Query1.SQL.Add( 'Select * from Aangeboden Order by Part' );

I have my SQL statements arranged like -

Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add( <Query> );
Query1.Open;
Query1.First;

 

[TealWren]

Why are you moving the datasource around? You could always have your datasource attached to a query instead of a table.

Does your grid have rows but just no data? Or no rows?

Are you sure that query returns results? Maybe the table is empty or you're looking at a different table than you thought? From that code, it looks like you're setting the database name for the query but not for the table. So maybe when you were using the table it was using a dataset from a different location.

Just some ideas...

TealWren

 

[RobPouwelse]

Elkari : I see that you Close your query, then make the statement and finally Open your query, but do you leave your query Open for the rest of your program??

TealWren : My grid has no rows and i'm sure the query returns results because i made a .db which was all unsorted and stuff and when it opened (but i couldn't change any value's) it was all sorted.

and i think i just saw the problem that i couldn't change the value's anymore.... because Datasource1.Dataset was still Query1 which should've been Table1 (i think...)

i think it would be best to optimize that part a tiny bit better

 

[RobPouwelse]

hmmm, it wasn't as easy as i thought.
I found out that for the SQL statement to work, the Datasource had to be referenced(coupled) to Query1 (if there are any Dutch out there : i meant &quot;Datasource koppelen aan Query1&quot ....????.. bleh, i don't even know how to put it..!?

What i want :
A DBGrid showing a database, the user must be able to alter that database. A button &quot;Sort&quot; which (ofcourse) sort the database. And finally a button &quot;Publish&quot; which calls a procedure that goes through all the &quot;sorted&quot; records and put them in a textfile then opening a FTPconnection an uploading the textfile.

The &quot;Publish&quot; button already works.
Darn, i can't even think straight today.

 

[svanels]

If the dbgrid is coupled to the query, through a datasource, it will only show data when you call Tquery.open. Same like putting a Ttable active to true.

Set RequestLive = true, to make updates possible. The default will give you a read-only query.

If you have to change the sql statements at runtime, you have to close the query first, modify your sql statements and finaly re-open the query again.

Your sort or refresh button:
query1.close;
query1.open;
gewoon de query opniew runnen


Regards S. van Els
SAvanEls@cq-link.sr

 

[RobPouwelse]

Before i can use Query1, do i have to have Table1 open?
like this..:

with Table1 do
begin
Close;
TableName := Db_Aangeboden;
DatabaseName := ExtractFilePath(Db_Aangeboden);
DataSource1.DataSet := Table1;
Open;
end;

Query1.Close;
Query1.DatabaseName := ExtractFilePath(Db_Aangeboden);
Query1.SQL.Clear;
Query1.SQL.Add('Select * from Aangeboden Order by Part');
try
Query1.Open;
Query1.First;
except
on E:Exception do
begin
Showmessage(E.Message);
end;
end;
Query1.Close;

This happens when i create my form so that it is automaticly sorted.
Are there things i forgot?

tnx
R. Pouwelse

Ps: svanels, ik dacht al dat jij nederlands was ookal heb jij .sr in jou e-mail staan

 

[TealWren]

Actually you don't need Table1 at all. Only use the query and take the with table1 do ... end out all together!
TealWren

 

[RobPouwelse]

Problem with that is that i have 2 use Table1 for that &quot;publish&quot; procedure (Table1.FieldCount,Table1.DisableControls, etc.)

Table1 and Query1 are 2 different Tables?? (i didn't know how else to put it ) E-mail: Rob@matas.nl (till Jan 10, from then on it's Deathwish@winning.com)

 

[svanels]

Nee ik ben Surinamer. Woon ook in Suriname

TQuery and TTable have many things in common, if you you explore the properties I think you will see that you can get rid of the table1. S. van Els
SAvanEls@cq-link.sr

 

[RobPouwelse]

I made an only Query DatabaseProgram, ..it works, BUT! i still can't edit any record!

here it is :

DBGrid1.Datasource := DataSource1;
Query1.Close;
Query1.DatabaseName := ExtractFilePath(Db_Aangeboden);
Query1.SQL.Clear;
Query1.SQL.Add('Select * from Aangeboden Order by Part');
Query1.Open;
DataSource1.DataSet := Query1;
Query1.First;

Is there a property that i haven't set(right)??

properties of Query1:
Active = True
AutoCalcFields = True
CachedUpdates = False
Constrained = False
Constraints = (CheckConstraints)
DatabaseName = {set in the program to ExtractFilePath(Db_Aangeboden)}
Datasource = {otherwise i would get a circular link}
Filter =
Filtered = False
FilterOptions = []
NAme = Query1
ObjectView = False
ParamCheck = True
Params = (TParams)
RequestLive = True
SessionName =
SQL = (TStrings)
Tag = 0
UniDirectional = False
UpdateMode = upWhereAll
UpdateObject =

that's all of them
maybe it's usefull to note that i'm using a local database.

tnx in advance, E-mail: Rob@matas.nl (till Jan 10, from then on it's Deathwish@winning.com)

 

[TealWren]

RequestLive will /request/ a live query. I'm guessing you don't have an index on each thing that you're trying to order by. Here are the rules for local databases (paradox) which determine whether the query will be updateable:

Single-table queries

Queries that retrieve data from a single table are updatable provided that:

There is no DISTINCT key word in the SELECT.
Everything in the SELECT clause is a simple column reference or a calculated column, no aggregation is allowed. Calculated columns remain read-only.
The table referenced in the FROM clause is an updatable base table.
There is no GROUP BY or HAVING clause.
There are no subqueries in the statement.
There is no ORDER BY clause.

The read-only effect of an ORDER BY clause is negated and the query updatable if the ORDER BY clause uses a single column and there is a dBASE single-column primary or secondary index based on that same field. dBASE compound (expression) indexes will not negate the read-only effect of an ORDER BY clause. A Paradox single- or multi-field primary index will make the query updatable if the ORDER BY uses exactly the same columns (in the same order) as the index. Paradox secondary indexes will not negate the read-only effect of an ORDER BY clause.

Multi-table queries

All queries that join two or more tables will produce a read-only result set.

Calculated fields

For updateable queries with calculated fields, an additional field property identifies a result field as both read-only and calculated. Every call to the BDE function DbiPutField causes recalculation of any dependent fields.


Good luck! TealWren

 

[RobPouwelse]

So i'd do best to manually sort the records and not use the query component altogether because in only need(ed) the query to make an order by statement. I'm not sure but i think that my table does not have an index so i'd be best to forget the whole SQL idea

tnx TealWren, svanels, Elkarie and Nordlund E-mail: Rob@matas.nl (till Jan 10, from then on it's Deathwish@winning.com)

 

[svanels]

TealWren, the order by clause makes the query read only in Paradox?

To RobPouwelse, don't give up that early on queries, try to scale your Database up to Local Interbase.

Regards S. van Els
SAvanEls@cq-link.sr

 

[fmmf]

What about to use a TTable and indexes? I dont know how to explain (because my english is so limited, I could try in portuguese if you want ), but the idea is to set the indexname of the TTable with the name of a index, index that you can create at runtime (see TTable.AddIndex) or at design time (with Database Desktop). I hope it could be useful ...
Fernando Milton
fmmf@dpi.ufv.br