How to insert integer value into SQLite table at Delphi

Question

I am trying to insert integer value in my SQLite table at Delphi.
In table emp usergroup_id is integer and label, description are string data type.
My code is as follows:

var
  gid: Integer;
  sdescription,ldescription: String;
begin
  sdescription := RzEdit1.Text;
  ldescription := RzMemo1.Text;
  gid := Integer(RzComboBox1.Items.Objects[RzComboBox1.Items.IndexOf(gname)]);

  try
    SQLConnection1.Connected := true;
    SQLMonitor1.Active := True;
    sSql := 'INSERT INTO emp(usergroup_id, label, description) VALUES (gid,''' + sdescription + ''',''' + ldescription + ''' )';
    SQLConnection1.ExecuteDirect(sSql);

  except
    on E: EDatabaseError do
      ShowMessage('Exception raised with message' + E.Message);
  end;
end;

It is giving me an error as Unknown column gid.
When I tried something like this with fixed integer value instead of variable it works:

sSql := 'INSERT INTO emp(usergroup_id, label, description) VALUES (1,''' + sdescription + ''',''' + ldescription + ''' )';

It inserts values successfully into table.
How to insert integer value of gid into database with above query. What would be the proper format?

kobik · Accepted Answer · 2013-10-07 11:56:15Z

6

Your gid becomes a part of the SQL statement (hence the Error: Unknown column gid).
You need to use the Delphi gid variable to construct the SQL statement (just like you did with sdescription and ldescription):

sSql := 'INSERT INTO emp(usergroup_id, label, description) VALUES (' + InttoStr(gid) + ', ''' + sdescription + ''',''' + ldescription + ''' )';

If You would have used Parameters you wouldn't have such a messy query/code (which is also subject to SQL injection, etc..) e.g.:

qry := TSQLQuery.Create(nil); // or what ever TQuery component you use in your framework
try
  qry.SQLConnection := SQLConnection1;
  qry.SQL.Text := 'INSERT INTO emp(usergroup_id, label, description) VALUES (:usergroup_id, :label, :description)';
  qry.Params.ParamByName('usergroup_id').Value := gid;
  qry.Params.ParamByName('label').Value := sdescription;
  qry.Params.ParamByName('description').Value := ldescription;
  qry.ExecSQL;
finally
  qry.Free;
end;

edited Oct 7, 2013 at 11:56

answered Oct 7, 2013 at 9:23

kobik

21.3k5 gold badges69 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ninad Avasare Over a year ago

Bonus code & detailed answer helped, however getting error like parameter usergroup_id not found, same with label and description. I am using TSQLQuery descendant of TQuery and modified code as follows qry.Params.ParamByName('usergroup_id').Value := gid;

whosrdaddy Over a year ago

As always, +1 for parameters ;)

Collectives™ on Stack Overflow

How to insert integer value into SQLite table at Delphi

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related