Are there any friendly grid controls?

[BigChimp]

Fine, Nebula, it's just that you never told us you were going to switch from data-bound to manual

That makes it look like our advice is being ignored - in my case, sure to raise blood pressure and increase the chances of my keyboard being damaged - never a good thing

Not being a grid guru myself, I don't have anything to suggest to your latest questions except:
1. Often it's a good idea to start a new thread for a new question. Helps to clarify the issues, and helps attract experts that are intrigued by the subject line.
2. Perhaps redundantly, but well... it's one of my hobby horses. If you or anybody finds out things about grids, please help by documenting it on the wiki That'll save me and others a lot of time when tyring to solve what's been figured out before.

[Nebula]

No worries, always good humour here 
- and happy to contribute to the wiki as and when I've anything to add.

In fairness though, I thought I'd made it clear that it was going to be a manually fetched SQL rowset to be displayed in the grid - i.e. reply #5 : "For instance, in vb.net I can connect to an MS-SQL server and show a SQL query results table as easily as this:"  + (code snippet)

I've never used data-aware controls, and need to seek out a good tutorial to sell me the idea. In my line of work we always grabbed relevant data to the form in question, and  allowed edit/insertion 'the hard way' by populating controls on the form and then grabbing from them again if an update was required. We weren't lazy about it 

Cheers

[BigChimp]

No worries, always good humour here 
- and happy to contribute to the wiki as and when I've anything to add.

Thanks for your earlier work on odbc, much appreciated.

In fairness though, I thought I'd made it clear that it was going to be a manually fetched SQL rowset to be displayed in the grid - i.e. reply #5 : "For instance, in vb.net I can connect to an MS-SQL server and show a SQL query results table as easily as this:"  + (code snippet)

Mm yes, AFAIR, the SQL is retrieved manually in your example, but you still have a databound grid in that .Net example snippet? Could be mistaken, of course, it's been a while since I've done anything with .Net

I've never used data-aware controls, and need to seek out a good tutorial to sell me the idea.

SQLDB Tutorial1 and 2 on the wiki Improvements/comments welcome - feels like I've been editing those tutorials for ages (didn't write them though).

Thanks

[Nebula]

(btw Column width 'auto-sizing' turned out to be easy

Size the columns to maximum contents :

Code: [Select]

  stringgrid1.AutoSizeColumns;
Size to width of column titles :

Code: [Select]

  for Col := 0 to (stringgrid1.ColCount - 1) do
    stringgrid1.ColWidths[Col] := stringgrid1.Canvas.TextWidth(stringgrid1.Cells[Col, 0]) + 10;

[Nebula]

Are there two ways to use grids?
I've been adding data to specific cells via loops, like this
stringgrid1.Cells[Col,Lin+stringgrid1.FixedRows] := query.Fields[Col].AsString;

Yet it appears that, at design time, there is a way to define columns, and there's an alignment field there for each column?

And this compiled for me
  stringgrid1.Columns.Items[0].Alignment:=taRightJustify;
- but it gave an error because I didn't have any columns set up, despite the grid cells being filled in, with what looks like columns to me 

Would there be a better way for me to use grids - add columns in my code,
stringgrid1.Columns.Add
- then fill in from my query somehow, and be able to set the alignment that way?

I could get around the formatting issue by setting the format in my SQL, returning the values as strings, and another column (to be hidden) as the actual numerical value if needed.

The stringgrid1.Columns.Items have .maxsize and .visible properties too, looks like what I need. Trouble is, I never was all that great at figuring out other people's object models - that's why I depend upon helpful forum and published code examples 

[Nebula]

Aha, got it sorted! Hooray 
Add the column details first, then loop around the query filling in the cells as before....

Code: [Select]

{ step 1 : set up columns, from query dataset }
stringgrid1.RowCount := query.RecordCount+1; //  +1 for Titles
stringgrid1.FixedCols := 0; // everything moves left/right when scrolling
stringgrid1.FixedRows := 1; // titles stay put when scrolling up/down
For Col := 0 To query.FieldCount-1 Do
  begin
    stringgrid1.Columns.Add;
    stringgrid1.Columns[col].Title.caption := query.Fields[Col].FieldName;
    { Right justify numerical fields }
    If ord(query.fields[col].DataType) in [3,6,14] then // TFieldTypes: ftInteger,ftFloat,ftAutoInc
      begin
        stringgrid1.Columns[col].Alignment := taRightJustify;
        stringgrid1.Columns[col].Title.Alignment:=taRightJustify;
      end;
  end;

{ step 2, adjust specific colums, justification or visible }
stringgrid1.Columns[6].Visible:=false;    // hide col 7

{ step 3, copy in query results }
Lin := 0;
While Not query.Eof Do
  Begin
    For Col := 0 To query.FieldCount-1 Do
    If ord(query.fields[col].DataType) in [6] then // format numbers with commas
      stringgrid1.Cells[Col,Lin+stringgrid1.FixedRows] := FormatFloat('#,##0.00;(#,##0.00);-',query.Fields[Col].AsFloat)
    else  // use string as is :
      stringgrid1.Cells[Col,Lin+stringgrid1.FixedRows] := query.Fields[Col].AsString;
    query.Next;
    Inc(Lin);
  End;

{step 4, final tweaks }
stringgrid1.AlternateColor:= RGBToColor(248,248,255);
stringgrid1.AutoSizeColumns; // size of largest contents in column
stringgrid1.ColumnClickSorts:=true;  // allow sorting on clicking column title
stringgrid1.options := stringgrid1.options + [goThumbTracking]  // scrolls as bar moved
  + [goColSizing]  // allow user to resize cols
  + [goRowSelect]; // row select, like in vb.net

To react to a row double-click, add an event procedure :
(add procedure definition line to the form class too, and use the Grid's Object Inspector (design time) Events tab to hook up the event to the proc)

Code: [Select]

procedure TForm1.StringGrid1DblClick(Sender: TObject);
begin
  ShowMessage('Row '+IntToStr(TStringGrid(Sender).Row) +',  Col '+ IntToStr(TStringGrid(Sender).Col));
end; (beware - that will fire when dblclicking the titles row too)

To allow sorting correctly on numerical columns (when using .ColumnClickSorts), investigate using an event proc
TForm1.StringGrid1CompareCells as mentioned in the wiki

I think that gives me just about everything I was used to in vb 

[teos]

I could not resist to post a comment. If you feel more at home with data in a stringgrid than in a DBGrid, you have been in .NET way too long.

The argument that setting properties in code so you know what happens when and therefore coding everything yourself is the main difference between Delphi/Lazarus and .NET. In Delphi/Lazarus a simple database application can be created (like mentioned allready) by putting some components on a form and setting some properties. No code, no looping, no need to re-invent that weel. With a stringgrid (i.e. getting data from a database in code), you have a square, stone wheel. With the visual approach, the wheel is rond, has a tire and everything a modern wheel needs :-)

I have never understood the folks that write the data access and data handling in code just because of the argument of debugging. I DO have heard a former Delphi trainer who switched to .NET raising that argument. But believe me: it's crap.

I would first start the dataset->datasource->dbgrid way and forget about coding it all yourself and introducing new bugs.

The article on StackOverflow does not represent others than regular users. Data aware components are quite OK, also in very large software projects.

[Nebula]

Well thanks, but I still don't really understand how to do everything that way.
What if you want to show the user query results from several joined tables? Doesn't a data aware control get limited to one table binding?

[Knipfty]

No!  In that case bind the datasource to a TQuery object.  In the TQuery object, you simply write the SQL statement that joins n tables together.  One grid, one query, multiple tables.

[teos]

Depending on how to show your data, there is another way:

Another option is the use of 2 datasets, 2 datasources, 2 grids, connect each datasource to a table, each grid to a datasource and the dataset that holds the detail data can be connected to the master source by use of the mastersource property.

Again: all the data you want, this can be nested as deep as needed but can be done without a single line of code.

Except ofcourse when using a Query, then you have to type some SQL :-)

[snorkel]

Thanks, I'll take a look at TDBGrid... I just wish there were more code snippets floating around online.
I guess I've been spoiled with vb.net, having MSDN, stackoverflow.com, sundry forums and blogs - so many people chipping in to the public knowledgebase. I hope more Laz/FPC/OP code examples get onto the net for people to crib from.

For instance, in vb.net I can connect to an MS-SQL server and show a SQL query results table as easily as this, having added a DataGridView control to the form :

Code: [Select]

Dim sqlConnection As New SqlClient.SqlConnection("server=TESTSERVER; database=TESTDB; Integrated Security=SSPI")
Dim sqlAdapter As New SqlClient.SqlDataAdapter("SELECT * from TestTable", sqlConnection)
Dim sqlDataTable As New DataTable
sqlAdapter.Fill(sqlDataTable)
' fill in the grid and tweak some appearances ...
DataGridView1.DataSource = sqlDataTable
DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Lavender
DataGridView1.Columns(2).DefaultCellStyle.Format = "dd.MM.yyyy"
I found all that from online searches, and verified for myself that it worked, well within one working day.
With Lazarus, I'm still searching and scratching my head!

Would anyone please care to be a star and translate the above into Lazarus-speak? 

It's even easier in Lazarus, you don't have to write any code to that.

Just drop a grid on your from, drop a data source, and then drop a DB connecton and query and transaction, then set the dbrid to use the data source in the object inspector
set the data source to use the query component, set the transaction to use the connection and finally set the query to the connection.

Now just active the connection and then activate the query via the object inspector after adding some SQL to the sql property, and bam your grid will fill with data at DESIGN time.

To have alternate row colors, the DBgrid in Lazarus has a option which you can set at design time via the object inspector.

Unlike .net you did not have to write a single line of code to do this, you could or course do all this at run time if you like.

For the date format you can add a column def to the query component and then format your date columns.  Also the DBgrid should show dates fine automatically, the only
thing the dbgrid won't show automatically is memo fields.

believe me you can do database stuff much easier than .net with Lazarus, it's just different and you can do most of it at design time and see live data in the grid at design time, which last I checked was not possible with .net.

As far as code snippets/examples you can pretty much use examples for Delphi's DB grid, but the Lazarus grid has more features built in like the alternate row colors and it
has the ability to auto size the columns to fit the data width automatically.

here is some untested example code

Code: [Select]

var
  myconnection:TPQConnection;
  myquery:TSQLQuery;
  mytransaction:TSQLTransaction;
  mydatasource:TDataSource;
begin
       //create connection
       myconnection:=TPQConnection.Create(nil);
       //create query
       myquery:=TSQLQuery.Create(nil);
       //create datasource
       mydatasource:=TDataSource.Create(nil);
       //set the datasources dataset
       mydatasource.DataSet:=myquery;
       //create transaction
       mytransaction:=TSQLTransaction.create(nil);
       //set up connection
       myconnection.HostName:='localhost';
       myconnection.DatabaseName:='Postgres';
       myconnection.UserName:='postgres';
       myconnection.Password:='yourpassword';
       myconnection.Transaction:=mytransaction;
       myconnection.Connected:=true;
       //setup query
       myquery.DataBase:=myconnection;
       myquery.SQL.Add('select * from pg_database where oid = :oid;');
       myquery.Params.ParamByName('oid').AsInteger:=10;
       myquery.Open;
       //set dbgrid alternate row colors
       dbgrid1.AlternateColor:=clgreen;
       //this will fill the grid with data
       DBGrid1.DataSource:=mydatasource;

       //if you want to loop through the dataset yourself

       while not  myquery.EOF do
       begin
          showmessage(myquery.FieldByName('oid').AsString);
          myquery.next;
       end;

keep in mind I created everything in code except for the dbgrid.  In order to simplify this even more you could put all the components on a data module and then simply create the datamodule in code when you need it.  That way you can set all the properties at design time.

The tquery object is pretty much the same as the .net dataset.  If you want completely unbound just loop through the tsqlquery and put the data where every you want in any  non data bound grid you want.

[snorkel]

Well thanks, but I still don't really understand how to do everything that way.
What if you want to show the user query results from several joined tables? Doesn't a data aware control get limited to one table binding?

You write your sql to join the tables, not join them on the client side.

The .net dataset does not really join tables, it can contain two or more datasets, then you can fill more that one grid by going to the next result set in the dataset
at least that's what I remember.

If you need to join tables, do a join in your sql, if you need to stack tables, do a union in your SQL.

the .net data stuff has nothing on the Lazarus/Delphi way of doing things.

[BigChimp]

the .net data stuff has nothing on the Lazarus/Delphi way of doing things.

Well, IIRC, the .net disconnected dataset model is quite powerful; could be much more powerful than FPC/Lazarus. You also can use bound controls (including datasource etc) quite easily, but this is not a .net forum and my recollection of .net are getting hazier and hazier

Nebula, I'd try the bound control route first, as explained in these posts or as I mentioned in http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1. This will give you a taste of what is possible without much code.

I've been meaning to look at the lazdatadesktop tool (in Lazarus\tools, IIRC) which can generate some code as well based on a database . Haven't really tried it yet, but might be a nice way to get something going fairly quickly. I suppose I'll write a tutorial on that whenever I get to it

Finally, it seems like you've dived into Lazarus with gusto and I hope you like it. If you want to do something that seems very hard you probably are doing it the wrong way - a post to the forum outlining what you want to achieve in general terms (including references to the way you'd do it in .net are fine to illustrate the point) as you're doing seems like a great way to get going quickly.
(Coming from a fellow .Net convert)

[Nebula]

Thanks all, but I'm just not getting that "lightbulb flicking on above my head" moment 

I refine my SQL queries fairly interactively in SQL Server Management Studio, and then cut-n-paste the working SQL into my code afterwards. I don't really see the need to have live data in the form at design time once I'm happy the query is correct. I've coped well enough without that, all this time!

As for speeding up development, I'm not seeing that either - once you have a solution that works for your needs you can make a general purpose routine out of it and use that all over.

Also, in my line of work it seems - unless I'm wrong - that data-aware controls are too simplistic for my needs. We don't use simple forms of the variety where you have buttons for Previous/Next, insert,delete etc. (which is usually what the examples show).
Everything we add to the database stays there, and if it's wrong then a correcting entry has to be made, but everything needs to be logged and audited.
Inserting data is likewise quite complex, with many tables being affected in the transaction, even auditing changes to the simple masterfiles. It really is safer to know exactly what's going on, rather than use tools that could be doing something else behind the scenes for all I know.

Plus, user interface work isn't the main activity, there are other things to be getting on with, the reporting requirements never seem to end - so it's not a huge gain to save a bit of time on the odd form here and there.

I realise people love to promote what works for them, and I'm glad you've got something you're happy with, but I'm just not sold on the idea... or is there some other massive advantage I haven't heard of yet?
I have a feeling that I'd have to start work at another company and be bombarded with good examples before I'd finally see any point changing my ways!
Cheers anyway 

P.S. If Lazarus supports both ideologies, isn't that a good thing? Isn't it better to attract newcomers with the attitude of "Here it is, it's great, use it any way you like!" - ?
It is human nature to stick with what you know when you're happy with it, rather than scratching your head trying to understand a different method when you're not even sure it may be better.
I am open to being sold the idea, if you think you can convince me (and everything on a forum may be useful to many others later), so please don't think you're wasting your time - I enjoy the discussion when I suspect that I may be wrong about something but I just haven't been persuaded yet!
Thanks

[BigChimp]

Some remarks that may be food for thought. Not meant as the be-all and end-all...

Everything we add to the database stays there, and if it's wrong then a correcting entry has to be made, but everything needs to be logged and audited.

IMO that should probably be done at the database level or middleware level to be sure you catch all changes to the db, not just those going via your application.

Inserting data is likewise quite complex, with many tables being affected in the transaction, even auditing changes to the simple masterfiles. It really is safer to know exactly what's going on, rather than use tools that could be doing something else behind the scenes for all I know.

If you don't trust the IDE/compiler... well, yes, then it's more or less game over.
However, there is a way to log every outgoing command:
http://wiki.lazarus.freepascal.org/SqlDBHowto#Troubleshooting:_TSQLConnection_logging
... except it does not show parameter values if using parametrized queries.
Once again, if you need that, I would suggest database level monitoring/auditing (e.g. IIRC the MON$STATEMENTS system table in Firebird)

I realise people love to promote what works for them, and I'm glad you've got something you're happy with, but I'm just not sold on the idea... or is there some other massive advantage I haven't heard of yet?
I have a feeling that I'd have to start work at another company and be bombarded with good examples before I'd finally see any point changing my ways!

Well, I'm not a great GUI guy and often issue SQL "by hand" from my code as well. It's just that in some cases doing everything by hand is inefficient. The trick is of course to find out when such a case exists

P.S. If Lazarus supports both ideologies, isn't that a good thing? Isn't it better to attract newcomers with the attitude of "Here it is, it's great, use it any way you like!" - ?
It is human nature to stick with what you know when you're happy with it, rather than scratching your head trying to understand a different method when you're not even sure it may be better.
I am open to being sold the idea, if you think you can convince me (and everything on a forum may be useful to many others later), so please don't think you're wasting your time - I enjoy the discussion when I suspect that I may be wrong about something but I just haven't been persuaded yet!

Not really trying to sell you the databound idea, I just would like to make sure you know when it's better to use it
As you say, doing it multiple ways is a good thing.