How do you reference fields on a dialog?

[richardurban]

What is the correct way to reference fields on a dialog to use in an update statement. I want to take the date value from a dialog control and use that in the update statement. The dialog is not data bond. I have tried various ways, but I keep getting the error "not a valid entry handle". here is the code, where "ref_date" is the name of the variable (control) in the dialog.

sql command = "update products set sale_flag= If(session.ref_date >= SALE_BEGIN_DATE and session.ref_date<=SALE_END_DATE,'Sale','');"

[Davidk]

Re: How do you reference fields on a dialog?

In either the onDialogValidate event or your own Ajax Callback you'd reference the 'e' object... as in...

e.dataSubmitted.CUSTOMERID

holds the value for the CustomerId control on your UX

Whenever in doubt, put a debug(1) statement into your code and then when you run your code and you're in the debugger, enter 'e' - without quotes - in the data entry box below the heading "Expression"

That will show you what's contained in the e object.

You'll need to either concatenate your sql statement together (yuck) or use sql arguments (yay).

If you're not sure about sql arguments do a search in this forums for the keyword args with the username davidk. I remember writing a lot of samples using args.

[richardurban]

Re: How do you reference fields on a dialog?

This works fine in Heidi SQL

Dim cn as sql::connection 


flag = cn.open("::Name::urbangrocery")
dim sqlCommand as c

sql command = "update products set current_price= If('2013-10-02'>=SALE_BEGIN_DATE and '2013-10-03'<=SALE_END_DATE,SALE_PRICE,NPRICE2);"
flag = cn.execute(sqlCommand)

But causes the error "Not a valid entry handle" when run in Alpha:updateprod error1.JPG
Can anyone help with this problem? I thought the issue was the variable, but even with the hard coded value, I get this error.

[Davidk]

Re: How do you reference fields on a dialog?

Hey Richard,

I can't see how that would work in any SQL interface. But... if you mean that the actual sql statement...

update products set current_price= If('2013-10-02'>=SALE_BEGIN_DATE and '2013-10-03'<=SALE_END_DATE,SALE_PRICE,NPRICE2)

works... then yes... I get that. It looks a bit backward to me... but what the heck... it works.

The only problem is your assignment of the sql statement to the variable.

You have sqlcommand dimmed, but you're assigning the statement to sql command, and then executing sqlcommand.

This is where I'd hit my keyboard with my head

[Tim Kiebert]

Re: How do you reference fields on a dialog?

Unless it is forum typo you have a space between 'sql' and 'command' in the assignment line

sql command = "update products set current_price= If('2013-10-02'>=SALE_BEGIN_DATE and '2013-10-03'<=SALE_END_DATE,SALE_PRICE,NPRICE2);"

Edit: Just too slow :)

[richardurban]

Re: How do you reference fields on a dialog?

Tim, you are right. That space was causing the error message. Ooops!

[Tim Kiebert]

Re: How do you reference fields on a dialog?

Oh good. Actually David beat me to the punch. :-)

[richardurban]

Re: How do you reference fields on a dialog?

Yes, thank you David, also.
Regarding the original question of referencing the variable in the dialog, the following function now works:

function setcurrentprice as c (e as p)
	
Dim cn as sql::connection 
Dim args as SQL::Arguments
args.set("ref_date",convert_type(e.datasubmitted.ref_date,"D"))
flag = cn.open("::Name::urbangrocery")
dim sqlCommand as c
sqlCommand ="update products set sale_flag='';"
flag = cn.execute(sqlCommand)

sqlcommand = "update products set sale_flag= If(:ref_date >= SALE_BEGIN_DATE and :ref_date <= SALE_END_DATE,'Sale','');"
flag = cn.execute(sqlCommand,args)

sqlcommand = "update products set current_price= If(:ref_date >= SALE_BEGIN_DATE and :ref_date <= SALE_END_DATE,SALE_PRICE,NPRICE2);"
flag = cn.execute(sqlCommand,args)

sqlcommand = "update products set new_field1 = if(unit='',cast(CURRENT_PRICE as char),CONCAT_WS(cast(CURRENT_PRICE as char),'/',unit))"
flag = cn.execute(sqlCommand)

cn.Close()
end function

A couple of notes: You need to be sure to include a space before and after the <= operator, and you need to convert the data submitted type to date.