Primary Key Column
Normally, the key column should not be associated with a tag for data upload. However, in some applications of CitectSCADA , the value of the key column is required to be downloaded to a tag. In this case, using the tag value to update or append a record will cause the error of key violation. To avoid this error, detection of the key column is implemented in the control. The tag value associated with the key column will be ignored on data uploading.
Column data in the control buffer
A data buffer is created in the control to store the data from the selected record for downloading or the data from the property changes for uploading. The data buffer won’t be reset when the record source property is changed.
Tag Associations
As mentioned in above section, the data buffer acts like a bridge between the column properties and a data source. When a download action is performed, the control will pass the data of the selected record to the data buffer and raise the event DataDownloading. CitectSCADA will catch this event and update the associated tags with the column property values. It is noted that getting the value of a column property is actually reading from the buffer.
Bear in mind that CitectSCADA updates all associated properties on instantiation of an ActiveX control with associated tags and thereafter updates those properties whose associate tags change their values.
Care must be taken if you change the data source, and then perform an upload action after the control has already been instantiated. For example, the control is configured with a number of associated tags and its data source is configured at design time. Then the data source is changed by a user at runtime. In this case, the data buffer will still store the data relating to the previous data source. If a data upload is performed at this point, incorrect data will be uploaded to the current data source.
To work around this issue, use Cicode function _ObjectSetProperty to update each column property directly with the value of its associated tag and then perform data upload (see the details in the next section). This improves the probability that the correct data will be uploaded.
Alternatively, perform a download first after the data source changes, which will fill the data buffer with the relevant data. And then change values of some associated tags and upload them. In this case, the original column data that remains unchanged won’t be overwritten by incorrect values.
Using Cicode to call method UploadData
An example here is to show how to add a new record in Cicode back to the data source. It is assumed that a data table has three columns, ShipperID (Primary Key), CompanyName and Phone as shown in the following grid.
As the first column is the primary key, there is no need to pass its value to the control. So “Column001” should not be included in the following Cicode function as the data engine will generate a new ID for this record anyway. It is noted that the page containing the control must be open while this function is called otherwise a hardware alarm will be generated. If no row is selected on GUI, a new record will be appended to the data source or the selected row’s data will be updated.
It is also noted that if the method of UploadData is called again, the control will use the same data in its buffer to add a new record or update the selected one.
FUNCTION AppendTagData(STRING sAN, INT ID, STRING sCompanyName, STRING sPhone)
OBJECT oDataManager;
oDataManager = ObjectByName(sAN);
_ObjectSetProperty(oDataManager, "Column002", sCompanyName);
_ObjectSetProperty(oDataManager, "Column003", sPhone);
_ObjectCallMethod(oDataManager, "UploadData", -1);
END
It needs to point out that in the above example no tag association is required since the values are directly passed to the column properties.
No key column in a data source
When a source table doesn’t have a primary key defined, it is fine to add a duplicated record to the table with DatabaseExchange control. The problem arises when one of duplicate records gets modified manually or updated via data upload. The following message will display. This is a known issue.
For example, Recipes.dbf is linked with DatabaseExchange control and has four duplicate records as shown in the grid below where the duplicate records are highlighted. As it can be seen, the value of “333” in the red circle is the new entry in “MILK” column. When the control attempts to update the record, the above error will occur as ADO cannot locate the record that needs to be updated.
Once in this situation, you have to close the graphics page to suppress this error. The result is that all duplicate records have been updated (see the grid below).
It should also be noted that deleting one of the duplicate records will end up deleting all of them. First you will receive the following message when trying to delete one of the duplicate records. After acknowledging the popup message and refreshing the data source, you will see all duplicated records of this instance are deleted.
To avoid this problem, the data source should have a primary key column or use some other means to ensure that each record is unique.