I am new to powerbuilder and having a lot of trouble using datawindows. I can't get my head around the insert mechanism of it. Can someone explain it to me, or at least point me in the right direction(article, tips etc...) ?
+7
A:
The SQL generated by the DataWindow is controlled by the Update Properties (menu item Rows / Update Properties...). From there, you can select a single table and set the properties that it will use to generate the SQL. Where Clause... determines which columns and their original values are used in the WHERE clause for UPDATEs and DELETEs. Key modification only comes into play when you change (or allow the user to change) columns that you've defined as key. Updatable Columns is the list of what will generate SQL (this doesn't affect what the UI allows one way or the other). Key columns can be the Primary key (which can be auto-populated with the Primary Key button, if you're DBMS supports the calls to query that from the database), but it doesn't have to be. (There are fairly unique cases where you might want to do something different.) If you've got an identity column as your key, you can identify that to your DataWindow and it will retrieve the generated value after an INSERT.
After that, populating the data in the DataWindow with InsertRow(), DeleteRow(), SetItem() and, of course, letting the user at the UI, will be changing data and status flags that will determine the SQL generated when Update() is called.
All these can be changed at run time with the Modify() function, so you can do things like update multiple tables with one DataWindow. This is implemented in PowerBuilder Foundation Class's multi-table update service, so if you ever want to change these values at run time, that's some good sample code.
Good luck,
Terry.
Terry
2010-06-14 14:26:32
Suppose I have a table called 'test', and it has three columns, named 'test_id', 'test_name', 'test_address'. I am using a retrieval argument on 'test_id' to fetch the data from the table to filter data,and I am only displaying the later two columns. Now when I insert a new row using the data window, it says that I am violating the 'not-null' constraint on the database, that it couldn't find a value for the 'test_id'. How can I solve this problem?
Night Shade
2010-06-15 04:57:00
Depends. If test_id is an identity/auto-increment column, you just need to declare that in the Update Properties. If it's a value you populate yourself, you'll have to programmatically do a SetItem() on the new row for column "test_id". The rules for generating the value may determine whether you can set the value at the same time as InsertRow(), or on the UpdateStart event. If you want to use ModifiedCount() to determine if a save is necessary, you might want to postpone the SetItem() until after that function call. (For bonus marks, Modify() the DW column's Initial attribute... no spc2xpln.)
Terry
2010-06-15 14:38:29
+2
A:
@Archangel You still need to provide the test_id field when inserting the row into the datawindow even after you specified a value for it as a retrieval argument. After you do your insertrow, make sure you do a setitem for all values which do not allow null values.
Jay
2010-06-15 11:37:37