Help to create a Query and Display the results in a dbGrid

[Mikheil 0]

I used to do a lot of programming in Delphi 6. I was a Novell Developer. I'm now retired for quite some time. I was asked to do a bit of work for a friend. His company programmer is off sick and on a pension, I can always use a bit of extra cash!

 

I've done most of the work. I'm using Rad Studio XE7 and MyDac for his database work. I created a database and tables at runtime. I've done all the data entry work, but now he wants to run a simple query and show the results in a dbGrid (or something similar. 

 

To be honest, I'm clueless on this one. At 80, the gray cells don't work as well as they did years ago plus I don't recall ever having to do this.

 

the query is very simple there is only one table involved. the object is to first ask for the query, there are 5 possible fields only one of the queries involves  an OR statement. - Select * from <tablename> where field1 LIKE "%string%" OR field2 LIKE "%string%" The others would involve only one field.

 

It sounds incredibly simple, but it's got me scratching my head. Age is a bad thing!

 

I thought of a dbListbox Display the possible items for the user to select. Create the query, feed this into a MyQuery, execute it and somehow feed the result into a dbGrid.

 

Is there a way to do this. Any help would be most welcome so that I can tie this one up?

Edited April 8, 2021 by Mikheil

[haentschman 92]

Hi...

 

key points:

1. DBGrid -> TDataSource -> TQuery

2. SQL in Query

Query.SQl.Text := 'select Bla from Bubb where Bla = :PAR'
Query.ParamByName('PAR').AsString := 'Test';
Query.Open;

* if you can...dont use LIKE. The database indizies are not used.

* always work with parameters! See: https://en.wikipedia.org/wiki/SQL_injection

 

...finish.

[Mikheil 0]

I'm afraid that you seem to have missed my point.  The query string isn't the problem. I appreciate your comments regarding LIKE, but there's a phrase "The customer is always right".  I'm not writing this for me. He has reasons for the LIKE.

 

DBGrid --> TDatasource -->TQuery  Is the bit that I'm out of my depth with.

 

I create a Form. I drop a DBGrid onto it. I have a DataModule with the TMyConnection TMyTable, TMyCommand, and TDatasource in it.

 

Where / how to I put the TQuery? On the Form or in the DataModule?

Do I need a second DataSource if so where to put it?  How do I link these together at runtime?

 

The next thing is how to allow the user to select which field(s) to query, so that I can construct the SQL?

Should I create a separate mini form for that or can I hold off displaying the DBGrid AFTER the user has made the selection and I've created the SQL string? 

 

Don't get old!

[Serge_G 87]

22 minutes ago, Mikheil said:

Where / how to I put the TQuery? On the Form or in the DataModule?

It's a "business layer" so I should say DataModule and perhaps TDataSource on Form

Agree with haentschman but "Like" can't be "parametrized".

Anyway you have the macro solution https://www.devart.com/mydac/docs/devart.dac.tcustomdadataset.macros.htm

So your query should be like this Select * from <tablename> &where

and the code

and this should respond your second interrogation (you can even use a macro for table name

22 minutes ago, Mikheil said:

The next thing is how to allow the user to select which field(s) to query, so that I can construct the SQL? 

Query.SQl.Text := 'select Bla from Bubb &where'
Query.MacroByName('where').AsString := Format('WHERE FIELD1 LIKE %s AND FIELD2 LIKE %s',[QuotedStr('%'+value1+'%'),QuotedStr('%'+value2+'%')]);
Query.Open;

Edited April 9, 2021 by Serge_G

[haentschman 92]

 

Quote

but "Like" can't be "parametrized".

I dont think so...

 

update Documents set SendTypeUSB = :GES where OriginalFileName like :PAT
...
Qry := CreateQuery;
try
  case SendType of
    sdtFolder: Qry.SQL.Text := GetSQLByName('SEAM_WRITE_SEND_FOLDER_STATE');
  end;
  Qry.ParamByName('GES').AsInteger := Integer(State);
  case ValueType of
    vatFileName:
    begin
      Qry.SQL.Add(GetSQLByName('SEAM_WRITE_SEND_WHERE_FILENAME_LIKE'));
      Qry.ParamByName('PAT').AsString := '%' + ExtractFileName(Value);
    end;

...it is working.

 

Quote

Where / how to I put the TQuery? On the Form or in the DataModule

TQuery + TDatasource on TDataModule. Never on the form!

Edited April 9, 2021 by haentschman

[Mikheil 0]

42 minutes ago, haentschman said:

 

I dont think so...

 

update Documents set SendTypeUSB = :GES where OriginalFileName like :PAT
...
Qry := CreateQuery;
try
  case SendType of
    sdtFolder: Qry.SQL.Text := GetSQLByName('SEAM_WRITE_SEND_FOLDER_STATE');
  end;
  Qry.ParamByName('GES').AsInteger := Integer(State);
  case ValueType of
    vatFileName:
    begin
      Qry.SQL.Add(GetSQLByName('SEAM_WRITE_SEND_WHERE_FILENAME_LIKE'));
      Qry.ParamByName('PAT').AsString := '%' + ExtractFileName(Value);
    end;

...it is working.

 

TQuery + TDatasource on TDataModule. Never on the form!

Please just forget I asked a question. I think I must be on the wrong forum or maybe the wrong planet., because none of the answers bear any resemblance to my question. I'll just forgo the money and tell my friend to wait until his programmer has recovered.

 

I honestly thought that I asked a clearly worded question. I was obviously wrong, so let's just forget I ever asked it.

[haentschman 92]

Quote

Quote

Where / how to I put the TQuery? On the Form or in the DataModule

TQuery + TDatasource on TDataModule. Never on the form!

...it is not an answer of your question?

 

Quote

I think I must be on the wrong forum or maybe the wrong planet., because none of the answers bear any resemblance to my question.

...that's why i'm out.

[Tom Chamberlain 47]

A simple direct answer: (use of a DataModule and Like are optional and up to you)

 

Drop a new TQuery on the form or datamodule.

Drop a TDataSource on the form or datamodule,  set the DataSet property of the TDataSource to the new TQuery.

Drop a TDBGrid on the form, set the DataSource property of the TDBGrid to the TDataSource.

 

Use a sample SQL statement in the TQuery and activate the TQuery, you should get data in your grid at design time.

Right-click the TDBGrid and select Columns Editor.

Right-click inside the columns editor property window and select Add all fields to populate the fields in the grid.

You can delete the columns you don't want to display in the grid, change the order and you can set the column heading in the Title property.

 

Now this is a quick and easy answer that I would try to avoid myself if possible, there are issues with doing this if you have blob fields or large amounts of data.  I would never let the users edit the data this way!  If you have access to some other data-ware components besides the base TDBGrid like TMS, Woll2Woll, Steema or Devart you can get a lot more features/functions out of one of those grids.  Hand loading a string grid is also a good option if you don't want a data-ware grid, this would be my option.

 

[Guest]

What i have done in my last project is to have TQuery (the analog) and it's support components (Transaction, Session at. al) on a TDataModule.

 

When designing controls (layouts, grid, et. al) designtime i connect the components (visual) to a "DataSource" on the form (i call these local ones). Then link the TDataSources DataSet property to the TQuery on the datamodule.

 

At runtime, due to architectural hierarchies i connect the TDataSource (already connected to the component in designtime) to the datamodule. THIS step is only because i do not want my form to "know" about the datamodule (i have it the other way around), my datamodules are created dynamically depending on a lot of factors and i do not want to use the "DFM linking" thing of the IDE because same IDE often breaks those links.

 

If i have Queries that depend on each other i link them together directly in the DataModule. So my design will look a bit strange because there are some TDataSources on the datamodule and a lot of them om the form (actually a frame).

 

So to speak from behind the keyboard; TDataSource is exactly the component that connects your data (TQuery) with your visual controls (TDBEdit, TDBGrid). You can place the TDataSource where it is most convenient for you. You can have several.

 

For me it is very convenient to have the DataSource's that are linked from Visual Controls on the Form or Frame and the ones linking TQueries together on the TDataModule.

 

What to think about here? Certainly when things move in a good direction the number of items will build up. You can create another TDataModule (when needed) and swap the TDataSource's DataSet property to the "new" or alternate TQuery (runtime).

 

At some point you will find that it is not so nice to have the DataModule in the interface uses clause in the form and the form in the uses clause of the datamodule. Moving your TDataSource around can help you to leverage circular dependencies (together with your own events). OOP-wise TDataSource goes down to the "bottom" so most stuff is compatible.

 

HTH,

 

/Dany

 

[Gary 18]

I do something similar to Danny. DBControls and TDataSource on form, Devart components on a TDataModule. I have a unit between them, but to avoid having your form reference the DataModule you can assign them at runtime TForm.TDataSource.DataSet := TDataModule.TMyQuery. 

 

Also it seems that your customer knows his way around a database. You might have a look at http://www.rosinsky.cz/delphi/

 

Thomas has a filter dialog, as well as a Grid properties dialog  that allows the end user to select the grid columns and create filters almost as good as a SQL statement

 

Gary