How can I use a DBgrid so that I can send an INSERT or UPDATE to the DB, and have the grid refresh itself automagically?
It seems like a Query object is used to send a SELECT to the database object, while changes must go through an UpdateSQL object, but I'm not clear at how it works. Could someone share a basic sample on how it works?
Geregistreerd: wo jan 25 2006, 04:34 Woonplaats: groningen (NL) berichten: 1561
AFAIK updatesql is used a lot and actually it is based upon the documentation of the VCL. So any sample of the VCL should work. Fact is that I don't have all kind of samples to work with the components. albert
Geregistreerd: ma apr 30 2007, 10:02 berichten: 84
Thanks for the help. I'm reading Bob Swart's book on Delphi + Databases, but don't find the following property in the ASQLite3Query object:
"Note the syntax for the parameter: a colon specifies that this is a parameter name (usually, but not necessarily with the same name as the column it needs to connect to). After we’ve specified the SQL contents, we need to specify the DataType and ParamType properties of the parameter. Double-click on the Params property of the qBorrowFriend TQuery command to get the Parameters Editor. Figure 7. TQuery Parameters Editors For each parameter – in this example only one of course – we must set the ParamType to input or output (ptInput in this case) and the DataType to the corresponding column type (ftInteger in this case). It may also be useful to specify a default value and type (set to Integer and 0 for example), so you can open the qBorrowFriend at design-time if you wish."
Is it the equivalent of double-clicking on the object, right-clicking in the dialog, and choosing "New Field..."?
Geregistreerd: wo jan 25 2006, 04:34 Woonplaats: groningen (NL) berichten: 1561
I realy don't understand your question. The Params property is there: with MyQuery do begin sql.text := 'select * from xxx where a=:a'; params[0].AsString := 'something'; open; while not eof do begin x := FieldByName('something').AsString; next; end; end;
Geregistreerd: ma apr 30 2007, 10:02 berichten: 84
Sorry, I was looking for a Params property for a ASQLite3Query object in the Object Inspector.
One last question, though: If I can perform updates + refresh through a Query objct, when should I use an UpdateSQL object? I don't understand why Borland provided two objects.
FWIW, depending on applications, I'll use data-aware grids or non-data-aware grids.
Here's an example of what I'd like to do (use a Query to display a SELECT into a DBgrid, and let the user add a record by pushing a button):
procedure TForm1.Button1Click(Sender: TObject);
begin
With ASQLite3Query1 do begin
//Close;
SQL.Text := 'INSERT INTO books (isbn,language) VALUES (:a,:b)';
params[0].AsInteger := 234;
params[1].AsString := 'something';
//Exception class EDatabaseError with message 'SQL logic error or missing database'.
//ExecSQL;
//Open;
end;
end;
procedure TForm1.FormActivate(Sender: TObject);
begin
With ASQLite3DB1 do begin
DefaultDir := ExtractFileDir(Application.ExeName);
Database := 'mybooks.sqlite';
Open;
SQLite3_ExecSQL('CREATE TABLE IF NOT EXISTS books (isbn INTEGER PRIMARY KEY, language VARCHAR)');
//SQLite3_ExecSQL('INSERT INTO books (isbn,language) VALUES (123,"english")');
end;
With ASQLite3Query1 do begin
Connection := ASQLite3DB1;
SQL.Text := 'SELECT * FROM books';
Open;
end;
DataSource1.DataSet := ASQLite3Query1;
With NextDBGrid1 do begin
DataSource := DataSource1;
//Have the right-most column fill remaining space
Columns[ASQLite3Query1.FieldCount-1].Options := NextDBGrid1.Columns[ASQLite3Query1.FieldCount-1].Options + [coAutoSize];
end;
Geregistreerd: ma apr 30 2007, 10:02 berichten: 84
I'm going through the samples, and was wondering 1) why there's a need for two Query objects, each pointing to a shared UpdateSQL object, and 2) why the INSERT string is mentionned twice, in the Query and in the UpdateSQL:
object ASQLite3UpdateSQL1: TASQLite3UpdateSQL
InsertSQL.Strings = (
'insert into animal *')
end
[...]
procedure TForm1.Button1Click(Sender: TObject);
begin
with ASQLite3Query2 do begin
Close;
SQL.Clear;
SQL.Add('insert into animal (id, desc) values (:v1, :v2)');
Params[0].AsString := '99';
Params[1].AsString := 'ninetynine';
ExecSQL;
end;
end;
Geregistreerd: ma apr 30 2007, 10:02 berichten: 84
Here's the latest:
procedure TForm1.FormActivate(Sender: TObject);
begin
//1. Create database and table
With ASQLite3DB1 do begin
DefaultDir := ExtractFileDir(Application.ExeName);
Database := 'mybooks.sqlite';
Open;
SQLite3_ExecSQL('CREATE TABLE IF NOT EXISTS books (isbn INTEGER PRIMARY KEY, language VARCHAR)');
end;
//2. Configure update
With ASQLite3UpdateSQL1 do begin
InsertSQL.Add('INSERT INTO books *');
end;
//3. Configure SELECT, and link query to update object
With ASQLite3Query1 do begin
Connection := ASQLite3DB1;
SQL.Text := 'SELECT * FROM books';
UpdateSQL := ASQLite3UpdateSQL1;
Open;
end;
//4. Glue to connect DBgrid to dataset
DataSource1.DataSet := ASQLite3Query1;
//5. Fill DBgrid
With NextDBGrid1 do begin
DataSource := DataSource1;
Columns[ASQLite3Query1.FieldCount-1].Options := NextDBGrid1.Columns[ASQLite3Query1.FieldCount-1].Options + [coAutoSize];
end;
end;
procedure TForm1.Button1Click(Sender: TObject);
var
InsertData : TStrings;
begin
//6. Add record
with ASQLite3Query1 do begin
Close;
SQL.Clear;
SQL.Add('INSERT INTO books (isbn,language) VALUES (NULL,:a)');
Params[0].AsString := 'test';
//Does add record, but raises error afterward
//Project1.exe raised exception class EAccessViolation with message 'Access violation at address 004DB408
ExecSQL;
end;
end;
Geregistreerd: wo jan 25 2006, 04:34 Woonplaats: groningen (NL) berichten: 1561
The UpdateSQL is used to store the three sqlstatements needed for genreting a live query on data-aware components. Since the query is copied into the sql statement, the select is lost and need to be refreshed. albert
