Recent

Author Topic: Setting properties ofdatabase components such as TSQLQuery at runtime  (Read 6282 times)

georges

  • New Member
  • *
  • Posts: 10
Hi

I'm new to Lazarus and learning how to set up the connectivity a Microsoft SQL Server 2008 R2 database.  I want to be able to set the component properties at run time.  At the moment, I'm setting the values manually in the code but eventually will be getting the values from TEdit component.

I have written the following code which compiles without errors.  However, the DBGrid displays with no columns.  What am I doing wrong?

var
   v_mssqlconnection: TMSSQLConnection;
   v_sqltransaction: TSQLTransaction;
   v_sqlquery: TSQLQuery;
   v_string: string;
   v_datasource: TDataSource;
   v_dbgrid: TDBGrid;

begin
   v_mssqlconnection:= TMSSQLConnection.Create(nil);
   v_mssqlconnection.HostName := 'e6530-01-vm03';
   v_mssqlconnection.DatabaseName := 'TESTDB';
   v_mssqlconnection.UserName := 'sa';
   v_mssqlconnection.Password := 'super';
   v_mssqlconnection.Connected := true;

   v_sqltransaction:= TSQLTransaction.Create(nil);
   v_sqltransaction.Database := v_mssqlconnection;
   v_sqltransaction.Active := true;

   v_sqlquery:= TSQLQuery.Create(nil);
   v_sqlquery.Database:= v_mssqlconnection;
   v_sqlquery.SQL.Text:= 'select ITEM_NO from IM_ITEM';
   v_sqlquery.Open;

   v_datasource:= TDataSource.Create(nil);
   v_datasource.DataSet := v_sqlquery;

   v_dbgrid:= TDBGrid.Create(nil);
   v_dbgrid.DataSource := v_datasource;

end. 

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Setting properties ofdatabase components such as TSQLQuery at runtime
« Reply #1 on: October 02, 2014, 08:51:10 am »
You don't set a visual parent for your dbgrid. Your grid shouldn't even be visible at that point. Are you sure you haven't defined a dbgrid at design time? If so... remove it and add a line below tdbgrid.create:
Code: [Select]
DbGrid.parent:=self;
You also might want to set the dimensions of the dbgrid to something other then the default.

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Setting properties ofdatabase components such as TSQLQuery at runtime
« Reply #2 on: October 02, 2014, 08:52:18 am »
Welcome to this form.

Quote
I have written the following code which compiles without errors.  However, the DBGrid displays with no columns.  What am I doing wrong?
There will be no DBGrid on form at all. If you use GUI components @runtime, give the parent to your form.
Example, your form is form1:
Code: [Select]
   v_dbgrid:= TDBGrid.Create(form1);
   v_dbgrid.DataSource := v_datasource;
   v_dbgrid.Align := alClient;
   v_dbgrid.Parent := form1;
This works like a charm.

Don't forget to release your datacomponents. If you use TMSSQLConnection.Create(form1), the form will clean all related components on the form after closing it.

You say to start a beginner. Why creating GUI components @runtime and not @designtime? Are there reasons?
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

georges

  • New Member
  • *
  • Posts: 10
Re: Setting properties ofdatabase components such as TSQLQuery at runtime
« Reply #3 on: October 03, 2014, 04:07:38 am »
Hi rvk

Thanks for the assistance.  I followed your advice and replaced the following lines

   v_dbgrid:= TDBGrid.Create(nil);
   v_dbgrid.DataSource := v_datasource;

with these

   v_dbgrid:= TDBGrid.Create(nil);
   v_dbgrid.DataSource := v_datasource;
   v_dbgrid.parent:=self;

but I get the error " Error: Identifier not found "self" ".   Can you give me a bit more guidance as to what I may be doing wrong?


georges

  • New Member
  • *
  • Posts: 10
Re: Setting properties ofdatabase components such as TSQLQuery at runtime
« Reply #4 on: October 03, 2014, 04:08:24 am »
Hi mangakissa

Thanks for the greeting.  I followed your advice and made the following changes to my previously posted code:

change #1
---------
   original code
   -------------   
   v_mssqlconnection:= TMSSQLConnection.Create(nil);

   new code
   -------------   
   v_mssqlconnection:= TMSSQLConnection.Create(form1);


change #2
---------
   original code
   -------------   
   v_dbgrid:= TDBGrid.Create(nil);
   v_dbgrid.DataSource := v_datasource;


   new code
   -------------   
   v_dbgrid:= TDBGrid.Create(form1);
   v_dbgrid.DataSource := v_datasource;
   v_dbgrid.Align := alClient;
   v_dbgrid.Parent := form1;


The code compiles without any errors.  It also displays the Grid but it is empty.  I have attached a picture of what it is doing.

As to the reason why I am creating GUI components at runtme and not designtime, it is because I want to have the flexibility be able to set the following properties at runtime and not hard coded:


   TMSSQLConnectionHostName
      TMSSQLConnectionDatabaseName
      TMSSQLConnectionUserName
      TMSSQLConnection.Password

It may not be absolutely necessary now but I want to develop my skills for more challenging stuff later.

 

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Setting properties ofdatabase components such as TSQLQuery at runtime
« Reply #5 on: October 03, 2014, 05:07:01 am »
It is indeed not absolutely necessary.

There is absolutely no need to create controls dynamically just to be able to set properties at runtime. You can set properties at runtime for controls dropped on a form just as well.
See http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1 and following...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Setting properties ofdatabase components such as TSQLQuery at runtime
« Reply #6 on: October 03, 2014, 06:27:53 am »
I have attached a picture of what it is doing.
There was no picture attached.
Could you also show your complete form-unit code?
We need to know where this code was executed in the source.

You say "self" was an unknown identifier so this code is apparently not in the formcreate of your form (where this is usually done). (We need to see that code too)

georges

  • New Member
  • *
  • Posts: 10
Re: Setting properties ofdatabase components such as TSQLQuery at runtime
« Reply #7 on: October 03, 2014, 06:59:12 am »
Hello BigChimp

I did try to set the properties at runtime for controls dropped on a form but I kept getting errors.   However, after your reply I tried again and saw what I was doing wrong.   I was placing the code to set the properties between the begin and end of the unit when I should have placed it in the FormCreate procedure.   Now it works. 

Thanks a lot.

BTW rvk, I also see your post inferred that as well.  Thank you as well. 

u2o

  • Jr. Member
  • **
  • Posts: 72
  • No message
Re: Setting properties ofdatabase components such as TSQLQuery at runtime
« Reply #8 on: October 21, 2014, 03:36:19 pm »
Hi!

@georges: I'm working (in the last year) in a application to manage loans of books in a library.

To simplify all SQlite3 (runtime) code refering to: open/close/query, etc...  I use the Modularization.

Create a unit named unitdatabases.pas (not a form, a unit)

Code: [Select]
unit UnitDatabases;

interface

uses
  Sqlite3DS,
  SQLite3Conn,
  sqldb,
  DBCtrls,
  DB,
  Classes, SysUtils, ExtCtrls;

type
  {TDatabases}

  TDatabases = class
  public
    class procedure DatabaseOpen(Sqlite3Dataset: TSqlite3Dataset; DataSource: TDataSource; DBNavigator: TDBNavigator; sDBFileName: string; sDBTableName: string; sDBPrimaryKey: string); overload;
    class procedure DatabaseClose(Sqlite3Dataset: TSqlite3Dataset); overload;
    class procedure DatabaseOpen(SQLite3Connection: TSQLite3Connection; SQLTransaction: TSQLTransaction; DataSource: TDataSource; SQLQuery: TSQLQuery; sDBFileName: string); overload;
    class procedure DatabaseClose(SQLite3Connection: TSQLite3Connection; SQLTransaction: TSQLTransaction; DataSource: TDataSource; SQLQuery: TSQLQuery); overload;
    class procedure DatabaseOpenQuery(Sqlite3Dataset: TSqlite3Dataset; DataSource: TDataSource; DBNavigator: TDBNavigator; sDBFileName: string; sDBTableName: string; sDBPrimaryKey: string; sQuery: string);
  end;

implementation

class procedure TDatabases.DatabaseOpen(Sqlite3Dataset: TSqlite3Dataset; DataSource: TDataSource; DBNavigator: TDBNavigator; sDBFileName: string; sDBTableName: string; sDBPrimaryKey: string); overload;
begin
  TDatabases.DatabaseClose(Sqlite3Dataset);
  Sqlite3Dataset.FileName := sDBFileName;
  Sqlite3Dataset.TableName := sDBTableName;
  Sqlite3Dataset.PrimaryKey := sDBPrimaryKey;

  DataSource.DataSet := Sqlite3Dataset;
  if not (DBNavigator = nil) then DBNavigator.DataSource := DataSource;
  Sqlite3Dataset.Open;

  Sqlite3Dataset.Active := True;
  Sqlite3Dataset.SaveOnClose := True;
  Sqlite3Dataset.SaveOnRefetch := True;
end;

class procedure TDatabases.DatabaseClose(Sqlite3Dataset: TSqlite3Dataset); overload;
begin
  Sqlite3Dataset.Active := False;
  Sqlite3Dataset.Close;
end;

class procedure TDatabases.DatabaseOpen(SQLite3Connection: TSQLite3Connection; SQLTransaction: TSQLTransaction; DataSource: TDataSource; SQLQuery: TSQLQuery; sDBFileName: string); overload;
begin
  SQLite3Connection.DatabaseName := sDBFileName;
  SQLite3Connection.Connected := True;
  SQLTransaction.Database := SQLite3Connection;
  SQLQuery.Database := SQLite3Connection;
  DataSource.DataSet := SQLQuery;
end;

class procedure TDatabases.DatabaseClose(SQLite3Connection: TSQLite3Connection; SQLTransaction: TSQLTransaction; DataSource: TDataSource; SQLQuery: TSQLQuery); overload;
begin
  SQLQuery.Close;
  SQLTransaction.CloseDataSets;
  SQLTransaction.Active := False;
  SQLite3Connection.CloseDataSets;
  SQLite3Connection.CloseTransactions;
  SQLite3Connection.Close;
end;


class procedure TDatabases.DatabaseOpenQuery(Sqlite3Dataset: TSqlite3Dataset; DataSource: TDataSource; DBNavigator: TDBNavigator; sDBFileName: string; sDBTableName: string; sDBPrimaryKey: string; sQuery: string);
begin
  TDatabases.DatabaseClose(Sqlite3Dataset);
  Sqlite3Dataset.FileName := sDBFileName;
  Sqlite3Dataset.TableName := sDBTableName;
  Sqlite3Dataset.PrimaryKey := sDBPrimaryKey;
  DataSource.DataSet := Sqlite3Dataset;
  Sqlite3Dataset.SQL := sQuery;

  //  DBGrid1.DataSource := DataSource;
  if not (DBNavigator = nil) then DBNavigator.DataSource := DataSource;
  Sqlite3Dataset.ExecSQL;
  Sqlite3Dataset.Open;

  Sqlite3Dataset.Active := True;
  Sqlite3Dataset.SaveOnClose := True;
  Sqlite3Dataset.SaveOnRefetch := True;
end;


end.

After in your Form, create the visual components, but no edit any properties...  The code does it for you...

The followed code has default names of visual controls, you can change if you need.

Code: [Select]
unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  UnitDatabases,
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs;

type
  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Datasource1: TDatasource;
    DBEdit1: TDBEdit;
    DBEdit2: TDBEdit;
    DBEdit3: TDBEdit;
    DBNavigator1: TDBNavigator;
    Sqlite3Dataset1: TSqlite3Dataset; 
    procedure Button1Click(Sender: TObject); 
    procedure Button2Click(Sender: TObject); 
    procedure FormCreate(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

procedure TfrmMaterialesActualizacion.FormCreate(Sender: TObject);
begin
  DBEdit1.DataField := 'ID_BOOK';
  DBEdit2.DataField := 'NAME';
  DBEdit3.DataField := 'AUTHOR';
  Edit1.MaxLength := 10;
  Edit2.MaxLength := 80;
  Edit3.MaxLength := 80
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  sDBPath, sTableName, sTableIdx, sQuery : String;
begin
  sDBPath := 'Path\To\Your\Database.db';
  sTableName :=  'books';
  sTableIdx := 'id';
  sQuery := 'SELECT * FROM ' + sTableName + ' WHERE ACTIVE="1" ';

  TDatabases.DatabaseOpenQuery(Sqlite3Dataset1, DataSource1, DBNavigator1, sDBPath , sTableName,  sTableIdx, sQuery);
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  TDatabases.DatabaseClose(Sqlite3Dataset1);
end;         


end.

No really functional Sample (need to add the visual controls), but use your imagination.

Now you can joke with the code.

if you do not understand something, just ask.

Regards!

P/d: Maybe tomorrow I can complete a functional example to compile, for now it's all...

-- edit ------------------------------------

If you create the Conection Controls manually, you need all units required by that Controls added into  the form in Uses clause. Some controls need add Components to the project, so first add the visual control, then delete it from the visual interfase of the Form and finally, manage as you need the runtime creation of the Conection Controls.

When you know exactly needed code, modularize all.
« Last Edit: October 21, 2014, 03:56:10 pm by u2o »

 

TinyPortal © 2005-2018