Use the database methods to create, update or delete records from the database. ALWAYS reset the database before modifying the database records.
The following table definition will be used for the first 5 examples,
Database.Reset()
Database.AddColumn("ID", intID)
Database.AddColumn("BARCODE", edtBarcode)
Database.AddColumn("DESCRIPTION", edtDescription)
Database.AddColumn("LOCATION", edtLocation)
Database.AddColumn("QTY", edtQty)
intResult = Database.AddRecord("STOCKTAKE")
Note:
intID – a Form Variable, assigned a unique number with NumberGenerator.GetUniqueNumber() method.
edtBarcode, edtDescription, edtLocation, edtQty – Form controls, values entered by the user or the program automatically.
ID, BARCODE, DESCRIPTION, LOCATION, QTY – table columns.
STOCKTAKE – table name.
intResult – variable holding the return value of the AddRecord() method; returns 0 on success or the error code on failure. Use this variable to specify program behaviour on pass or fail.
Always use AddColumn method to create a temporary record before using AddRecord to create the physical record on the local database.
Database.Reset()
intResult1 = Database.SetQueryParam("pStocktakeID", intID)
Database.AddColumn("QTY", edtQty)
IF intResult1 == 0
{
intResult2 = Database.UpdateRecords("qUpdateStocktake")
...
}
Note:
intID – a Form Variable, the unique record ID. Usually derived from a list of Stocktake records using the GetCurrentRowColumnValue method of the listview control.
edtQty – a Form control, a value entered by the user.
pStocktakeID – the query parameter that defines the record to update.
qUpdateStocktake – the query that returns the record to update.
Query definition:Database.Reset()
intResult1 = Database.SetQueryParam("pStocktakeID", intID)
IF intResult1 == 0
{
intResult2 = Database.DeleteRecords("qUpdateStocktake")
...
}
Note:
intID – a Form Variable, the unique record ID. Usually derived from a list of Stocktake records using the GetCurrentRowColumnValue method of the listview control.
pStocktakeID – the query parameter that defines the record to delete.
qUpdateStocktake – the query that returns the record to delete. Same as the query definition in Example 2.
intResult1, intResult2 – variables holding the return values of the methods; returns 0 on success or the error code on failure. Use this variable to specify program behaviour on pass or fail. DeleteRecords() will only be called if the parameter is set successfully via SetQueryParam(), ie. if intResult1 == 0.
Database.Reset()
intResult = Database.DeleteRecords("qStocktake")
Note:
All deleted records will be reflected on the server database.
qStocktake – the query
that returns all the records to delete.
Query definition:
Parent table: STOCKTAKE
Output Fields: all fields.
intResult – variable holding the return value of the DeleteRecords() method; returns 0 on success or the error code on failure. Use this variable to specify program behaviour on pass or fail.
Database.Reset()
intResult = Database.DeleteRecordsLocally("qStocktake")
Note:
Deleted records will not be reflected on the server database. The DeleteRecordsLocally method is only used for housekeeping of the local database. Usually used when deleting records on the local database but not on the server.
qStocktake – the query
that returns all the records to delete.
Query definition:
Parent table: STOCKTAKE
Output Fields: all fields.
intResult – variable holding the return value of the DeleteRecordsLocally() method; returns 0 on success or the error code on failure. Use this variable to specify program behaviour on pass or fail.
// Discover the engine type being used
IF Database.GetDatabaseEngine() == DB_MS_ACCESS
{
strDateDel = "#"
}
ELSE
{
strDateDel = "'"
}
// Format date parameter
// Delete job records locally after 24 hours = 60 sec*60 mins*24 hrs
dtDeleteDate = DateTime.Now() - 86400
strDate = DateTime.Format(dtDeleteDate, "yyyy-MM-dd hh:mm:ss")
// Delete JOBS that are Processed, Returned and Rejected
Database.Reset()
strSql = "DELETE FROM JOB WHERE JOB_STATUS IN (‘Processed’,‘Returned’,‘Rejected’) AND DT_STATE_CHANGED <= "
strSql = strSql & strDateDel & strDate & strDateDel
intResult = Database.ExecuteUpdate(strSql)
IF intResult <> 0
{
Form.MessageBox("Error", "Could not delete processed job records !", MB_ICONERROR)
EXIT
}
Note:
JOB table definition is as follows
MS ACCESS and SQL SERVER CE has several difference in their SQL statements such as the datetime delimiters used. The GetDatabaseEngine allows you to check which local database engine is being used and allows you to change the datetime delimiter (as in this example) before creating the SQL statement.
DB_MS_ACCESS – a system constant value for the MS Access database engine. It can be found under “Data Sources/Constants/system”.
strSql – a string variable that stores the SQL statement to execute. For readability purposes, use the string Concatenate operator to add the SQL statements together.
intResult – an integer variable that stores the result of the ExecuteUpdate method to detect if the operation was successful or not.
The EXIT statement will exit the expression and execution of the rest of the expression will stop.
edtFile = Form.ShowFileOpenDialog()
intResult = Database.ExportToDatabase(edtFile, "EMPLOYEE", "qExportEmp", true)
IF intResult <> 0
{
Form.MessageBox("Export”, "Error in export. Pls check trace file. ", MB_ICONERROR)
EXIT
}
Note:
edtFile – a read-only edit control to display and hold the export file name.
intResult - an integer variable that stores the result of ExportToDatabase method to check if the operation was successful or not.
EMPLOYEE - the table to be exported to.
qExportEmp – the query that defines the columns to export. You must make sure that the query output columns has a matching column in the table to be exported to.
Database.ResetTimestamp("CUSTOMER")
Synchroniser.DisableAll()
Synchroniser.EnableSyncRule("SyncCust", true)
Synchroniser.EnableSyncRule("SyncProduct", true)
Form.ShowSyncDialog(false)
Note:
SyncCust – a sync rule to synchronise CUSTOMER records from the server.
SyncProduct – a sync rule to synchronise PRODUCT records from the server.
Synchroniser methods can be found under “Data Source/Objects/Synchroniser”.
Upon synchronisation, the sync engine will return all records from the CUSTOMER table because the timestamp for this table was reset.
You can also use the ResetTimestamp method on a different expression and link the expression to another button and allow the users to reset the timestamp at will.
edtID = ID
Database.AddColumn("ID", edtID)
Variable1 = Database.LockServerRecord("LOCK_TABLE")
Settings.SettingIntLockID = Database.GetServerLockId()
VarLockOwner = Database.GetServerLockOwner()
IF Variable1 <> 0
{
Form.CanModify = false
Form.MessageBox("Lock Records", "The record is locked. Please wait to modify.", MB_OK)
edtName = NAME
edtSalary = SALARY
}
ELSE
{
edtName = Database.GetLockedRecordColumn("NAME")
edtSalary = Database.GetLockedRecordColumn("SALARY")
Form.CanModify = true
}
Note:
The following expression is linked to the data loading of edtID. This locks each record everytime it is loaded on the cursor form unless the record has already been locked.
If the record has already been locked then modification to the record is disabled.
As can be seen from the flow of the expression, the last record that was locked will be released first before locking the current record.
The NAME and SALARY columns are loaded differently depending on whether the record is locked. If the current user has successfully locked the record, then a call to GetLockedRecordColumn is required to load the values of NAME and COLUMN.