Cannot open a non-select statement.

4 messages · 3 people · 9-26-2014 – 9-29-2014

Izac Kruger 2
Rik van Kekem 1
William Ferreira 1

Reply by email Subscribe to this list Your subscription

Read-only archive. Use j and k to move between messages.

Izac Kruger9-26-2014 13:52 UTC

Hi All,
I am new on lazarus,
I got one form on which I performed 2 onClick even on 2 button: The first button its for INSERT the data in the SQLite database, The second button its for CLEAR the information on the form. After I have clicked on the first button to INSERT the data, the operation goes successfully; if I clicked on the second button who is to removed all the data on the fields, then after refield again the information and clicked on the INSERT button, I received a funny message: SQLQeuryLite: Cannot open a non-select statement.
I tried to re-activate/restart the transaction component but still having the same error.
Please see below my codes:
------------------------------------------------FIRST BUTTON----------------------------------------------

else begin
       //DB connections.
    if DataModuleConn.SQLTransSQLite.Active=False then
      begin
       DataModuleConn.SQLite3Conn.Connected:=True;
       DataModuleConn.SQLTransSQLite.Active:=True;
       DataModuleConn.SQLQuerySQLite.Active:=True;
       DataModuleConn.SQLQuerySQLite.SQL.Text:='INSERT INTO loginConnection
(hostname, hostip, dbname, username, password) VALUES ('
                                               +':getHOSTNAME, :getHOSTIP,
:getDBNAME, :getUSERNAME, :getPASSWORD);';
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getHOSTNAME').AsString:=lblEdtHostFQDN.Text;
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getHOSTIP').AsString:=lblEdtHostIP.Text;
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getDBNAME').AsString:=lblEdtDBName.Text;
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getUSERNAME').AsString:=lblEdtDBuserName.Text;
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getPASSWORD').AsString:=lblEdtDBPassword.Text;
       DataModuleConn.SQLQuerySQLite.ExecSQL;
       DataModuleConn.SQLQuerySQLite.SQL.Text:='INSERT INTO textConnection
(hostname, hostip, dbname, username, password) VALUES ('
                                               +':getHOSTNAME, :getHOSTIP,
:getDBNAME, :getUSERNAME, :getPASSWORD);';
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getHOSTNAME').AsString:=lblEdtHostFQDN1.Text;
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getHOSTIP').AsString:=lblEdtHostIP1.Text;
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getDBNAME').AsString:=lblEdtDBName1.Text;
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getUSERNAME').AsString:=lblEdtDBuserName1.Text;
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getPASSWORD').AsString:=lblEdtDBPassword1.Text;
       DataModuleConn.SQLQuerySQLite.ExecSQL;
       DataModuleConn.SQLQuerySQLite.SQL.Text:='INSERT INTO
binaryConnection (hostname, hostip, dbname, username, password) VALUES ('
                                               +':getHOSTNAME, :getHOSTIP,
:getDBNAME, :getUSERNAME, :getPASSWORD);';
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getHOSTNAME').AsString:=lblEdtHostFQDN2.Text;
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getHOSTIP').AsString:=lblEdtHostIP2.Text;
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getDBNAME').AsString:=lblEdtDBName2.Text;
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getUSERNAME').AsString:=lblEdtDBuserName2.Text;
DataModuleConn.SQLQuerySQLite.Params.ParamByName('getPASSWORD').AsString:=lblEdtDBPassword2.Text;
       DataModuleConn.SQLQuerySQLite.ExecSQL;
       ShowMessage(msgSuccessful + lisLineEnding + msgSaved);
       DataModuleConn.SQLTransSQLite.Commit;
       //DataModuleConn.SQLQuerySQLite.Close;
          bitBtnOK.Caption:=liBtnUpdate;
          bitBtnOK.Enabled:=False;
          //DataModuleConn.SQLTransSQLite.StartTransaction;
    end else
       begin
       DataModuleConn.SQLite3Conn.Connected:=False;
       DataModuleConn.SQLTransSQLite.Active:=False;
       DataModuleConn.SQLQuerySQLite.Active:=False;
       end;
  end;

------------------------------------------------SECOND BUTTON-----------------------------------------

begin
   lblEdtHostFQDN.Text:='';
   lblEdtHostIP.Text:='';
   lblEdtDBName.Text:='';
   lblEdtDBuserName.Text:='';
   lblEdtDBPassword.Text:='';
   lblEdtDBPasswordRepeat.Text:='';
   lblEdtHostFQDN1.Text:='';
   lblEdtHostIP1.Text:='';
   lblEdtDBName1.Text:='';
   lblEdtDBuserName1.Text:='';
   lblEdtDBPassword1.Text:='';
   lblEdtDBPasswordRepeat1.Text:='';
   lblEdtHostFQDN2.Text:='';
   lblEdtHostIP2.Text:='';
   lblEdtDBName2.Text:='';
   lblEdtDBuserName2.Text:='';
   lblEdtDBPassword2.Text:='';
   lblEdtDBPasswordRepeat2.Text:='';
   ckBoxShowPassword.Checked:=False;
   ckBoxShowPassword1.Checked:=False;
   ckBoxShowPassword2.Checked:=False;
   ckBoxSameAsLogin.Checked:=False;
   ckBoxSameAsLogin1.Checked:=False;
   ckBoxSameAsTData.Checked:=False;
   lblEdtHostFQDN.SetFocus;
   bitBtnOK.Caption:=lisOK;
   bitBtnOK.Enabled:=True;
   if DataModuleConn.SQLTransSQLite.Active=True then
       DataModuleConn.SQLTransSQLite.Active:=False;
end;

THANKS
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140926/90695689/attachment.html>

Reply by email

William Ferreira9-26-2014 17:07 UTC

to Izac Kruger

For SELECT statements use TSQLQuery.Open;
For INSERT, DELETE or UPDATE, use TSQLQuery.ExecSQL;

Date: Fri, 26 Sep 2014 15:52:07 +0200
From:
To:
Subject: [Lazarus] Cannot open a non-select statement.

Hi All,
I am new on lazarus,
I got one form on which I performed 2 onClick even on 2 button: The first button its for INSERT the data in the SQLite database, The second button its for CLEAR the information on the form. After I have clicked on the first button to INSERT the data, the operation goes successfully; if I clicked on the second button who is to removed all the data on the fields, then after refield again the information and clicked on the INSERT button, I received a funny message: SQLQeuryLite: Cannot open a non-select statement. I tried to re-activate/restart the transaction component but still having the same error. Please see below my codes:
------------------------------------------------FIRST BUTTON----------------------------------------------

else begin
       //DB connections.
    if DataModuleConn.SQLTransSQLite.Active=False then
      begin
       DataModuleConn.SQLite3Conn.Connected:=True;
       DataModuleConn.SQLTransSQLite.Active:=True;
       DataModuleConn.SQLQuerySQLite.Active:=True;
       DataModuleConn.SQLQuerySQLite.SQL.Text:='INSERT INTO loginConnection (hostname, hostip, dbname, username, password) VALUES ('
                                               +':getHOSTNAME, :getHOSTIP, :getDBNAME, :getUSERNAME, :getPASSWORD);';
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getHOSTNAME').AsString:=lblEdtHostFQDN.Text;
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getHOSTIP').AsString:=lblEdtHostIP.Text;
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getDBNAME').AsString:=lblEdtDBName.Text;
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getUSERNAME').AsString:=lblEdtDBuserName.Text;
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getPASSWORD').AsString:=lblEdtDBPassword.Text;
       DataModuleConn.SQLQuerySQLite.ExecSQL;
       DataModuleConn.SQLQuerySQLite.SQL.Text:='INSERT INTO textConnection (hostname, hostip, dbname, username, password) VALUES ('
                                               +':getHOSTNAME, :getHOSTIP, :getDBNAME, :getUSERNAME, :getPASSWORD);';
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getHOSTNAME').AsString:=lblEdtHostFQDN1.Text;
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getHOSTIP').AsString:=lblEdtHostIP1.Text;
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getDBNAME').AsString:=lblEdtDBName1.Text;
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getUSERNAME').AsString:=lblEdtDBuserName1.Text;
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getPASSWORD').AsString:=lblEdtDBPassword1.Text;
       DataModuleConn.SQLQuerySQLite.ExecSQL;
       DataModuleConn.SQLQuerySQLite.SQL.Text:='INSERT INTO binaryConnection (hostname, hostip, dbname, username, password) VALUES ('
                                               +':getHOSTNAME, :getHOSTIP, :getDBNAME, :getUSERNAME, :getPASSWORD);';
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getHOSTNAME').AsString:=lblEdtHostFQDN2.Text;
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getHOSTIP').AsString:=lblEdtHostIP2.Text;
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getDBNAME').AsString:=lblEdtDBName2.Text;
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getUSERNAME').AsString:=lblEdtDBuserName2.Text;
       DataModuleConn.SQLQuerySQLite.Params.ParamByName('getPASSWORD').AsString:=lblEdtDBPassword2.Text;
       DataModuleConn.SQLQuerySQLite.ExecSQL;
       ShowMessage(msgSuccessful + lisLineEnding + msgSaved);
       DataModuleConn.SQLTransSQLite.Commit;
       //DataModuleConn.SQLQuerySQLite.Close;
          bitBtnOK.Caption:=liBtnUpdate;
          bitBtnOK.Enabled:=False;
          //DataModuleConn.SQLTransSQLite.StartTransaction;
    end else
       begin
       DataModuleConn.SQLite3Conn.Connected:=False;
       DataModuleConn.SQLTransSQLite.Active:=False;
       DataModuleConn.SQLQuerySQLite.Active:=False;
       end;
  end;

------------------------------------------------SECOND BUTTON-----------------------------------------

begin
   lblEdtHostFQDN.Text:='';
   lblEdtHostIP.Text:='';
   lblEdtDBName.Text:='';
   lblEdtDBuserName.Text:='';
   lblEdtDBPassword.Text:='';
   lblEdtDBPasswordRepeat.Text:='';
   lblEdtHostFQDN1.Text:='';
   lblEdtHostIP1.Text:='';
   lblEdtDBName1.Text:='';
   lblEdtDBuserName1.Text:='';
   lblEdtDBPassword1.Text:='';
   lblEdtDBPasswordRepeat1.Text:='';
   lblEdtHostFQDN2.Text:='';
   lblEdtHostIP2.Text:='';
   lblEdtDBName2.Text:='';
   lblEdtDBuserName2.Text:='';
   lblEdtDBPassword2.Text:='';
   lblEdtDBPasswordRepeat2.Text:='';
   ckBoxShowPassword.Checked:=False;
   ckBoxShowPassword1.Checked:=False;
   ckBoxShowPassword2.Checked:=False;
   ckBoxSameAsLogin.Checked:=False;
   ckBoxSameAsLogin1.Checked:=False;
   ckBoxSameAsTData.Checked:=False;
   lblEdtHostFQDN.SetFocus;
   bitBtnOK.Caption:=lisOK;
   bitBtnOK.Enabled:=True;
   if DataModuleConn.SQLTransSQLite.Active=True then
       DataModuleConn.SQLTransSQLite.Active:=False;
end;

THANKS

Reply by email

Rik van Kekem9-26-2014 17:09 UTC

to Izac Kruger: I got one form on which I performed 2 onClick even on 2 button: The first button its for…

Izac Kruger wrote
if I clicked on the second button who is to removed all the data on the fields, then after refield again the information and clicked on the INSERT button, I received a funny message: SQLQeuryLite: Cannot open a non-select statement.

...

Show quoted text 11 lines

else begin //DB connections. if DataModuleConn.SQLTransSQLite.Active=False then begin DataModuleConn.SQLite3Conn.Connected:=True; DataModuleConn.SQLTransSQLite.Active:=True; DataModuleConn.SQLQuerySQLite.Active:=True; DataModuleConn.SQLQuerySQLite.SQL.Text:='INSERT INTO loginConnection (hostname, hostip, dbname, username, password) VALUES (' +':getHOSTNAME,

You have a line DataModuleConn.SQLQuerySQLite.Active:=True; Why?
This is usually for opening a dataset with a SELECT-statement. If your SQL is still the INSERT-statement from the previous insert an Active:=True will give you that error.

Actually you should make sure the SQLQuerySQLite is NOT active before
changing the SQL.Text. So if you change that line as follows it should work:
  DataModuleConn.SQLQuerySQLite.Active:=False;
  // ^-- always make sure Active is false before setting SQL.Text

Reply by email

Izac Kruger9-29-2014 15:51 UTC

to Rik van Kekem

Thanks a lot, its works

"Rik van Kekem" wrote 39 lines

Izac Kruger wrote: > if I clicked on the second button who is to > removed all the data on the fields, then after refield again the > information and clicked on the INSERT button, I received a funny > message: SQLQeuryLite: Cannot open a non-select statement. > ... else begin > //DB connections. > if DataModuleConn.SQLTransSQLite.Active=False then > begin > DataModuleConn.SQLite3Conn.Connected:=True; > DataModuleConn.SQLTransSQLite.Active:=True; > DataModuleConn.SQLQuerySQLite.Active:=True; > > DataModuleConn.SQLQuerySQLite.SQL.Text:='INSERT INTO > loginConnection (hostname, hostip, dbname, username, password) VALUES (' > +':getHOSTNAME, > You have a line DataModuleConn.SQLQuerySQLite.Active:=True; Why? This is usually for opening a dataset with a SELECT-statement. If your SQL is still the INSERT-statement from the previous insert an Active:=True will give you that error. Actually you should make sure the SQLQuerySQLite is NOT active before changing the SQL.Text. So if you change that line as follows it should work: DataModuleConn.SQLQuerySQLite.Active:=False; // ^-- always make sure Active is false before setting SQL.Text -- _______________________________________________ Lazarus mailing list http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140929/e54c52dc/attachment-0001.html>

Reply by email

Live chat for Free Pascal, Lazarus, Delphi and every Pascal programmer.

Cannot open a non-select statement.