KB-31207: Commands to insert, edit, and delete records in a database.

Question:

How can I insert, edit and delete records of a database via E3?

Solution:

Via E3Studio, access the Query’s properties and then, on SQL tab, enable SQL’s edition option, and use SQL’s INSERT, UPDATE, and DELETE clauses. Via scripts, use the SetVariableValue() method to set values to the Query’s variables.

Example with INSERT command:

INSERT INTO Table1 (Batch, User, InitialDate)
VALUES (‘<% Batch%>‘, ‘<%User%>‘, #<%InitialDate%>#)

Example with UPDATE command:

UPDATE Table1
SET Table1.FinalDate = #<%FinalDate%>#
WHERE (Table1.ID = <%ID%>)

Example with DELETE command:

DELETE FROM Table1
WHERE Table1.ID = <%ID%>

Example of script (being executed in a button, for example) that attributes value 123 to ID variable and executes the Query’s SQL command:

set Query1 = Application.GetObject(“Data.Query1”)
Query1.SetVariableValue “ID”, 123
Query1.Execute(True)