I'm freshening up some of my code and trying to do a few things and I keep getting error when trying to get the result set.
this is the code that calls the function
Trying to condense my code to a function here is the function code I have 12 pages of code that i'm trying to reduce to 1
Here is a screen shot of the error
alpha_Error.PNG
this is the code that calls the function
Code:
'Date Created: 29-Jul-2016 04:57:10 PM 'Last Updated: 03-Jul-2018 02:20:19 PM 'Created By: Admin 'Updated By : Admin DIM SHARED frm as D DIM SHARED to as D DIM SHARED varC_result as C ok_button_label = "&OK" cancel_button_label = "&Cancel" varC_result = ui_dlg_box("Choose Range",<<%dlg% {region} From:| [%DATE;P=popup.calendar(dtoc(frm));I=popup.calendar%.15frm!frm_*]; To:| [%DATE;P=popup.calendar(dtoc(to));I=popup.calendar%.15to!to_*]; {endregion}; {line=1,0}; {region} <*15=ok_button_label!OK> <15=cancel_button_label!CANCEL> {endregion}; %dlg%,<<%code% if left(a_dlg_button,4) = "frm_" then if a_dlg_button = "frm_killfocus" then frm = ctod(dtoc(frm)) end if a_dlg_button = "" end if if left(a_dlg_button,3) = "to_" then if a_dlg_button = "to_killfocus" then to = ctod(dtoc(to)) end if a_dlg_button = "" end if %code%) count=1 while count <10 pday="pd"+count 'DIM a connection variable DIM cn as SQL::Connection dim flagResult as l flagResult = cn.open("::Name::pat") if flagResult = .f. then ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text) end end if 'oring 'SELECT joinid, cfname, clname,straddy,city,state,zipcode,eagent,moneypd,epdocs,underwriter,homephone,cellphone,work_phone,ph_name1,ph_name2,ph_name3,status,enr_status,pay_status,follow_up,custname,saledate,cliemail,clifax,enroldate,c_type,card_name, AES_DECRYPT(c_num, sha2('passthis420',512)), c_xp, c_vc, c_am_vc, pd1,pa1,ba,bc,bs,bz,verif1,verif2,verif3,verif4,verif5,verif6,verif7,baddsame,verif1,verif2,verif3,verif4,verif5,verif6,verif7,trans_hist FROM pat.ccb_info INNER JOIN clientd ON pat.ccb_info.idclientd=clientd.idclientd WHERE (pd1 BETWEEN :frm and :to) and status <> 'dead' 'SELECT joinid, cfname,lname,straddy,city,state,zipcode,eagent,moneypd,epdocs,underwriter,homephone,cellphone,work_phone,ph_name1,ph_name2,ph_name3,status,enr_status,pay_status,follow_up,custname,saledate,cliemail,clifax,enroldate,c_type,card_name, AES_DECRYPT(c_num, sha2('passthis420',512)), c_xp, c_vc, c_am_vc, pd1,pa1,ba,bc,bs,bz,verif1,verif2,verif3,verif4,verif5,verif6,verif7,baddsame,verif1,verif2,verif3,verif4,verif5,verif6,verif7,trans_hist,auth_sent,lender,loan_num FROM pat.ccb_info INNER JOIN clientd ON pat.ccb_info.idclientd=clientd.idclientd WHERE (pd1 BETWEEN :frm and :to) and status <> 'dead' INNER JOIN intake ON pat.intake.idclientd=client.idclientd cn.PortableSQLEnabled = .f. dim sqlStatement as c sqlStatement = "SELECT joinid, cfname, clname,price_quote,pay_status,enr_status,straddy,city,state,zipcode,eagent,assign_to,assign_date,magnet,moneypd,epdocs,underwriter,homephone,cellphone,ph_name1,ph_name2,ph_name3,work_phone,status,follow_up,custname,saledate,cliemail,clifax,enroldate,c_type,card_name, AES_DECRYPT(c_num, sha2('passthis420',512)), c_xp, c_vc, c_am_vc,pd1,pa1,pd2,pa2,pd3,pa3,pd4,pa4,pd5,pa5,pd6,pa6,pd7,pa7,pd8,pa8,pd9,pa9,pd10,pa10,pd11,pa11,pd12,pa12, ba,bc,bs,bz,verif1,verif2,verif3,verif4,verif5,verif6,verif7,verif8,verif9,verif10,verif11,verif12,baddsame,trans_hist FROM pat.ccb_info INNER JOIN clientd ON pat.ccb_info.idclientd=clientd.idclientd WHERE ("+pday+" BETWEEN :frm and :to and status not in ('dead' ,'cancelled','refunded'))" DIM args as sql::arguments if a5_eval_valid_expression("=Var->frm",local_variables()) then args.add("frm",convert_type(a5_eval_expression("=Var->frm",local_variables()),"D")) end If if a5_eval_valid_expression("=Var->to",local_variables()) then args.add("to",convert_type(a5_eval_expression("=Var->to",local_variables()),"D")) end If if a5_eval_valid_expression("=Var->pday",local_variables()) then args.add("pday",convert_type(a5_eval_expression("=Var->pday",local_variables()),"c")) end If 'Execute the Query flagResult = cn.Execute(sqlStatement,args) if flagResult = .f. then ui_msg_box("Error",cn.CallResult.text) cn.close() end end if 'Displays a message box (style:'OK Button', image: 'Information symbol', id: 'pd'). dim rs as sql::resultset rs = cn.ResultSet dim data as C data = rs.ToString() if data = "" then goto end end if flagResult = cn.Execute(sqlStatement,args) if flagResult = .f. then ui_msg_box("Error",cn.CallResult.text) cn.close() end end if dim rs as sql::resultset rs = cn.ResultSet dim ttbl as p dim flagLoop as l flagLoop = .t. while flagLoop payment_write() flagLoop = rs.NextRow() end while cn.close() end: count=count+1 end while dim shared ttl as n ttl=0.00 dim shared ts1 as n dim shared ts2 as n dim shared ts3 as n dim shared ts4 as n dim shared ts5 as n dim shared ts6 as n dim shared ts7 as n dim shared ts8 as n dim shared ts9 as n dim shared ts10 as n dim shared ts11 as n dim shared ts12 as n ts1= tablesum("paytoday","this_payment<>0 ","this_payment") ttl=ts1+ts2+ts3+ts4+ts5+ts6+ts7+ts8+ts9+ts10+ts11+ts12 'Displays a message box (style:'OK Button', image: 'Information symbol', id: 'ttldia'). DIM SHARED ttldia_result as N DIM SHARED ttldia_OK_Button as L ttldia_OK_Button = .F. title_var = "Money Coming " message_text_var ="Money Expected "+ ttl ttldia_result=ui_msg_box(title_var,message_text_var,UI_OK+ UI_FIRST_BUTTON_DEFAULT+ UI_INFORMATION_SYMBOL) 'Test to see which button on the message box was pressed.... SELECT CASE ttldia_result = UI_OK_SELECTED ttldia_OK_Button= .t. END SELECT 'Open a Form or Browse layout, displaying all, or selected records in the layout. query.filter = ".t." query.order = "this_pdate" 'Open the layout_type showing just the records that satisfy the filter DIM Shared varP_paytoday as P DIM layout_name as c layout_name = "paytoday" dim tempP as p 'Get pointer to existing window. In case layout_name is qualified with a dictionary name, extract up to first @. In case formname has spaces, normalize it tempP=obj(":"+object_name_normalize(word(layout_name,1,"@"))) 'Test if pointer is valid if is_object(tempP) then 'Test if pointer refers to a form or browse if tempP.class() = "form" .or. tempP.class() = "browse" then dim flagIsBaseFilter as l flagIsBaseQuery = .t. if flagIsBaseFilter then 'Apply the query to the form or browse as the Base query. (this means that 'the user cannot turn the query off, only add to the query) tempP.BaseQueryRun(query.filter,query.order) else tempP.QueryRun(query.filter,query.order) end if 'then activate the already open window tempP.activate() else 'Window is not already open, so open it varP_paytoday = :Browse.viewqueried(layout_name,query.filter, query.order ) end if else varP_paytoday = :Browse.viewqueried(layout_name,query.filter, query.order ) end if 'script_play_local("gpr2")
Trying to condense my code to a function here is the function code I have 12 pages of code that i'm trying to reduce to 1
Code:
FUNCTION payment_write AS C ( ) dim ccnum as c ccnum="" if rs.data("c_num")="" goto skip end if ccnum=rs.Data("AES_DECRYPT(c_num, sha2('mysecretkey',512))") ttbl=table.open("paytoday") ttbl.enter_begin() ttbl.Idclientd = rs.Data("joinid") ttbl.Card_name=rs.Data("card_name") ttbl.C_type=rs.Data("c_type") ttbl.C_num=decrypt_string(ccnum,"mysecretkey") ttbl.C_xp=rs.Data("c_xp") ttbl.C_vc=rs.Data("c_vc") ttbl.C_am_vc=rs.Data("c_am_vc") ttbl.this_pdate=rs.data(pday) ttbl.this_payment=rs.data("pa1") ttbl.Pd1=rs.Data("pd1") ttbl.Pa1=rs.Data("pa1") ttbl.Pd2=rs.Data("pd2") ttbl.Pa2=rs.Data("pa2") ttbl.Pd3=rs.Data("pd3") ttbl.Pa3=rs.Data("pa3") ttbl.Pd4=rs.Data("pd4") ttbl.Pa4=rs.Data("pa4") ttbl.Pd5=rs.Data("pd5") ttbl.Pa5=rs.Data("pa5") ttbl.Pd6=rs.Data("pd6") ttbl.Pa6=rs.Data("pa6") ttbl.Pd7=rs.Data("pd7") ttbl.Pa7=rs.Data("pa7") ttbl.pd8=rs.data("pd8") ttbl.pa8=rs.data("pa8") ttbl.pd9=rs.data("pd9") ttbl.pa9=rs.data("pa9") ttbl.pd10=rs.data("pd10") ttbl.pa10=rs.data("pa10") ttbl.pd11=rs.data("pd11") ttbl.pa11=rs.data("pa11") ttbl.pd12=rs.data("pd12") ttbl.pa12=rs.data("pa12") ttbl.Ba=rs.Data("ba") ttbl.Bc=rs.Data("bc") ttbl.Bs=rs.Data("bs") ttbl.Bz=rs.Data("bz") ttbl.Verif1=rs.Data("verif1") ttbl.Verif2=rs.Data("verif2") ttbl.Verif3=rs.Data("verif3") ttbl.Verif4=rs.Data("verif4") ttbl.Verif5=rs.Data("verif5") ttbl.Verif6=rs.Data("verif6") ttbl.Verif7=rs.Data("verif7") ttbl.Verif8=rs.Data("verif8") ttbl.Verif9=rs.Data("verif9") ttbl.Verif10=rs.Data("verif10") ttbl.Verif11=rs.Data("verif11") ttbl.Verif12=rs.Data("verif12") ttbl.Baddsame=rs.Data("baddsame") ttbl.Trans_hist=rs.Data("trans_hist") ttbl.Cfname=rs.Data("cfname") ttbl.Clname=rs.Data("clname") ttbl.Straddy=rs.data("straddy") ttbl.City=rs.Data("city") ttbl.state=rs.Data("state") ttbl.Zipcode=rs.Data("zipcode") ttbl.Eagent=rs.Data("eagent") ttbl.Moneypd=rs.Data("moneypd") ttbl.Epdocs=rs.Data("epdocs") ttbl.Underwriter=rs.Data("underwriter") ttbl.Homephone=rs.Data("homephone") ttbl.Cellphone=rs.Data("cellphone") ttbl.Work_phone=rs.Data("work_phone") ttbl.Ph_name1=rs.data("ph_name1") ttbl.Ph_name2=rs.data("ph_name2") ttbl.Ph_name3=rs.data("ph_name3") ttbl.Enr_status=rs.data("enr_status") ttbl.Pay_status=rs.data("pay_status") ttbl.Status=rs.Data("status") ttbl.Follow_up=rs.Data("follow_up") ttbl.Custname=rs.Data("custname") ttbl.Saledate=rs.Data("saledate") ttbl.Magnet=rs.Data("magnet") ttbl.Cliemail=rs.Data("cliemail") ttbl.Clifax=rs.Data("clifax") ttbl.Enroldate=rs.Data("enroldate") ttbl.Assign_date=rs.data("assign_date") ttbl.Assign_to=rs.data("assign_to") ttbl.Price_quote=rs.data("price_quote") ttbl.enter_end(.t.) ttbl.close() skip: END FUNCTION
alpha_Error.PNG
Comment