onBeforeSQLCommandExecute in Grid, Getting Data from Controls

[10001]

I'm sure that this is a really basic question to those who know, but I can't find an answer anywhere. I'm trying to use the onBeforeSQLCommandExecute server side event in order to customize how the SQL is written to my database. However, I can't figure out how to fill my arguments with the data from the grid fields.

I tried args.set("arg1", e.datasubmitted.<fieldname>) and args.set("arg1", e.data.<fieldname>), as well as a number of other permutations, with no luck. Would appreciate if someone would point me in the right direction. Thanks.

[Lance Gurd]

Re: onBeforeSQLCommandExecute in Grid, Getting Data from Controls

first you need to dim the args as in

Dim args as sql::arguments

Then you use args.add("arg1",e.dataSubmitted.myfiledname), don't forget that the data is always character, therefore if your arg needs to be some other data type, use convert_type() to change it.

[10001]

Re: onBeforeSQLCommandExecute in Grid, Getting Data from Controls

Still not working. I get the following error when I submit:

An error occurred in the onBeforeSQLCommandExecute event handler. Error reported: command: args.add("WGID", e.datasubmitted.workgroupid) Property not found
e.datasubmitted subelement not found.

Here's the code:

dim cn as sql::Connection
dim args as sql::Arguments
dim sqlcall as c
dim flag as l

e.cn.PortableSQLEnabled = .f.
e.SQLCommandWasHandled = .t.

args.Clear()
args.add("WGID", e.datasubmitted.workgroupid)
args.add("EmpID", e.datasubmitted.employeeid)
args.add("CrBy", e.datasubmitted.createdby)

Is there something besides e.datasubmitted, like an e.data-to-be-submitted, since it hasn't actually be submitted yet?

[Lance Gurd]

Re: onBeforeSQLCommandExecute in Grid, Getting Data from Controls

Yes sorry those events handle the data that is submitted a whole different way! Go to the videos here and search for SQLCommandExecute, this will bring you to video 78 or SSE12 which has 6 video's all about these events

[10001]

Re: onBeforeSQLCommandExecute in Grid, Getting Data from Controls

Lance,

I watched those videos last night and did not see where they address the topic of arguments. The example used in the videos does not utilize arguments at all. I'm still completely at a loss here. This functionality does not seem to be documented anywhere.

[Jim Coltz]

Re: onBeforeSQLCommandExecute in Grid, Getting Data from Controls

The example used in the videos does not utilize arguments at all. I'm still completely at a loss here. This functionality does not seem to be documented anywhere.

The documentation is mostly in the function prototype. Argument handling is explained as so:

'e.argsXML = The XML for the SQL::Arguments object with value for the arguments used in the statement
'e.argsXML contains the argument values for the data that was submitted when the user clicked the submit button. 
'e.argsXML contains the new and old values for all fields on the Grid.
'Note: To convert e.argsXML into a sql::arguments object you would do:
'		dim myargs as sql::arguments
'		myargs.xml = e.argXML

'If your Grid sets arguments for the initial Query, these argument values are stored in XML format in:
'e.rtc.ArgumentsQuery.XML

So, in your example you would use:

dim cn as sql::Connection
dim args as sql::Arguments

args.xml = e.argXML

dim sqlcall as c
dim flag as l

e.cn.PortableSQLEnabled = .f.
e.SQLCommandWasHandled = .t.

However, the grid doesn't have e.datasubmitted available so you now you have to find the values you want to set your arguments to. These are in e.rv.V.Rwhaterver the current row number is. You have some more work to do to harvest the values to put into your arguments. Everything you need is availiable. Finding it and figuring out how to get it is where the fun starts.

Also, unless you need a new connection you can just use the e.cn sql connection object that already exists and not bother with "dim cn as sql::Connection".

[DaveF]

Re: onBeforeSQLCommandExecute in Grid, Getting Data from Controls

I don't believe the "e.DataSubmitted" structure is used in this particular event. I use "request.variables.V.R(row_number).(field_name)". Enter "request.variables.V" in the watch variables section of the debugger and expand the row number variable. You should see your field variables. The row number is stored in "e.rowNumber".

Edit: Jim's post beat mine. You can use either "request.variables.V" or "e.rv.V". The row data is stored in both constructs, but the documentation in the comments section says we should start using "request.variables".

[Jim Coltz]

Re: onBeforeSQLCommandExecute in Grid, Getting Data from Controls

Yes, we should be using the request object instead of e.rv.V. (old habit)

[10001]

Re: onBeforeSQLCommandExecute in Grid, Getting Data from Controls

Thanks for the help. By the way, e.argXML did not work for me, it's apparently e.argsXML. Working code below:

dim cn as sql::Connection = e.cn
dim args as sql::Arguments

args.xml = e.argsXML

dim sqlcall as c
dim flag as l

e.cn.PortableSQLEnabled = .f.
e.SQLCommandWasHandled = .t.

args.add("WGID", request.variables.V.R_1.workgroupid)
args.add("EmpID", request.variables.V.R_1.employeeid)
args.add("CrBy", request.variables.V.R_1.createdby)