Update Fields during Excel Export

[psamuels56]

I could use some help with what I thought was going to be an easy program.

My application allows the user to enter a start date and end date to display a grid that would then be Exported to Excel. Works perfectly!

However, I want to update the SQL table to show that those records were exported (e.g. set TableName.ExportDate = Date()). I created a function called "UpdateDate" and set it to execute when I clicked a button on the Navigation Bar of the grid (I'd prefer it to update the field automatically when I do the Excel Export, but as an Alpha newbie coming from MS Access, I'm taking it slowly).

My simple SQL statement is: UPDATE TableName SET TableName.ExportDate = Date(). When I click the button, the "export" occurs, but the field isn't updated.

I've tried adding this statement in my XBasic declarations, but no matter how I tried creating the code, it doesn't work.

If someone can direct me to an Alpha video or thread that gives some suggestions, I'd appreciate it.

FYI - It's tough going from being proficient in one platform to starting from scratch in another. I appreciate the message board and all the help it has offered, so far.

Phil

[pvin2011]

Re: Update Fields during Excel Export

hi, I am stuck with this problem as well. Were you able to figure out how to mark the field after export? Thanks! Pratheep

[Stan Mathews]

Re: Update Fields during Excel Export

Quoting the documentation.

To update data you use the SQL::Connection::Execute() method. Note that the fields to update and the corresponding value are specified in a comma delimited list of name/value pairs.

dim conn as SQL::Connection

dim sql_update as C

dim qry as C

qry = "SELECT customer_id, bill_city, bill_state_region, bill_postal_code FROM customer"

sql_update = "update customer set bill_city = 'Phoenix', bill_postal_code = '08000', bill_state_region = 'AZ' where customer_id = 1"

? conn.open("{A5API=Access,FileName='C:\Program Files\a5v8\MDBFiles\Alphasports.mdb',UserName='Admin'}")

= .T.

? conn.execute(sql_update)

= .T.

[pvin2011]

Re: Update Fields during Excel Export

Hi Stan, I need to update multiple records like this:

sql_update = "update customer set bill_city = 'Phoenix', bill_postal_code = '08000', bill_state_region = 'AZ' where customer_id IN (1,2,3,4)"

XBASIC does not like the query when I use IN (.....)

[Stan Mathews]

Re: Update Fields during Excel Export

Works fine here.

dim conn as SQL::Connection
dim sql_update as C
dim qry as C
qry = "SELECT customer_id, bill_city, bill_state_region, bill_postal_code FROM customer"
sql_update = "update customer set bill_city = 'Phoenix', bill_postal_code = '08000', bill_state_region = 'AZ' where customer_id IN (1,2,3,4)"
? conn.open("{A5API=Access,FileName='C:\Program Files\a5v8\MDBFiles\Alphasports.mdb',UserName='Admin'}")
= .T.

? conn.execute(sql_update)
= .T.

[Jberry]

Re: Update Fields during Excel Export

I feel dumb asking this but where does one put code like this? Is it on the a5w page or in the Events section of the Grid?

Thanks

-John

[Stan Mathews]

Re: Update Fields during Excel Export

Either should work as they both execute xbasic code.

[Jberry]

Re: Update Fields during Excel Export

Thanks Stan

[kingcarol]

Re: Update Fields during Excel Export

I haven't tried this, but to execute your update query right after the export to Excel, I would try doing the export via the OnClick event for a button on the grid. When you add the action to Export to Excel, there is a server side After Export event there. Looks like you would just put the name of your Xbasic function there (the one that has your update query in it).

Here's a screenshot of that feature:
http://screencast.com/t/TYF7FRqh

[pvin2011]

Re: Update Fields during Excel Export

Hi Stan,

The issue I am having is running this code below using arguments.
sql_update = "update customer set bill_city = 'Phoenix', bill_postal_code = '08000', bill_state_region = 'AZ' where customer_id IN (1,2,3,4)"

my code is something like this...

sql_update = "update customer set bill_city = 'Phoenix', bill_postal_code = '08000', bill_state_region = 'AZ' where customer_id IN :CustomerID"

dim args as sql::arguments
args.add("customerID","1,2,3,4")

sql::arguments does not seem to like comma separated values in arguments??

Have you tried this way?

thanks,
Pratheep

[Stan Mathews]

Re: Update Fields during Excel Export

I'll have to have a look in the morning. I've never used this at all. Just read the documentation and experimented with AlphaSports.

[Stan Mathews]

Re: Update Fields during Excel Export

SELECT CUSTOMER_ID, LASTNAME, COMPANY, EMAIL
FROM Customer
WHERE CUSTOMER_ID IN (:Cust_Id)

This is valid in the AlphaSports sample. The issue seems to be with the numeric customer id field.

"1,2,3,4" is not a valid entry for a numeric.

[pvin2011]

Re: Update Fields during Excel Export

So this kind of query is not possible with Alpha5?