Parameters in SQL statements???

[cold25]

I have modified the following example block of code so a user can specify what
city is searched for though the variable "searchCity." My question is how can I replace
some of the other things in the SQL statement with other parameters? For example, I have included the
lines inside the ****'s below in an attempt to replace "State" in the WHERE clause
with the parameter "FieldToSearch", so the user could choose another field other than
State to search for a city in, such as "Country." My attempts below yeild blank columns.

I've also tried using: stmtParams.ParamByName('FieldToSearch').AsString := searchField;
in place of: stmtParams[1].AsString := searchField;

I'm not sure what I'm doing wrong, but I'm thinking I've got to be close. How can I get this to work?
Any help would be greatly appreciated.

procedure TDataForm.SELECT_WithParamsButtonClick(Sender: TObject);
var
SQLstmt: String;
stmtParams: TParams;
Cursor: hDBICur;
begin
searchCity := Edit1.Text; //Value entered by user
searchField := Edit5.Text; //Value entered by user

stmtParams := TParams.Create;
try
Database1.Connected := True;
stmtParams.CreateParam(ftString, 'CityParam', ptInput);
stmtParams[0].AsString := searchCity;

//**********
stmtParams.CreateParam(ftString, 'FieldToSearch', ptInput);
stmtParams[1].AsString := searchField;
//**********

SQLstmt := 'SELECT Company, City '+
'FROM "Customer.db" ' +
// 'WHERE (State = :CityParam) ' + //This is the original line
'WHERE FieldToSearch = :CityParam) ' + //This is what I've been trying.
'ORDER BY City, Company';
Database1.Execute(SQLstmt, stmtParams, False, @Cursor);
Table1.Close;
TDBDataSet(Table1).Handle := Cursor;
finally
stmtParams.Free;
end;
end;

 

[towerbase]

FieldToSearch is not a parameter and hence cannot be substituted using the Params mechanism.

A quick fix is to replace the WHERE line with something like

Format ( 'WHERE %s = :CityParam)', [ searchField ] ) +

Andrew

 

[cold25]

Andrew,
Thank you for your help. My SQL knowledge is minimal and the quick fix you suggested has helped alot. I have another question for you, if you wouldn't mind. I'm using a TTable, a TDatabase & a TDBGrid to display .dbf table data. I had read, and now have seen, that although I can use SQL in Delphi successfully to select certain records and fields, they are displayed in a read-only format, which I cannot edit. I am currently trying to figure out what I can accomplish with the BDE component TUpdateSQL. What database components might you suggest that would allow me to edit and save my changes to the records I've selected, thus helping me overcome the read-only problem?

Thanks,

cold25

 

[lespaul]

The Data Control Tab has components that are tied to the fields in the query. If you update the field on the form then the data is updated into the database when the query posts. The post happens automatically when another record is selected and just to be safe I usually check if the dataset is in edit mode, post the data.

Check out How to use Database Look Up fields in the FAQ area of this forum or FAQ102-1212. Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[towerbase]

Forgive me if I'm wrong but I think that Leslie is confusing TQuery and TTable components.

As I understand it, cold25 wishes to update some of the records from a result set produced by a TQuery which are by default readonly.

Uisng a TUpdateSQL component is probably the simplest way. Remember to set the CachedUpdates property to True and the ReadOnly property to False in the TQuery.

Answering your specific question, I think that you can use any of DataAccess components. They will all link to your TQuery component through a TDataSource component and will be unaware whether the data comes from TQuery or TTable or whatever.

Having said all this, I have never got TUpdateSQL to work with a dBASE (III) table. But identical code seems to work okay with Paradox. Perhaps someone else has suceeded?

Andrew