Sending Parameters from Delphi to Query
I'm trying to send parameters using a funtcion in delphi, to send it to a DBXQuery in my report, this because I created a menu with the name of the tables to sort the report. When I execute the function, the report shows the error Data Type Unknown. Someone can solve this with me?
Comments
So, in the params property is like this:
Where ID indicates the column who contains the ID of the table, obviously.
Is there something wrong ?
MyQuery.SQL.Text := ' SELECT * FROM TABLE ORDER BY ';
And when you know what order should be then use
MyQuery.SQL.Text := MyQuery.SQL.Text + ' 1'
MyQuery.Open;
or any simillar technique [img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> Mick[/img]
Hi Mick.pl! I'm really doing it in some reports. The problem is that I've some queries with inner joins and a popup menu in a dropdown button who selects the column to sort the report. I'd have to write all the sql code again for each button to sort. So use params is better for what I'm doing here. Unless you know how to do this in a way that doesn't need to rewrite all the SQL again.
Thnx
Another assumption about your query (for example MyQuery: TfrxADOQuery) seems like
SELECT * FROM AnyWhere ORDER BY :ColumnName
Again use dynamic query modification, but earlier you must write a small function (I call it ReplaceText).
Having this function you may write a FR script like:
MyQuery.SQL.Text := ReplaceText( MyQuery.SQL.Text, ':ColumnName', StringChosenInaDialog);
MyQuery.Open;
Mick
I'm Sorry Mick, don't know how to do it =/
Let's try to do it togehter [img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> Attach an exmaple of one of your queries that you need to sort according to end user will. I mean - full[/img]select statement. This will help me to give more suggestions.
Mick
I assume your query is build like below (I mean this is a construction ADOQuery.SQL):
SELECT *
FROM Users
ORDER BY :SortParam
Pay attention this ADOQuery.SQL has 3 lines. Numbering from 0 to 2.
Change this query and write
SELECT *
FROM Users
ORDER BY
:SortParam
Now ADOQuery.SQL has 4 lines. Numbering from 0 to 3.
And when your end user finishes the dialog you will know the column name which should be used in Order by clause.
And then write in your script :
ADOQuery.SQL[ 3] := ColumnName
And verify the result with
ShowMessage( ADOQuery.SQL.Text)
to see how your select statement looks now before you open the query.
Mick