Field x is required, But not supplied.

jessewillem · Post by **jessewillem** » 16.02.2010, 20:47

Everythime I want to insert data to my MySQL database I have this erro message. I'm using the ZQuery with the ZUpdateSQL component. I've added all the parameters. But everythime I have this error. Can someone help me with it, I don't know tihs is a stupid question or not, Or it's asked many times.

Here is some of my code.

Code: Select all

procedure TForm2.Button2Click(Sender: TObject);
var
  while22 : integer;
begin
  if opendialog1.Execute then
  begin
    listbox2.Items.AddStrings(opendialog1.Files);
    while22 := 1;
  {  while 0 >= listbox2.Items.Count do
      form3.BASSPlayer1.Open(listbox2.Items.Strings[while22]);
      zquery4.Active := false;
      zquery4.Active := true;
      zquery4.Append;
      zupdatesql1.Params.ParamByName('genre').Value := form3.BASSPlayer1.StreamInfo.Genre;
      zupdatesql1.params.ParamByName('artist').Value := form3.BASSPlayer1.StreamInfo.Artist;
      zupdatesql1.params.ParamByName('album').Value := form3.BASSPlayer1.StreamInfo.Album;
      zupdatesql1.params.ParamByName('title').Value := form3.BASSPlayer1.StreamInfo.Title;
      zupdatesql1.params.ParamByName('filename').Value := form3.BASSPlayer1.StreamPath;
      zquery4.Post;
      zquery4.ApplyUpdates;
      zquery4.CommitUpdates;
      inc(while22);
  end;
end;      }
 //myQuery.Prepare;

 try
    for while22 := 0 to listbox2.Items.Count do
    begin
      //myString := IntToStr(i);
   //   myQuery.ParamByName('AString').AsString := myString;
   //   myQuery.ExecSQL;
         form3.BASSPlayer1.Open(listbox2.Items.Strings[while22]);
      //zquery4.Active := false;
      //zquery4.Active := true;
      zquery4.Append;
     { zupdatesql1.Params.ParamByName('genre').Value := form3.BASSPlayer1.StreamInfo.Genre;
      zupdatesql1.params.ParamByName('artist').Value := form3.BASSPlayer1.StreamInfo.Artist;
      zupdatesql1.params.ParamByName('album').Value := form3.BASSPlayer1.StreamInfo.Album;
      zupdatesql1.params.ParamByName('title').Value := form3.BASSPlayer1.StreamInfo.Title;
      zupdatesql1.params.ParamByName('filename').Value := form3.BASSPlayer1.StreamPath;  }
      zupdatesql1.Params.FindParam('genre').Value := 'test';
      //zupdatesql1.params.ParamByName('artist').Value := 'test';
      //zupdatesql1.params.ParamByName('album').Value := 'test';
      //zupdatesql1.params.ParamByName('title').Value := 'test';
      //zupdatesql1.params.ParamByName('filename').Value := 'test';
      zquery4.Post;
      zquery4.ApplyUpdates;
      zquery4.CommitUpdates;
    //  inc(while22);
    end;
  finally
  //  myQuery.UnPrepare;
  while22 := 0;
  end;
  end;
 end;

end.

MySQL query

Code: Select all

INSERT INTO songs2(title, artist, album, genre, filename)VALUES(:title, :artist, :album, :genre, :filename);

This is the query i'musing with the updatesql component.

jeremicm · Post by **jeremicm** » 17.02.2010, 11:05

Looks like you have one more field in table that is set to not null and you are not filling it with data.

Can you post SQL table structure?

jessewillem · Post by **jessewillem** » 17.02.2010, 14:09

Code: Select all

Field  	Type  	Collation  	Attributes  	Null  	Default  	Extra  	Action
	ID 	int(11) 			No 	None 	auto_increment 	Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	title 	longtext 	latin1_swedish_ci 		No 	None 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	artist 	longtext 	latin1_swedish_ci 		No 	None 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	album 	longtext 	latin1_swedish_ci 		No 	None 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	genre 	text 	latin1_swedish_ci 		No 	None 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	filename 	longtext 	latin1_swedish_ci 		No 	None 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext

As I see I have to change not null to null?

jessewillem · Post by **jessewillem** » 17.02.2010, 14:58

Changed it, But now I get only Null's in my DB. Strange?

Pitfiend · Post by **Pitfiend** » 17.02.2010, 18:51

Can you put the sql query in the zquery? Sometimes that happens when you misspell anything in the sql definition, that it's taken as a field, resulting in that weird error.

Post by **mdaems** » 17.02.2010, 20:29

Jesse,

Why are you using a TZUpdateSQL component in this case.
Seems like the easiest solution is :

- Set ZQeery.sql to 'select * from <tablename>' (replace <tablename> and eventually list field names instead of *)
- ZQuery.Open -> this opens the resultset. If you only want to append, add a where condition to the query like 'where 1=2'. The result is and (empty) dataset
- ZQuery.append; -> adds a new empty row to the dataset
- ZQuery.FieldByname('genre').AsString := .....
- You shouldn't have to fill the ID field as mysql autoincrements are handled correctly (I hope)
- ZQuery.Post; ->finishes editing the newly added line
- ZQuery.close

Does that solution work?

Succes ermee!

Mark

jessewillem · Post by **jessewillem** » 18.02.2010, 18:24

Thanks for your tips. But the problem still isn't fixed. Is it because I use ZUpdateSQL component?, Do I have to do it with a ZQuery to insert records?
Here's my code, I currently have.

Code: Select all

procedure TForm2.Button2Click(Sender: TObject);
var
  while22 : integer;
begin
  if opendialog1.Execute then
  begin
    listbox2.Items.AddStrings(opendialog1.Files);
    while22 := 0;
     while while22 >= listbox2.Items.Count do
      form3.BASSPlayer1.Open(listbox2.Items.ValueFromIndex[while22]);
      form3.ListBox2.Items.Add(form3.BASSPlayer1.StreamInfo.Title);
      zquery4.Active := false;
      zquery4.Active := true;
      zquery4.Append;
      zupdatesql1.Params.ParamByName('genre').Value := form3.BASSPlayer1.StreamInfo.Genre;
      zupdatesql1.params.ParamByName('artist').Value := form3.BASSPlayer1.StreamInfo.Artist;
      zupdatesql1.params.ParamByName('album').Value := form3.BASSPlayer1.StreamInfo.Album;
      zupdatesql1.params.ParamByName('title').Value := form3.BASSPlayer1.StreamInfo.Title;
      zupdatesql1.params.ParamByName('filename').Value := form3.BASSPlayer1.StreamPath;
      zquery4.Post;
      inc(while22, 1);
  end;
end;
 //myQuery.Prepare;
   { for while22 := 0 to listbox2.Items.Count do
    begin
      //myString := IntToStr(i);
   //   myQuery.ParamByName('AString').AsString := myString;
   //   myQuery.ExecSQL;
         form3.BASSPlayer1.Open(listbox2.Items.Strings[while22]);
         form3.ListBox2.Items.Add(form3.BASSPlayer1.StreamInfo.Title);
      zquery4.Active := false;
      zquery4.Active := true;
      zquery4.Append;
      zupdatesql1.Params.ParamByName('genre').Value := form3.BASSPlayer1.StreamInfo.Genre;
      zupdatesql1.params.ParamByName('artist').Value := form3.BASSPlayer1.StreamInfo.Artist;
      zupdatesql1.params.ParamByName('album').Value := form3.BASSPlayer1.StreamInfo.Album;
      zupdatesql1.params.ParamByName('title').Value := form3.BASSPlayer1.StreamInfo.Title;
      zupdatesql1.params.ParamByName('filename').Value := form3.BASSPlayer1.StreamPath;
     // zupdatesql1.Params.FindParam('genre').Value := 'test';
      //zupdatesql1.params.ParamByName('artist').Value := 'test';
      //zupdatesql1.params.ParamByName('album').Value := 'test';
      //zupdatesql1.params.ParamByName('title').Value := 'test';
      //zupdatesql1.params.ParamByName('filename').Value := 'test';
      zquery4.Post;
      zquery4.Close;
      //zquery4.SQL.Add('INSERT INTO songs2(title, artist, album, genre, filename)VALUES(''' + form3.BASSPlayer1.StreamInfo.Title + ''' , ''' + form3.BASSPlayer1.StreamInfo.Artist + ''', ''' + form3.BASSPlayer1.StreamInfo.Album + ''', ''' + form3.BASSPlayer1.StreamInfo.Genre + ''', ''\\' + form3.BASSPlayer1.StreamPath + ''')');
      //zquery4.ExecSQL;
    //  zquery4.Post;
    //  zquery4.ApplyUpdates;
     // zquery4.CommitUpdates;
      //inc(while22);
    end;
  end;
  end;          }

scheurer · Post by **scheurer** » 24.02.2010, 13:19

Hi,

we had the same error message in our applications.
Our solution was a patch in ZAbstractRODataset

change
...
{$IFNDEF FOSNOMETA}
Required := IsWritable(I) and (IsNullable(I) = ntNoNulls);
{$ENDIF}
...

to

...
{$IFNDEF FOSNOMETA}
Required := IsWritable(I) and (IsNullable(I) = ntNoNulls)
and not HasDefaultValue(i);
{$ENDIF}
...

If the error remains, You can test for autoinc columns since autoinc fields
are writeable in Mysql and the unique property flags as required.

....
{$IFNDEF FOSNOMETA}
Required := IsWritable(I) and (IsNullable(I) = ntNoNulls)
and not HasDefaultValue(i)
and not IsAutoIncrement(i);
{$ENDIF}
...

In case of autoinc fields and fields with default values no values
for insert has to be supplied even if fields are not nullable.

K.Scheurer