mysql data to web dialog component

[Walter]

Hi everyone,
Please assist me with linking data from a mysql select statement to a web dialog component. First time working with mysql and web dialogs.
I would like to filter the mysql tbl with a ulink value and assign the results to the controls on the dialog.
I have added 2 controls so far to test.
I also managed to dump the result into a variable called "vResult" but would like to assign the values to the controls individually.
I have tried CurrentForm.controls.bk_no.value = rs.bk_no - but not working.
There is also a problem in the "WHERE (IMP_ASG = :vUserNo) , if I replace vUserNo with "10010001" then the statement works good.

Here is my code so far:

If eval_valid(session.__protected__pUser.ulink) then
    Dim vUserNo as C
    vUserNo = session.__protected__pUser.ulink
end if

'Execute a query on a SQL database
'DIM a connection variable
DIM cn as SQL::Connection
dim flagResult as l 
flagResult = cn.open("{A5API=MySQL,Server='00.00.00.000',Port='3306',UserName='username',Password='password',Database='mysql db'}")
if flagResult = .f. then 
	'ui_msg_box("Error","Could not connect to database. Error reported was: ' " + crlf() + cn.CallResult.text)
	'end 
end if

'Specify if you are using Portable SQL syntax, or not
cn.PortableSQLEnabled = .t. 
dim sqlStatement as c 
sqlStatement = <<%sql%
SELECT BK_NO, BK_AMT, BK_RSNME, BK_UNDESC, BK_CNTRY, BK_AREA, BK_ASGPREF, BK_NOTE, BK_CNF, IMP_ASG  FROM bkng  WHERE (IMP_ASG = :vUserNo) 
%sql%
'Execute the Query
flagResult = cn.Execute(sqlStatement)
if flagResult = .f. then 
	'ui_msg_box("Error",cn.CallResult.text)
	cn.close()
	'end 
end if 
dim rs as sql::resultset
rs = cn.ResultSet
'Now, dump the contents of the ResultSet to a variable
dim vResult as c 
vResult = rs.ToString()

'Now, close the connection 
cn.close()

'assign values to this dlg:
CurrentForm.Controls.userid.value = session.__protected__pUser.ulink
CurrentForm.controls.bk_no.value = vResult
CurrentForm.Controls.bk_amt.value = "result from tbl"

Any help would be appreciated !

Regards
Walter

[Walter]

Re: mysql data to web dialog component

In addition to the first post, will THIS select statement always return one record only. So I only need to "map" one record's values to the dialog.

Thanks
Walter

[Walter]

Re: mysql data to web dialog component

Just to update:

Thanks to Dr Peter Wayne for assisting with the sql statement.
The solution is:

dim sqlStatement as c 
sqlStatement = <<%sql%
SELECT BK_NO, BK_AMT, BK_RSNME, BK_UNDESC, BK_CNTRY, BK_AREA, BK_ASGPREF, BK_NOTE, BK_CNF, IMP_ASG FROM bkng WHERE (IMP_ASG = :userNo)
%sql%
dim args as sql::arguments
args.set("userNo",vUserNo)
'Execute the Query
flagResult = cn.Execute(sqlStatement, args)

The second problem to assign the mysql table field values to the dialog components are still a problem.
I have created this so far and is working ok'ish...:

'Now, dump the contents of the ResultSet to a variable
dim vResult as c 
vResult = rs.ToString()
 
'close the connection 
cn.close()
 
'assign values to this dlg:
CurrentForm.Controls.ulink.value = vUserNo
CurrentForm.controls.bkng_no.value = word(vResult,1)
CurrentForm.Controls.amount.value =convert_type(word(vResult,2),"numeric")

regards
Walter

[Walter]

Re: mysql data to web dialog component

Finally got it!

To assign the values from the resultset to the controls on the dialog:

dim rs as sql::resultset
rs = cn.ResultSet
 
'assign values to this dlg:
ulink = vUserNo
bkng_no = rs.data("bk_no")
amount = convert_type(rs.data("bk_amt"),"character")

Thanks Selwyn.

Hope this help someone later on .... :)

Walter