Forum > Databases
Why can my TQuery perform read operations but cannot successfully execute insert
akjarjash:
project1.lpr
--- 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";}};} ---program project1; {$mode objfpc}{$H+} uses {$IFDEF UNIX} cthreads, {$ENDIF} {$IFDEF HASAMIGA} athreads, {$ENDIF} Interfaces, // this includes the LCL widgetset Forms, unit1 { you can add units after this }; {$R *.res} begin RequireDerivedFormResource:=True; Application.Scaled:=True; Application.Initialize; Application.CreateForm(TForm1, Form1); Application.Run;end.
Unit1.lfm
--- 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";}};} ---object Form1: TForm1 Left = 452 Height = 227 Top = 151 Width = 608 Caption = 'Form1' ClientHeight = 227 ClientWidth = 608 OnCreate = FormCreate object DBGrid1: TDBGrid Left = 16 Height = 208 Top = 8 Width = 400 Color = clWindow Columns = < item Title.Caption = '编号' FieldName = 'id' end item Title.Caption = '姓名' FieldName = 'name' end item Title.Caption = '年龄' FieldName = 'age' end> DataSource = DataSource1 Options = [dgEditing, dgTitles, dgIndicator, dgColumnResize, dgColumnMove, dgColLines, dgRowLines, dgTabs, dgAlwaysShowSelection, dgConfirmDelete, dgCancelOnExit, dgDisplayMemoText] TabOrder = 0 end object Label1: TLabel Left = 432 Height = 17 Top = 15 Width = 36 Caption = '姓名：' end object Label2: TLabel Left = 432 Height = 17 Top = 50 Width = 36 Caption = '年龄：' end object EditUserName: TEdit Left = 486 Height = 25 Top = 11 Width = 98 TabOrder = 1 end object EditUserAge: TEdit Left = 486 Height = 25 Top = 46 Width = 96 TabOrder = 2 end object Button1: TButton Left = 432 Height = 25 Top = 88 Width = 75 Caption = '新增' TabOrder = 3 OnClick = Button1Click end object Button2: TButton Left = 512 Height = 25 Top = 88 Width = 75 Caption = '更新' TabOrder = 4 end object Button3: TButton Left = 432 Height = 25 Top = 128 Width = 75 Caption = '查询' TabOrder = 5 end object Button4: TButton Left = 512 Height = 25 Top = 128 Width = 75 Caption = '编辑' TabOrder = 6 end object SQLite3Connection1: TSQLite3Connection Connected = True LoginPrompt = False DatabaseName = 'C:\Users\akjarjash\Desktop\student.db' KeepConnection = True Transaction = SQLTransaction1 AlwaysUseBigint = False Left = 424 Top = 168 end object SQLQuery1: TSQLQuery IndexName = 'DEFAULT_ORDER' MaxIndexesCount = 4 FieldDefs = < item Name = 'id' DataType = ftAutoInc Precision = -1 end item Name = 'name' DataType = ftMemo Precision = -1 end item Name = 'age' DataType = ftInteger Precision = -1 end> Active = True Database = SQLite3Connection1 Transaction = SQLTransaction1 SQL.Strings = ( 'select' ' *' 'from t_students' ) InsertSQL.Strings = ( 'insert into t_students' ' (name, age' ' )' 'values' ' (:name, :age' ' )' ) UpdateSQL.Strings = ( 'update t_students' 'set' ' name = :name, age = :age' 'where' ' (id = :OLD_id)' ' ' ) DeleteSQL.Strings = ( 'delete from t_students' 'where' ' (id = :OLD_id)' ' ' ) RefreshSQL.Strings = ( 'select' ' *' 'from t_students' 'where' ' (id = :id)' ' ' ) Params = <> Macros = <> Left = 536 Top = 168 end object SQLTransaction1: TSQLTransaction Active = True Database = SQLite3Connection1 Left = 480 Top = 168 end object DataSource1: TDataSource DataSet = SQLQuery1 Left = 592 Top = 168 endend
Unit1.pas
--- 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";}};} ---unit Unit1; {$mode objfpc}{$H+} interface uses Classes, SysUtils, SQLite3Conn, SQLDB, DB, Forms, Controls, Graphics, Dialogs, DBGrids, StdCtrls; type { TForm1 } TForm1 = class(TForm) Button1: TButton; Button2: TButton; Button3: TButton; Button4: TButton; DataSource1: TDataSource; DBGrid1: TDBGrid; EditUserName: TEdit; EditUserAge: TEdit; Label1: TLabel; Label2: TLabel; SQLite3Connection1: TSQLite3Connection; SQLQuery1: TSQLQuery; SQLTransaction1: TSQLTransaction; procedure Button1Click(Sender: TObject); procedure FormCreate(Sender: TObject); private public end; var Form1: TForm1; implementation {$R *.lfm} { TForm1 } procedure TForm1.FormCreate(Sender: TObject);begin // 执行初始化设定工作end; procedure TForm1.Button1Click(Sender: TObject);begin // 检查用户是否输入相关数据 if Trim(self.EditUserName.Text) = '' then begin ShowMessage('学生姓名数据为必填数据!'); self.EditUserName.Text:=''; self.EditUserName.SetFocus; Exit; end; if Trim(self.EditUserAge.Text) = '' then begin ShowMessage('学生姓名数据为必填数据!'); self.EditUserAge.Text:=''; self.EditUserAge.SetFocus; Exit; end; // 检查数据库连接对象 if not self.SQLite3Connection1.Connected then begin self.SQLite3Connection1.Open; end; // 检查数据库事务 if not self.SQLTransaction1.Active then begin self.SQLTransaction1.StartTransaction; end; // 进行新增操作前异常处理 try // 检查查询对象的激活状态 if not self.SQLQuery1.Active then begin self.SQLQuery1.Active:=True; end; // 设置数据库新增标记 self.SQLQuery1.Append; // 设置插入语句参数 self.SQLQuery1.FieldByName('name').AsString:=Trim(self.EditUserName.Text); self.SQLQuery1.FieldByName('age').AsInteger:=StrToInt(self.EditUserAge.Text); // 执行新增操作 self.SQLQuery1.Post; // 进行事务提交 self.SQLTransaction1.Commit; // 提示用户信息 ShowMessage('恭喜，新增成功!'); // 处理编辑框文本 self.EditUserName.Text:=''; self.EditUserAge.Text:=''; // 刷新数据源参数 self.SQLQuery1.Close; self.SQLQuery1.Active:=True; self.SQLQuery1.Open; self.DataSource1.DataSet.Refresh; except on E:Exception do begin self.SQLTransaction1.Rollback; ShowMessage('抱歉，系统异常('+E.Message+')，新增失败!'); end; end; end;end.
Zvoni:
"ApplyUpdates" missing? resp. sqoAutoApplyUpdates not set in TSQLQuery.Options?
akjarjash:
I tested it again, and it still doesn't work. Could you provide me with a complete example, similar to the one in the attachment interface, for performing CRUD operations on an SQLite3 database table using buttons?
To facilitate the work, you can use the following prompts through ChatGPT (PS: Although the code generated by ChatGPT in my tests achieves the functionality, I would like to have some template code to speed up the process):
Please provide a complete Lazarus code example that meets the following requirements:
01) Please complete a code example that performs CRUD operations on TDBGrid (Student Basic Information Management System). You can add any controls as needed, and the database should use sqlite3. The database file path is: /Users/akjarjash/Downloads/student.db. The database has a table called t_students with fields: id (auto-increment), name (text), age (integer).
02) The code example files you should output include: `project1.lpr`, `Unit1.lfm`, `Unit1.pas`.
03) Do not omit any file or any content of any file.
04) Note: You need to provide the actual content of the Unit1.lfm file.
05) Note: Avoid referencing the Windows unit in the 'uses' clause of the instance (this is important).
06) Note: In the given Unit1.lfm file, for the Form window, you only need to set the following properties and the events you need:
```
Left = 249
Height = 240
Top = 147
Width = 320
Caption = 'Form1'
ClientHeight = 240
ClientWidth = 320
LCLVersion = '3.99.0.0'
```
Handoko:
For adding new record you do not have you use TQuery, I usually use ExecuteDirect. You can see my demo:
https://forum.lazarus.freepascal.org/index.php/topic,65185.msg496461.html#msg496461
Zvoni:
--- Quote from: akjarjash on November 21, 2023, 08:57:33 am ---I tested it again, and it still doesn't work. Could you provide me with a complete example, similar to the one in the attachment interface, for performing CRUD operations on an SQLite3 database table using buttons?
--- End quote ---
I'm the wrong person to ask that.
I don't use "Append", "Post" and whatever else.
I use SQL-Statements. Period
Navigation
[0] Message Index
[#] Next page