Tuesday, October 31, 2017

Update SQL database after printing report

October 31st 2017

So you know people who use alpha native DBF for their database needs are able to update the table after printing the report by using simple four lines of code and wondering if that can be done for the SQL database too.
Of course you can do it. In fact it can be done more than one way.
I am going to take one method in each post and discuss how that can be achieved.

Today I am going to show you how you can update using sql connection string and sqldatasource for the report.
For the purpose of this post the table contains a field vintage, and i am going to update that field with an arbitrary value to show the point. The report has a filter where the prompt ask for the name of the brand of the wine and prints the report then updates the vintage in the table to a year specified.  In a real world example you would print invoices to many people and at the end update the table to show date of printing and the fact the invoice was printed to set a flag to true.

So here is how it is done:
Step 1: Create a report based on the sqldatasource as usual. add any where clause and order clause as needed. Test run the report and see if all works okay. Once satisfied save the report.
Step 2: Now go to the top and select report, then report events, then onPrintInit enter the following code:

dim SHARED cn as sql::Connection
dim result as l = .f.
result = cn.Open("::Name::claretPOS")
if .not. result then
end
end if 

Then go to onPrintexit enter the following code:

dim SHARED cn as sql::Connection
if cn then
cn.Close()
end if

Then go to Reports, Section events, detail, onRecord, there enter this code:

dim t as p
dim SHARED cn as sql::Connection
dim args as sql::Arguments
t = table.current()
dim id as n
id = t.ID

dim result as l = .f.
dim sqlUpdate as c
sqlUpdate = "UPDATE inventory SET vintage = 2000 WHERE id = :newID"
args.set("newId", id)
result = cn.Execute(sqlUpdate, args)

As you can see I am showing my connection string and my table information, you need to adjust to yours.
Also I want to emphasize that you need to dim in each segment, then it will look for SHARED namespace.
And you can have your own update statement, not necessary to have the way I have, this is to show  as an example without corrupting my data.
So what happens is when the detail section is printed the id of that record is harvested and sqlUpdate statement is called and the backend is updated.  This will work every time you call this report and if your intenstion is to update then it will happen.
So here is an example:

http://www.youtube.com/watch?v=0vyh34jiWXE