MySQL: After INSERT my DBGrid is empty :-(

The_Unknown · Post by **The_Unknown** » 14.12.2009, 19:29

Hello,

at the moment I try to realize a MySQL-database in a Delphi program. For this I put the following components onto my form: ZConnection (mySQL-configuration), ZQuery, DataSource and DBGrid (and some buttons).

With the following code I connect with the database:

Code: Select all

ZConnection1.Connected := True;
ZQuery1.SQL.Text := 'SELECT * FROM customers;';
ZQuery1.Active := True;

That works just fine. After that I wanted to create a new line (record?) in the table:

Code: Select all

ZQuery1.SQL.Text :=
'INSERT INTO `customers` (`First`,`Last`,`eMail`,`Street`,`ZIP`,`PCity`) VALUES'+
'(''1'', ''2'', ''3'', ''4'', ''5'', ''6'');';
ZQuery1.ExecSQL;

The one thing that works is: the new line is inserted correctly. But after the click onto the button the DBGrid is empty.

Could you please help me out here?

Thanks in forward.

Bye The_Unknown (sorry for the bad English

)

haentschman · Post by **haentschman** » 14.12.2009, 21:18

Hi...

after the INSERT you must refresh your Query to load the changes from the database.
You are shure that you correctly set the properties from the Grid and the Datasource ?

The_Unknown · Post by **The_Unknown** » 14.12.2009, 21:42

And how should I do that? ZQuery1.Refresh; gives me an exception :-(

haentschman · Post by **haentschman** » 14.12.2009, 22:10

Take two Queries, one for the Data (Query1) and one for the Insert Statment (Query2). Then you can make a Query1.Refresh because the SQL in the Query1 ist the same before. The other way is you take the SQl "SELECT * from....." after the INSERT back into Query1 and open the Query again.

Post by **mdaems** » 16.12.2009, 22:18

Actually, the best way to do this is by just appending a line to the ZQuery object like this: (syntax not checked)

Code: Select all

ZQuery.append;
ZQuery.FieldByName('first').Asinteger := 1;
...
ZQuery.post;

That way the ZQuery component builds the sql needed to insert the data.

The other way is what you did, but requerying afterwards:

Code: Select all

ZQuery1.SQL.Text := 
'INSERT INTO `customers` (`First`,`Last`,`eMail`,`Street`,`ZIP`,`PCity`) VALUES'+ 
'(''1'', ''2'', ''3'', ''4'', ''5'', ''6'');'; 
ZQuery1.ExecSQL; 
ZQuery1.SQL.Text := 'SELECT * FROM customers;'; 
ZQuery1.Active := True;

This is not very efficient, however as the refresh is done to fill the query object with only on more row, the one you've just inserted.

What happens in my example:
- You query the database and fill the ZQuery object with data
- Data is shown in the grid, linked to the ZQuery data buffers
- A row of data is added to the buffer by the Append statement
- The grid is automatically updated from the buffer
- The post sends an insert statement to the database

What happens in your example:
- You query the database and fill the ZQuery object with data
- Data is shown in the grid, linked to the ZQuery data buffers
- You clear the data buffer of the ZQuery object by ExecSQL. (which sets Active:=false)
- The record is inserted in the database
My second solution adds
- You query the database again and fill the ZQuery object with data
- Data is shown in the grid, linked to the ZQuery data buffers

Actually, you should study the database handling that's standard in Delphi using TQuery objects. The 'Using databases' (or something alike) chapter is quite good and clear on the basic concepts of using datasets.
(Just to make you even more curious : apart from Append, there's also Edit and Delete which do all the work for you)

Mark