[SOLVED] Conversion problem

[Slyde]

I have some NULL text fields in my SQLite3 db.  And when I read a row I populate some TMemos with the fields.  But when I get to a field that's NULL, I get the following error:

Could not convert variant of type (Null) into type (String).

from a line like this:

Code: Pascal  [Select][+][-]

1. Memo5.Text := frmMain.DS2.DataSet.Fields[5].Value;

if frmMain.DS2.DataSet.Fields[5].Value is NULL, then I need to be able to convert it for TMemo.  I just don't know how to do it.

[Slyde]

I fixed the problem and tried to remove this post.  My apology.

[Zvoni]

Yes solved, i know, but why don't you "convert" it inside the SQL-Statement/Resultset?
There is no reason to do this crap in Frontend-code

[Slyde]

I don't know how to do that.  I do it on the frontend because I don't how to convert on the backend a NULL value in SQL to a value TMemo will accept.

[Zvoni]

I don't know how to do that.  I do it on the frontend because I don't how to convert on the backend a NULL value in SQL to a value TMemo will accept.

Show your SQL-Statement (SELECT blablabla)
And what database? SQLite, MySQL?

[Slyde]

SQLite3

Code: Pascal  [Select][+][-]

1. ZQ_Grid.SQL.Text := 'SELECT qid, q, a1, a2, a3, a4, ta FROM MC_Questions';

[Zvoni]

SQLite3

Code: Pascal  [Select][+][-]

1. ZQ_Grid.SQL.Text := 'SELECT qid, q, a1, a2, a3, a4, ta FROM MC_Questions';

Which column returns the NULL? You have above Dataset.Field[5]
Is it a3 or a4?
I don't remember if Fields-List is zero-based

EDIT:

Code: SQL  [Select][+][-]

1. SELECT qid, q,
2. COALESCE(a1, 'Default for a1') AS a1,
3. COALESCE(a2, 'Default for a2') AS a2,
4. COALESCE(a3, 'Default for a3') AS a3,
5. COALESCE(a4, 'Default for a4') AS a4,
6. COALESCE(ta, 'Default for ta') AS ta
7. FROM MC_Questions
8.  

COALESCE --> Returns the first NON-NULL-Value
Instead of "Default for xx" you can enter whatever text you want to be shown.
Even empty string
COALESCE(a3, '') As a3

[Slyde]

SQLite3 is zero-based.  Column 6, the 'ta' column has some NULLs in it.

This is how I fixed it:

Code: Pascal  [Select][+][-]

1. if frmMain.DS2.DataSet.Fields[6].Value = Null then
2.     Edit1.Text := ''
3.   else
4.     Edit1.Text := frmMain.DS2.DataSet.Fields[6].Value;

It may be crap, but at least I got it to work.  I'm learning as I go, Zvoni. 

[Zvoni]

SQLite3 is zero-based.  Column 6, the 'ta' column has some NULLs in it.

This is how I fixed it:

Code: Pascal  [Select][+][-]

1. if frmMain.DS2.DataSet.Fields[6].Value = Null then
2.     Edit1.Text := ''
3.   else
4.     Edit1.Text := frmMain.DS2.DataSet.Fields[6].Value;

As i said: No need for it.
If you have fields which are already setup as "NON-NULL" in your Database you can remove the COALESCE (e.g. if a1 to a4 can never be NULL in the database you can remove the COALESCE)
e.g.

Code: SQL  [Select][+][-]

1. SELECT qid, q, a1, a2, a3, a4,
2. COALESCE(ta, 'Default for ta') AS ta
3. FROM MC_Questions

EDIT: Looks like multiple-choice questionnaire
qid = Primary Key
q = Text-Field "Question"
a1-a4 = Text-Fields "Answer X"
what is ta? (just out of curiosity)

[Slyde]

I've never used COALESCE before.  But I was thinking of doing the same thing with an UPDATE statement.  Just looked at it on SO.

Thanks for helping, Zvoni.  I appreciate you.

[Slyde]

'ta' := The Answer 

[Zvoni]

I've never used COALESCE before.  But I was thinking of doing the same thing with an UPDATE statement.  Just looked at it on SO.

Thanks for helping, Zvoni.  I appreciate you.

COALESCE is IMO one of the more important functions in SQL.
I use it extensively in my queries, when i have to grab data from different tables at the same time, especially in LEFT JOINS

EDIT:
What COALESCE(ta, '') is doing on the backend would be equivalent to frontend (-aircode)

Code: Pascal  [Select][+][-]

1.     if frmMain.DS2.DataSet.Fields[6].Value <> Null then        
2.         Edit1.Text := frmMain.DS2.DataSet.Fields[6].Value
3.     Else
4.         Edit1.Text := '';

[Slyde]

I incorporated COALESCE into my query and it worked like a charm.  Thanks again for your help, Zvoni.

[jcmontherock]

In MySQL the field returned is 'NULL'. I use:

Code: Pascal  [Select][+][-]

1.     // Change text color if null field
2.     if sText = 'NULL' then StringGrid1.Canvas.Font.Color := clSilver
3.     else                           StringGrid1.Canvas.Font.Color := clBlack;
4. ...
5.  

[wp]

Code: Pascal  [Select][+][-]

1. Memo5.Text := frmMain.DS2.DataSet.Fields[5].Value;

Maybe like this?

Code: Pascal  [Select][+][-]

1. Memo5.Text := frmMain.DS2.DataSet.Fields[5].AsString;

Or like this?

Code: Pascal  [Select][+][-]

1. if not IsNull(frmMain.DS2.Dataset.Fields[5]) then Memo5.Text := frmMain.DS2.DataSet.Fields[5].AsString else Memo5.Text := '';