CODEWITHSUNDEEP
mysqlsqldelphi
t1f
t1f

Reputation: 3181

Null field not inserting NULL from empty TEdit

I add new order details to a table in my db. One of the columns there is named email, in the form I created to add the new order in my delphi application and subsequently the db, I use TEdits to pass the data along.

This is the code:

procedure TForm2.actAddComandaExecute(Sender: TObject);
begin
if dbmodule.comenziConnection.Connected then
begin
    if addOrderForm.ShowModal=mrok then
begin
    dbmodule.comenziQuery.SQL.Clear;
    dbmodule.comenziQuery.SQL.Add( 'insert into `r33758pi_tipotask`.`comenzi` ( stare, client, telefon, email, detalii, livrare, pret, user, status, observatii ) values ( :stare, :client, :telefon, :email, :detalii, :livrare, :pret, :user, :status, :observatii ) ' ) ;
    dbmodule.comenziQuery.Params.ParamByName( 'stare' ).AsString := addOrderForm.ComboBoxEx1.Text ;
    dbmodule.comenziQuery.Params.ParamByName( 'client' ).AsString := addOrderForm.Edit2.Text ;
    dbmodule.comenziQuery.Params.ParamByName( 'telefon' ).AsString := addOrderForm.Edit3.Text ;
    dbmodule.comenziQuery.Params.ParamByName( 'email' ).AsString := addOrderForm.Edit4.Text ;
    dbmodule.comenziQuery.Params.ParamByName( 'detalii' ).AsString := addOrderForm.Edit5.Text ;
    dbmodule.comenziQuery.Params.ParamByName( 'livrare' ).AsString := addOrderForm.ComboBoxEx6.Text ;
    dbmodule.comenziQuery.Params.ParamByName( 'pret' ).AsString := addOrderForm.Edit7.Text ;
    dbmodule.comenziQuery.Params.ParamByName( 'user' ).AsString := addOrderForm.ComboBoxEx8.Text ;
    dbmodule.comenziQuery.Params.ParamByName( 'status' ).AsString := addOrderForm.ComboBoxEx9.Text ;
    dbmodule.comenziQuery.Params.ParamByName( 'observatii' ).AsString := addOrderForm.Edit10.Text ;
    dbmodule.comenziQuery.ExecSQL ;
end;
end;
end;

If I leave Edit4 empty on the form everything inserts but the field email for that order doesn't have a Null value, it shows just as being empty - not null but no data either.

The column email is set as Null by default in db so that's not the problem.

Screenie from workbench: enter image description here

There should be 2 NULL showing up between the values there but it's just empty.

Any ideas why?

Using Rad Studio 10 Seattle and dbExpress components

EDIT

For any future readers, please do read all the answers comments also, great stuff in there.

Upvotes: 2

Views: 1305

Answers (3)

Jerry Dodge
Jerry Dodge

Reputation: 27276

An empty string is not the same thing as a NULL string. When it comes to SQL, you need to understand the difference.

You need to add some sort of logic to write string values vs null values, such as...

if addOrderForm.Edit5.Text <> '' then
  dbmodule.comenziQuery.Params.ParamByName('detalii').AsString := addOrderForm.Edit5.Text
else
  dbmodule.comenziQuery.Params.ParamByName('detalii').Value := NULL;

This way, if the edit control is empty, a NULL will get written to the table field instead of an empty string.

As suggested, this can further be wrapped inside a common function to save you on writing a ton of code:

function NullIfEmpty(const S: string): Variant;
begin
  if S <> '' then
    Result := S
  else
    Result := NULL;
end;

And then use it like...

dbmodule.comenziQuery.Params.ParamByName('detalii'):= 
  NullIfEmpty(addOrderForm.Edit5.Text);

Upvotes: 8

Christine Ross
Christine Ross

Reputation: 463

You should use TDB* components which handle automatically such problems.

Upvotes: -6

David A
David A

Reputation: 379

The problem you are having is due to using ParamByName( 'email' ).AsString, which will set the email column to a blank string. If you want it to remain null I would use code like this to clear the parameter, 

if Trim(addOrderForm.Edit4.Text) = '' then
  dbmodule.comenziQuery.Params.ParamByName('email').Clear
else
  dbmodule.comenziQuery.Params.ParamByName('email').AsString := Trim(addOrderForm.Edit4.Text);

Upvotes: 2

Related Questions