Update Table doesn't seem to work

[gkelby]

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]

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').AsInteger := 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]

[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]

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:

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.

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...