Forum > Databases

<SOLVED> Syntax for user input value in SQLiteQuery

(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

Go to full version