On Thu, Jun 19, 2008 at 8:22 AM, Joost van der Sluis <<a href="mailto:joost@cnoc.nl">joost@cnoc.nl</a>> wrote:<br><div class="gmail_quote"><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Op zaterdag 14-06-2008 om 16:06 uur [tijdzone -0500], schreef Howard Lee<br>
Harkness:<br>
<div class="Ih2E3d"><br>
> However, it doesn't seem to matter what I put in for nbDelete. I<br>
> always get the error "There are no fields found to generate the<br>
> where-clause" if I do ApplyUpdates for nbDelete. If I put nothing in<br>
> there, the field appears to be deleted, but it is still in the table,<br>
> and comes back to the grid when I press "refresh".<br>
<br>
</div>Does your table has a primary key? And do you have set the<br>
'UsePrimaryKeyAsKey' property to true?</blockquote><div><br>Yes, and yes. See the code that I posted at the beginning of the thread -- if you see a problem with the code , please tell me what I'm doing wrong, or not doing right. There is definitely something wrong with it, because it doesn't work. If I left something out, tell me and I can just zip up the whole project and email it to you.<br>
</div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><br>
<br>
sqldb tries to create an delete-query for you, but to do that it has to<br>
indentify the record that should be deleted. Normally it uses the<br>
primary key for that. If there is no primary key, you have to tell it<br>
which fields should be included as a 'key'. (ie: be a part of the<br>
where-statement in the delete query: 'delect from table where ...)</blockquote><div><br>Things would be a lot easier for me if only I could find a working example of a small database program using MySQL and Lazarus. I've found a few examples, but I have been unable to make any of them work. Currently, I'm about to download the latest Lazarus/FPC versions to see if something magically works, but it seems to me that I really ought be able to get a trivial program like the one I posted at the beginning of the thread to work with minimal effort.<br>
<br>Here again is the code that I posted in the first part of the thread:<br></div></div><br clear="all">
To start with, here's a sample database setup:<br>
<br>
CREATE TABLE IF NOT EXISTS ZIPCODE<br>
(<br>
ZIPID INT NOT NULL AUTO_INCREMENT,<br>
ZIP VARCHAR(5) NOT NULL,<br>
ZIPEXT VARCHAR(4),<br>
CITY VARCHAR(40),<br>
STATE VARCHAR(2),<br>
PRIMARY KEY(ZIPID) -- YES! there is a primary key!<br>
);<br>
<br>
INSERT INTO ZIPCODE (ZIP,ZIPEXT,CITY,STATE) VALUES<br>
('01814',NULL,'Lawrence','MA')<br>
,('13502',NULL,'Utica','NY')<br>
,('22030',NULL,'Fairfax','VA')<br>
,('27030',NULL,'Mt. Airy','NC')<br>
,('27103',NULL,'Winston-Salem'<div id="1fol" class="ArwC7c ckChnd">,'NC')<br>
,('27104',NULL,'Winston-salem','NC')<br>
,('27106',NULL,'Winston-Salem','NC')<br>
<br>
I started up Lazarus, created an application, and put a DBGrid,<br>
DBNavigator, a MySQL50Connection, an SQLTransaction, an SQLQuery, and<br>
a DataSource components on it. Here is the Form File:<br>
<br>
object Form1: TForm1<br>
Left = 307<br>
Height = 300<br>
Top = 180<br>
Width = 602<br>
HorzScrollBar.Page = 601<br>
VertScrollBar.Page = 299<br>
ActiveControl = DBGrid1<br>
Caption = 'Form1'<br>
ClientHeight = 300<br>
ClientWidth = 602<br>
Position = poDesktopCenter<br>
object DBGrid1: TDBGrid<br>
Left = 24<br>
Height = 201<br>
Top = 32<br>
Width = 560<br>
DataSource = Datasource1<br>
FixedColor = clBtnFace<br>
FixedHotColor = cl3DLight<br>
Options = [dgEditing, dgTitles, dgIndicator, dgColumnResize,<br>
dgColumnMove, dgColLines, dgRowLines, dgTabs, dgAlwaysShowSelection,<br>
dgConfirmDelete, dgCancelOnExit]<br>
OptionsExtra = [dgeAutoColumns, dgeCheckboxColumn]<br>
ParentColor = False<br>
TabOrder = 0<br>
TabStop = True<br>
end<br>
object DBNavigator1: TDBNavigator<br>
Left = 152<br>
Height = 25<br>
Top = 248<br>
Width = 241<br>
BevelOuter = bvNone<br>
ClientHeight = 25<br>
ClientWidth = 241<br>
DataSource = Datasource1<br>
end<br>
object MySQL50Connection1: TMySQL50Connection<br>
Connected = True<br>
Streamedconnected = True<br>
DatabaseName = 'test'<br>
Password = 'test'<br>
Transaction = SQLTransaction1<br>
UserName = 'root'<br>
left = 24<br>
top = 9<br>
end<br>
object SQLQuery1: TSQLQuery<br>
Active = True<br>
Database = MySQL50Connection1<br>
Transaction = SQLTransaction1<br>
SQL.Strings = (<br>
'select * from zipcode'<br>
'order by zip'<br>
)<br>
Params = <><br>
UpdateMode = upWhereChanged<br>
UsePrimaryKeyAsKey = True // YES! UsePrimaryKeyAsKey is set to True!<br>
ParseSQL = True<br>
left = 24<br>
top = 44<br>
end<br>
object SQLTransaction1: TSQLTransaction<br>
Database = MySQL50Connection1<br>
left = 59<br>
top = 9<br>
end<br>
object Datasource1: TDatasource<br>
DataSet = SQLQuery1<br>
left = 59<br>
top = 44<br>
end<br>
end<br>
<br>
I did not add any code at all to the main PAS file, so the project and<br>
source are as follows:<br>
<br>
program zipcode;<br>
{$mode objfpc}{$H+}<br>
uses<br>
{$IFDEF UNIX}{$IFDEF UseCThreads}<br>
cthreads,<br>
{$ENDIF}{$ENDIF}<br>
Interfaces, // this includes the LCL widgetset<br>
Forms<br>
{ you can add units after this }, zipcode, SQLDBLaz;<br>
begin<br>
Application.Initialize;<br>
Application.CreateForm(TForm1, Form1);<br>
Application.Run;<br>
end.<br>
<br>
+++++<br>
<br>
unit Zipcode;<br>
<br>
{$mode objfpc}{$H+}<br>
<br>
interface<br>
<br>
uses<br>
Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs,<br>
mysql50conn, sqldb, db, DBGrids, DbCtrls;<br>
<br>
type<br>
{ TForm1 }<br>
TForm1 = class(TForm)<br>
Datasource1: TDatasource;<br>
DBGrid1: TDBGrid;<br>
DBNavigator1: TDBNavigator;<br>
MySQL50Connection1: TMySQL50Connection;<br>
SQLQuery1: TSQLQuery;<br>
SQLTransaction1: TSQLTransaction;<br>
private<br>
{ private declarations }<br>
public<br>
{ public declarations }<br>
end;<br>
<br>
var<br>
Form1: TForm1;<br>
<br>
implementation<br>
<br>
initialization<br>
{$I zipcode.lrs}<br>
end.</div><br>BTW, I did get rid of the exception on close by adding an event handler for formclose that closes the database. And I was able to get an insert to work by adding an event handler to the navigator component that did SQLQuery1.Appyupdates in response to nbPost button click. However, delete and edit still do not work.<br>
-- <br>Howard Lee Harkness<br><a href="http://www.celtic-fiddler.com">www.celtic-fiddler.com</a><br>