DBGrid showing "(MEMO)" as the value of string fields

Question

I'm trying to write a simple SQLite application using Lazarus and the SQLdb components.

I connect to the database and populate a TDBGrid. Problem is that all columns that are text fields display the value "(MEMO)" rather then the string in database.

Honeyman

changing column types in the database fixes this, see my answer – Profitsharing

Answer 1

I have found a simple solution:

The property dgDisplayMemoText from the DBGrid must be enabled.

Addison

Answer 2

I forgot the source of this but this is what I am doing with memo fields in tdbgrid. bluish is right about the gettext event, this is how to implement it in the code:

Create a class called MemoDifier:

MemoDifier = class
  public
    procedure DBGridOnGetText(Sender: TField; var aText: string;
      DisplayText: boolean);
  end;                  

At the implementation section of your code, put this:

procedure MemoDifier.DBGridOnGetText(Sender: TField; var aText: string;
  DisplayText: boolean);
begin
  if (DisplayText) then
    aText := Sender.AsString;
end; 

Then click the tdbgrid control in your form and at the Object Inspector(Lazarus IDE), click the Events tab, scroll below to find the OnPrepareCanvas event. Double click it to generate the code. Then modify the code to suit to your needs such as the name of your tdbgrid control:

procedure Tmainui.TDBGrid1PrepareCanvas(sender: TObject;
  DataCol: Integer; Column: TColumn; AState: TGridDrawState);
var
  MemoFieldReveal: MemoDifier;
begin
   if (DataCol = 1) then
   begin
     try
       TDBGrid1.Columns.Items[0].Field.OnGetText := @MemoFieldReveal.DBGridOnGetText;
       TDBGrid1.Columns.Items[1].Field.OnGetText := @MemoFieldReveal.DBGridOnGetText;
       TDBGrid1.Columns.Items[2].Field.OnGetText := @MemoFieldReveal.DBGridOnGetText;
     except
       On E: Exception do
       begin
         ShowMessage('Exception caught : ' + E.Message);
       end;
     end;
   end;
end; 

The variable MemoFieldReveal points to the class MemoDifier. Don't forget to modify the index (Items[x]) to point to your index number of the tdbgrid items/fields which shows the (MEMO) text.

Granadilla

Answer 3

Memo fields cannot be shown in the TDBGrid. Add TDBMemo to the form and connect it to the same TDataSource. You will see the text in your memo in this case.

Annieannihilate

Answer 4

Old question but I came across it while looking for a similar issue, but on the data retrieved by code rather than with a DBGrid; When I retrieved a string fields with the methods DisplayText or Text, I got "(memo)" instead of the correct value.

The answer is actually simple… The TSQLQuery class owns a set of methods called AsXxx to get the data according to a type of data. Here use AsString to assign a variable inline.

SQLQuery1.Open;
//some check to make sure there are fields
name:=SQLQuery1.Fields[1].AsString;   //gives "English" for example
code:=SQLQuery1.Fields[2].DisplayText;   //gives "(Memo) instead of "en"

More, if you don't know at design time of which type the variable is (for example, you want to design a generical function for any kind of table) with its FieldDefs property of your TSqlQuery object can help you. It owns a DataType property that allows to choose which AsXxx function to use. Here for example.

SQLQuery1.Open;
//some check to make sure there are fields
with SQLQuery1.FieldDefs[1].DataType of
  ftMemo: SQLQuery1.Fields[1].DisplayText;        //gives "(Memo)"
  ftString: name:=SQLQuery1.Fields[1].AsString;  //gives "English" for example
...
end;

And if you look at the datatype while debugging, you will notice that with SQLite, any text is seen as a ftMemo, not a ftString for there is only one type of text there.

Gord

Answer 5

As said on IRC, you probably need to add the fields of your query to the form, (so that "field" components are generated for them) and then implement the TMemoField.GetText event.

See if entering the "fields" field in the object inspector brings up an editor to generate the components (it does so in Zeos iirc).

Analyse

I don't quite get this answer. I have the same problem but I'm using postgres. What exactly should I do? Could you please elaborate? Thanks! – Berlin

Answer 6

I have the same in MySQL and Tgrid so I'm hoping the answer is the same as it is quite simple :-)

Say if s is the field causing the problem then Instead of writing

SELECT s

write

SELECT LEFT(s,200) AS s 

Tetrapody

Answer 7

An apparently simple solution is to limit the length of the TEXT in the field using something like VARCHAR(n) in the column type where n is the maximum number of allowed characters.

Comedo

Answer 8

Another option

If you are using TZConection add this line to you code when you Connect you database

TZConnection).Properties.Add('Undefined_Varchar_AsString_Length=100'); 

Jernigan

How does that even compile? – Decalcomania

Answer 9

after some tests, I could see that although the fields are treated as "MEMO", for "string" fields, just keep the "VARCHAR" option instead of "TEXT" when creating the table.

Bostick

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Biparty

Answer 10

You can also simple use TRIM in your sql:

SELECT TRIM(myfield) as NewField

This trick also works in Lazarus.

Removed

Answer 11

If oracle database, change Tquery and put on select string .... cast(field as varchar2(100) as field ....

Precast

Answer 12

This article gives a solution: Displaying and editing MEMO fiels in Delphi's TDBGrid.

Here I summarize what you have to do:

• in the .dfm add OnGetText = MyDataSetMyFieldGetText to the TMemoField (here named MyField) belonging to your data set (for example a TTable, here named MyDataSet)

• in the .pas > interface > type > inside your form definition, add

  procedure MyDataSetMyFieldGetText(Sender: TField; var Text: string; DisplayText: Boolean);
  

• in the .pas > implementation > add this method

  procedure TDM.WorkVisiteNoteGetText(Sender: TField; var Text: string; DisplayText: Boolean);
  begin
    Text := Copy(WorkVisiteNote.AsString, 1, 100);
  end;
  

Fairman

Why do you need to add in the .dfm? If you have a persistent field, you can select that field in the structure view (or drop down list at the top of the Object Inspector) and use the Object Inspector's Events tab to do that, and the IDE will generate the event handler's stub code for you. – Tum