[SQLite] Feeding combo with SELECT?, Delphi newbie inside® Options

[littlebigfred]

Hello,

I'm a Delphi7 newbie trying to use Aducom's wrapper to SQLite and NextDBGrid to edit the following table:

CODE

CREATE TABLE phones (phones_tel TEXT PRIMARY KEY NOT NULL, phones_name TEXT NOT NULL, phones_no_cid INTEGER, phones_private INTEGER, phones_tel_parent TEXT);

Since a customer may have more than one phone number, I added the phones_tel_parent column. For each child phone number, I need to set this column to that customer's main phone number, or to NULL if it is that customer's main number.

To do this, I guess I need NextDBGrid to do this:
- it must have three columns: phones_tel, phones_name, and phones_tel_parent
- the third column must be a combobox that contains all the rows, so I can just pick the customer's main number
- fill the grid with all the rows from that table
- save this row to the SQLite database

Here's what things look like at this point:
http://img411.imageshack.us/img411/3044/ne...idsqlitegf1.jpg
http://img246.imageshack.us/img246/5370/ne...dsqlite2vf8.jpg

I did read this article, but I don't know how to run SELECT on the grid and the combobox, and how to save the change to the database. Besides, when I hit F9, those TEXT columns are marked as "(MEMO)" in NextDBGrid :-/ Can someone help?

Thank you.

[Boki (Berg)]

Hello,

I will first answer on (MEMO) question. Use MemoColumn instead TextColumn and set MemoDisplayOptions property to mdContent.

I will answer on other questions shortly when I fully review it.

Best regards

[Boki (Berg)]

Hello again,

I am not sure that I have understand question completelly, but try to answer:

You may fill DBComboBox Column with results from another TQuery component.

I have attach one small sample project to demonstrate you how to made this. Please note that it is done with using ADO, but I hope that you will have no problems.

Best regards

Attached File(s)

 Phones_Sample.zip ( 24.52K ) Number of downloads: 5

 

[littlebigfred]

Hello

I will first answer on (MEMO) question. Use MemoColumn instead TextColumn and set MemoDisplayOptions property to mdContent.

After adding an NxDBMemoColumn1 column to a NextDBGrid, I don't see any property named MemoDisplayOptions. I opened all the lines marked "+", but nothing. Is it located elsewhere in the project?

You may fill DBComboBox Column with results from another TQuery component.

Sorry for not being clear, since I'm totally in the dark with Delphi and databases anyway :-/ Here's a diagram:

http://img526.imageshack.us/img526/9030/delphisqlite2dt3.jpg

I have two tables ("Phones" and "Activity") in an SQLite database. I'd like to connect the grid to that database using Aducom's free DB + Query/Table widgets and a DataSource.

I'd like to SELECT all rows in table #1, and one of the columns ("Activity") should be a combobox that lists rows from table #2 through a SELECT.

I have attach one small sample project to demonstrate you how to made this. Please note that it is done with using ADO, but I hope that you will have no problems.

Thanks for the sample. I notice that it contains SQL in the source code. Does it mean I should put SQL statements in the source code instead of typing them in the Table/Query widgets?

Thank you.

[Boki (Berg)]

Hello Fred,

Please note that MemoDisplayOptions property is available in latest version only (v4.2.4). You may download it from Members Section.

Thanks for the sample. I notice that it contains SQL in the source code. Does it mean I should put SQL statements in the source code instead of typing them in the Table/Query widgets?

Yes, because after you select different record (AfterScroll event), we fill Items property of ComboBox column.

Here is a code from sample again:

1) AfterScroll event of Table (this is ADO table in sample, but table component from SQLite have it too).

CODE

procedure TForm1.ADOTable1AfterScroll(DataSet: TDataSet);
var
  PersonId: Integer;
begin
  PersonId := ADOTable1.FieldByName('ID').AsInteger;

  ADOQuery1.Close;
  ADOQuery1.SQL.Text := 'SELECT PhoneNumber FROM phones WHERE PersonId = ' + IntToStr(PersonId);
  ADOQuery1.Open;

  with colPhone do
  begin
    Items.Clear;
    while not ADOQuery1.Eof do
    begin
      Items.Add(ADOQuery1.FieldByName('PhoneNumber').AsString);
      ADOQuery1.Next;
    end;
  end;

end;

2) So, after new record is selected, we are reading field ID of current record. This is next line in sample:

CODE

  PersonId := ADOTable1.FieldByName('ID').AsInteger;

3) In phones table we are having PersonId field which connect person with phone. In that case, every person may have more than 1 phone.

4) We will select all records with using SQL Query component where PersonId is equal to variable PersonId.

CODE

  ADOQuery1.SQL.Text := 'SELECT PhoneNumber FROM phones WHERE PersonId = ' + IntToStr(PersonId);

5) Then, we fill Items property (TStrings) of column by reading field PhoneNumber.

I hope that this helps a little bit

I suggest trying to convert this sample project to SQl lite at first place which may help you understanding concept better

Best regards

[littlebigfred]

Thanks for your help + sample, I made good progress. I can now successfully connect the NextDBGrid widget to a SQLite table

But I now have the same "(MEMO)" issue with the DBComboBoxColumn that I had with the DBTextColumn: The three columns in the table are defined as TEXT, and DBComboBox doesn't have DBMemoColumn's MemoDisplay property:

http://img404.imageshack.us/img404/8614/ne...idparentzt9.jpg

BTW, actually, I don't need to query a second table: What I need to do, is for each row in the Phones table, to fill the Parent column so as to know if this number is the customer's main office number (in which case, this column should be empty, ie. NULL), or if it's an extension (in which case, the user must select the main office number through the DBComboBox Parent column). IOW, a customer may have more than one number, so I need to specify for each number if it's the main number or an extension.

Is there a work-around for the DBComboBox not showing TEXT data as expected?

Thank you.

[Boki (Berg)]

Hello Littlebigfred,

Maybe there is no need to use Memo Blob Field, but instead Char (string) field-type?

MemoField is used mainly for large texts and maybe in your situation you don't need such field type?

Best regards

[littlebigfred]

Maybe there is no need to use Memo Blob Field, but instead Char (string) field-type? MemoField is used mainly for large texts and maybe in your situation you don't need such field type?

According to this page, I thought that I could use TEXT for any non-numeric bit:

http://sqlite.org/datatype3.html

But I guess the Aducom SQLite components do treat TEXT and VARCHAR differently, which would explain why grid objects (I'm also having this issue with the open-source SMDBGrid) handle the two columns differently :/ I'll check if there are limitations to using VARCHAR.

Thanks.

[Boki (Berg)]

Hello Fred,

You probably should use VARCHAR instead TEXT. It seems that Text is Memo Blob Field

I am using VARCHAR in MySql for string fields.

Best regards

[littlebigfred]

You probably should use VARCHAR instead TEXT. It seems that Text is Memo Blob Field I am using VARCHAR in MySql for string fields.

Someone told me that it's not really that much more work to use a regular grid than a DB-aware grid, so I'd like to give it a shot, and just read an SQLite file directly, so I can see for myself, and also keep using TEXT and just convert this to some Delphi string type so I can use any grid (I'm having the same MEMO issue with SMDBGrid, since I'm told that the Aducom SQLite connector turns TEXT into TMemo).

I've never done this, so if someone has information about the following, this would help:
1. How to read an SQLite file directly? Is there some kind of up-to-date wrapper to open the DLL and access the file?
2. How to put this data into a grid, let the user make changes, and rewrite the data to the file?

Thank you.

[Boki (Berg)]

Hello Fred,

You may simply read DataSet with using similar code to the:

CODE

row := 0;

NextGrid1.AddRow(Table1.RowCount);

Table1.First;

while not Table1.Eof do
begin
  for i := 0 to Table1.Fields.FieldsCount - 1 do
  begin
    NextGrid1.Cell[i, row].AsString := Table1.Fields[i].AsString;
    Inc(row);
    Table1.Next;
  end;
end;

This sample code will load all data from Table (or Query) and load it into grid. But columns need to be added first.

But I think that it may be better that you use VARCHAR instead TEXT. I am not sure why developers of SQLite made TEXT type to be MEMO Blob type.

Best regards

[littlebigfred]

You may simply read DataSet with using similar code to the:

Thanks much for the code I'll give up on using NextDBGrid for now, fill a NextGrid with the output of SELECT's, and, once the user has made changes, just write the contents of the grid into a text file that I'll import into the SQLite file. Rough, but it'll do the job for now, as there are too many details I don't know yet on how to link a database to a table widget to a DBgrid, and have the changes be written back to the database.

I've looked at the ADO sample above, but now that I no longer use a NextDBGrid, I don't know how to fill the last column (NxComboBoxColumn1) with items that I read from a SELECT into a StringList array.

CODE

procedure TForm1.Button1Click(Sender: TObject);
var
  row,i : Integer;
  MyTable : TStringList;
begin
  with ASQLite3DB1 do begin
    DefaultDir := ExtractFileDir(Application.ExeName);
    Database := 'db.sqlite';
    Open;
  end;

  MyTable := TStringList.Create;
  //Make sure you created enough columns in NextGrid!
  with ASQLite3Query1 do begin
      //Fill MyTable with phone numbers
      SQL.text := 'select numbers from phones';
      Open;
      while not eof do begin
          MyTable.Add(FieldByName('numbers').AsString);
          Next;
      end;
      Close;

      SQL.text := 'select * from phones';
      Open;
      NextGrid1.AddRow(ASQLite3Query1.RecordCount);
      row := 0;
      while not eof do begin
          //Ignore last field
          for i := 0 to ASQLite3Query1.FieldCount - 2 do begin
            NextGrid1.Cell[i, row].AsString := ASQLite3Query1.Fields[i].AsString;
          end;

          //? How to fill last column NxComboBoxColumn1 with items from MyTable?
          for i := 0 to MyTable.Count -1 do begin
            //? Why is the leading zero in 023290 removed?

            //NOK NextGrid1.Cell[ASQLite3Query1.FieldCount -1, row].AsString := MyTable.ValueFromIndex[i];
                        //NOK NxComboBoxColumn1.Items.Add(MyTable.ValueFromIndex[i]);
          end;
          Inc(row);
          Next;
      end;
      Close;
  end;

  MyTable.Free;
  ASQLite3DB1.Close;
end;

Thank you.

[Boki (Berg)]

Hello Fred,

ComboBox column have Items proerty which may be filed using next way (You will also need to go trough DataSet with using .Next):

CODE

with NextGrid1.Columns[5] as TNxComboBoxColumn do
begin
  Items.Clear;
  Items.Add('item 0');
  Items.Add('item 1');
  Items.Add('item 2');
  ....
end;

Best regards

[littlebigfred]

ComboBox column have Items proerty which may be filed using next way (You will also need to go trough DataSet with using .Next):

Thanks. I must be doing something wrong because the combobox isn't displayed, and nothing drops down when I click on this cell : http://img223.imageshack.us/img223/7008/nextgridcombomj8.jpg

I simplified the code to isolate the problem:

CODE

  NextGrid1.AddRow(1);
  //0-3 : text, 4 : combo
  with NextGrid1.Columns[4] as TNxComboBoxColumn do begin
    Items.Clear;
    Items.Add('123');
  end;

[littlebigfred]

This doesn't work either

CODE

procedure TForm1.Button1Click(Sender: TObject);
[...]
  NextGrid1.AddRow;
  //NextGrid1.Cell[4,0].AsString := 'test';
  //BAD with NextGrid1.Columns[4] as TNxComboBoxColumn do begin

  NxComboBoxColumn1.Items.Add('test');

[Boki (Berg)]

Hello Fred,

Maybe you didin't set coEditing in Column.Options property to True?

Best regards

[littlebigfred]

I just set it to True, but it makes no difference. I also looked at the list of articles and the \Demos sub-directory, but didn't find tutorials on how to use the different components.

Generally speaking, how do newbies use Bergsoft components? By learning with other components (eg. Borland's native components), and copy/pasting because the Bergsoft components have the same logic?

I'm totally in the dark, and there's so many settings, that I don't know where to go. If someone has a working "hello, world" project that shows how to get data from a table/query component, fill a NextDBGrid (including a combobox filled with another SELECT), and how to rewrite the changes back to the database, I'm interested

[littlebigfred]

I just set it to True, but it makes no difference.

Found it: I mistakenly set the first column (text) to Options.coEditing=True instead of the last column (combobox) It works now. Sorry for the above.

[Boki (Berg)]

Hello Fred,

If you want, you may attach here sample project and I may see where is a problem with combo-box column. You must be able to edit cell when coEditing is True.

I think that your problem is common for any kind of Grid (non-db) component and it is more DB-related. You have solve loading data into grid and now you need to check what is changed and then with using UPDATE sql command transfer it back to DB.

NextGrid common usage is very similar to TStringGrid usage and I think that many procedures for TStringGrid may work on NextGrid.

Again, I suggest better go with NextDBGrid and VARCHAR field type

Best regards

[aducom]

According to this page, I thought that I could use TEXT for any non-numeric bit:

http://sqlite.org/datatype3.html

But I guess the Aducom SQLite components do treat TEXT and VARCHAR differently, which would explain why grid objects (I'm also having this issue with the open-source SMDBGrid) handle the two columns differently :/ I'll check if there are limitations to using VARCHAR.

Thanks.

Yes, Text and varchar are teated differently. Varchar has a described max length declaration like varchar(somelength) and is treated as AnsiString. Text however does not have a length qualifier and is treated as a CLOB.