Simple TQuery help?

[cold25]

I'm trying to make a TQuery work that I'm using parameters in. I think I'm close, but so far it's
never worked yet. Below is the procedure I'm using. I've made it from examples I've seen on
this web site. My database table is named 1nin.dbf. I'm trying to search a field in the table
named EXS for a value of 33. Could someone please take a look at this and see what I'm
doing wrong? Thanks in advance.

cold25


procedure TForm1.TQuery1;
var
tname, value: string;
SqlString: string;

begin
tname := '"1nin.dbf"'; value := '33';
SqlString := Concat('SELECT * FROM ' + tname + 'WHERE EXS = ' + value);
Query1.Close; Query1.SQL.Clear;
Query1.SQL.Add(SqlString);

//I don't understand what should go where the two question marks are below,
//or if any other commands are needed after this.
Query1.ParamByName('?').AsString := ?;

end;

 

[Tracey]

you only need the parambyname line if you say
'select * from table where fieldvlaue = :fieldvalue'

then u would say
query1.parambyname('fieldvalue') := value;

because u are contatinating the query params into the actual sql, the query doesnt actually have any parameters.

 

[towerbase]

You do not say what kind of error you get when you try your code. There are at least three things wrong with your procedure as it stands.

1. There should be a space character before the WHERE otherwise it will become part of the table name.

2. You should have a Query1.Open statement to actually obtain the result set.

3. And as Tracey points out, as your code stands, you don't need to use Query Params.

Of course, these may be typos in transferring your code to this forum. But it would help if you told us why you think the code has never worked yet.

Andrew

 

[LucieLastic]

Try

SqlString := Concat('SELECT * FROM ' + tname + ' WHERE EXS = ' + QuotedStr(value));

 

[petertickler]

Is the EXS field an Integer or string field? If a string field, then you must pass the value as a string - i.e. within quotation marks.

 

[lespaul]

cold25,
I can completely understand your confusion. I only recently started using Delphi and it takes a while to get a small clue!

If you have placed a TQuery object on your form, one of the properties in the Object Inspector is SQL. If the following is what you have in the SQL property:

SELECT * FROM 1nin.dbf WHERE EXS = :VALUE

Then in your code you would do:

Query1.ParamByName('VALUE').AsString := '33';

normally you would have it set equal to a variable that changes.

The way you have it first (putting the variables into the SQL statement directly) works as well, but then you don't need the parameters.

Then you need to do either:
Query1.Active := True;

or

Query1.Open;




Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[cold25]

Thank you all for your help. I'm working with these suggestions right now, so hopefully it will work very soon. Thanks!

cold25

 

[cold25]

The block of code below raises the following exception for me:
Project Project1.exe raised exception class EDatabaseError with message 'Table1: Field 'SearchParam' not found'. Process stopped. Use Step or Run to continue.

Any thoughts on why :SearchParam is being read as the field instead of the value I enter into Edit1.Text?
:SearchParam is supposed to supply the value that the query searches for.
Thanks in advance,

cold25



Procedure TForm1.TQuery1;
var
stmtParams: TParams;
begin
searchField := Edit1.Text; searchTopic := Edit5.Text;
currentTable := Concat( '"' + Table1.TableName + '"');
subString1 := Concat(' FROM ', currentTable);

// create the TParams object
stmtParams := TParams.Create;
try
// activate the TDatabase component
Database1.Connected := True;
// create the TParam object for SearchParam
stmtParams.CreateParam(ftString, 'SearchParam', ptInput);
// assign user input as the data value for the SearchParam parameter
stmtParams[0].AsString := searchTopic;

SQLstmt := 'SELECT *' +
subString1 +
Format ( ' WHERE (%s = :SearchParam) ', [searchField] );

//***********************************************
//Put in the statements to launch the SQLstmt at this point.
Query1.Close; Query1.SQL.Clear;
Query1.SQL.Add(SQLstmt);
Query1.Prepare;

Query1.Open;
//***********************************************

finally
//free the dynamically created TParams object
stmtParams.Free;
end;
end;

 

[LucieLastic]

cold25, this looks complicated, I'd be tempted to simplify it all to something like :-

Procedure TForm1.TQuery1;
begin
try
// activate the TDatabase component
Database1.Connected := True;
// assign user input as the data value for the

SQLstmt := 'SELECT * ' +
QuotedStr(Table1.TableName) +
' WHERE '+Edit5.Text = '+quotedStr(edit1.text);

//***********************************************
//Put in the statements to launch the SQLstmt at this point.
Query1.Close; Query1.SQL.Clear;
Query1.SQL.Add(SQLstmt);
Query1.Prepare;

Query1.Open;
//***********************************************

finally

end;
end;

 

[towerbase]

weez may have simplified it a little too much. The select statement should read

SQLstmt := 'SELECT * FROM ' +

Andrew

 

[LucieLastic]

Thankyou Andrew, a slight typo on my part. I did have his variable in there (CurrentTable) first then replaced it and so lost the 'FROM' bit.

lou