Update Table doesn't seem to work
lazarus / 16-Databases

----------------------------------------------------------------------
gkelby · 2014-03-23 08:00

Hi:

I have a DBGrid that I load data from a table and it all works fine. 
I am able to add rows to the grid and update the table.  The problem
is that I've not been able to find a way to provide a constant value
to a particular column in the DB table through the code or by setting
a grid column to a particular value.  So instead, I've resorted to
updating the table from the grid then trying to apply an SQL "Update
Table" command to essentially insert a particular value into the table
column.  I've used SQLdb Tutorial 3 as a guide (page 8 of 9 if you
print the tutorial) but it does not seem to work.  Here's what the
code looks like...

     DM2.SQLQuery1.Close;
     DM2.SQLQuery1.SQL.Text := 'UPDATE Allowances SET ProfileID =
:SelectedProfileID WHERE ProfileID = 0';
     DM2.SQLQuery1.Params.ParamByName('SelectedProfileID').AsString :=
SelectedProfileID;
     DM1.SQLTransaction1.StartTransaction;
     DM2.SQLQuery1.ExecSQL;
     DM1.SQLTransaction1.Commit;
     DM2.SQLQuery1.Close;

Suggestions would be greatly appreciated

Thanks
GK

----------------------------------------------------------------------
BigChimp · 2014-03-23 08:22

Do you see an error message? What does it say?

In your SQL, you have a WHERE clause where ProfileID seems to be an
integer. However, you treat the parameter as a string type.
I'd change it to (untested):
Code: [Select]DM2.SQLQuery1.Params.ParamByName('SelectedProfileID').As
Integer := StrToInt(SelectedProfileID); //if SelectedProfileID really
is a string type variable

No need for the SQLQuery1.Close statements; these are used only when
.Opening result datasets, not when running .ExecSQL

Apart from that it should just work...!?

----------------------------------------------------------------------
gkelby · 2014-03-23 23:47

[Solved] Thanks for the suggestion.  I had used "AsInteger" before but
it wouldn't compile as I was not using "StrToInt(SelectedProfileID)". 
Once I added the "StrToInt(SelectedProfileID)" it did compile and
ran.  Unfortunately the ProfileID field in the table still wasn't
updating.  Given your suggestion should have worked, I began to wonder
about the "ProfileID" field itself.  It is defined as being an Integer
and Null and that's the clue.  In the update statement it's specified
as being "=0", when in fact it's actually Null as it has no value
assigned.  Once the Update Statement was changed to "UPDATE Allowances
SET ProfileID = :SelectedProfileID WHERE ProfileID is Null" it all
worked.  Seems so simple.  Blasted Nulls, there's a good reason why
it's highly recommended not to use them.

Thanks for your help, I appreciate it.  Now I can move on to the other
grids and tables.

Regards...
Gk.

----------------------------------------------------------------------
BigChimp · 2014-03-24 07:51

Glad you got it going.

Well, there's a good reason why it's highly recommended to *use* NULLs
in databases when appropriate: when you don't know what value
something is.
Depends on your level of experience/comfortability with relational
databases I suppose

BTW, often I get stuck with SQL statements in code, I get it to print
out the SQL and try and run that in a db management tool to see what's
going on... (of course with backups of the data etc). Sometimes I see
very stupid mistakes....

Finally, I'd be interested in the "proper" solution for your original
problem:
QuoteThe problem is that I've not been able to find a way to provide a
constant value to a particular column in the DB table through the code
or by setting a grid column to a particular value.
Perhaps using this event for your query could be useful to remove the
need to fiddle with manual update statements:
http://www.freepascal.org/docs-html/fcl/db/tdataset.afterinsert.html

... there might well be a more elegant solution...