<SOLVED> Syntax for user input value in SQLiteQuery

Forum > Databases

(1/1)

Bert_Plitt:
Hello, I need help with SQLite query syntax. I wrote a demo project to show how to find the nearest of an array of ten points to a point entered by the user. The ten points are included in a table named POINTS.DB, which has four columns: IDX, NAME, XCOORD, YCOORD. The user point is input by two TEdit controls: named edtXCoord and edtYCoord. When a search button is clicked, I want to display the point in the table nearest to the input point.

The following is the code I tried to do the search:

procedure TForm1.sbSearchClick(Sender: TObject);
var
xVal, yVal: Extended;
begin
xVal:=StrToFloat(edtXCoord.Text);
yVal:=StrToFloat(edtYCoord.Text);
qryPoints.Close;
qryPoints.SQL.Text :=
'select * FROM Points order by '+
'((xVal-XCoord)*(xVal-XCoord) + (yVal-YCoord)*(yVal-YCoord)) LIMIT 1';
qryPoints.Open;
end;

This code produces an error: 'no such column: xVal'

My question is what's the code needed to get the values of xVal and yVal included in the Query?

dseligo:

--- Quote from: Bert_Plitt on June 13, 2022, 09:37:16 pm ---My question is what's the code needed to get the values of xVal and yVal included in the Query?

--- End quote ---

You need parameters in query (they are preceded with colon sign):

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---procedure TForm1.sbSearchClick(Sender: TObject);var xVal, yVal: Extended;begin xVal:=StrToFloat(edtXCoord.Text); yVal:=StrToFloat(edtYCoord.Text); qryPoints.Close; qryPoints.SQL.Text := 'select * FROM Points order by '+ '((:xVal-XCoord)*(:xVal-XCoord) + (:yVal-YCoord)*(:yVal-YCoord)) LIMIT 1'; qryPoints.ParamByName('xVal').AsFloat := xVal; qryPoints.ParamByName('yVal').AsFloat := yVal; qryPoints.Open;end;

Bert_Plitt:
Thanks dseligo, works great. I figured it was something simple!

Navigation

[0] Message Index