Does reportview work with a getrows() array from an SQL query? Any examples?
If 'getrows' returns a safearray like the ADO Recordset version does then yes. You can use safe arrays just like WIL arrays so you can load a REPORTVIEW the same way you would load it with a rank 2 WIL array.
Yes. I think there is an example in the tech database. If you have trouble finding one let me know. Basically just do the getrows() and use that array as the source for the ReportView. When they first implemented ReportView, it didn't work without a small tweak to the array that was returned but after I pointed that out they fixed it so it worked. I think I had to Redimension the array or something like that but not anymore.
Jim
Not finding an example in the tech database so here is some code I chopped out of a program.
Jim
SQLText = "select copy_order, copy_requestor, copy_requestor_id, copy_budget, copy_location from copy_cat where copy_profile = '%copy_profile%' order by copy_order"
myREC.Open (SQLText, myConn, adoConst.adOpenStatic, adoConst.adLockReadOnly, adoConst.adCmdText)
If myREC.EOF == @FALSE Then
copy_cat_arr = myREC.GetRows()
DialogControlSet(COP_Handle,"rv_COP_copy_cat",@dcContents, copy_cat_arr)
Else
copy_cat_arr = ArrDimension(1,11)
ArrInitialize(copy_cat_arr, " ")
DialogControlSet(COP_Handle,"rv_COP_copy_cat",@dcContents,copy_cat_arr)
EndIf
myREC.Close
Thanks (both): I need to put together a quick demo with a select of 75 fields from MySQL. Don't need a dynamic dialog so it seems I can assign getrows() to my reportview var. Any chance I can get an example of how to make a static reportview pretty as possible (allow scrolling and sorting only).
My alternative is powershell and C# - looking at about 700 lines as opposed to 50 in WB
Sure...give me a few minutes. If you want sorting to work like I think it should it requires a dynamic dialog. That is, if you click the header it sorts ascending, if you click again it sorts descending.
Jim
After looking at this some more I think you are going to have to go with a Dynamic Dialog, if you want it pretty. You can populate the List but then have no way to adjust column width, formatting, etc.
Jim
Quote from: JTaylor on March 06, 2015, 08:23:10 AM
After looking at this some more I think you are going to have to go with a Dynamic Dialog, if you want it pretty. You can populate the List but then have no way to adjust column width, formatting, etc.
Jim
I'll look into it. Right now powershell, c# can set them all in advance of the display... more initial code but had to get the demo out this morning.
I have another great alternative with an MSHTML control and an HTML table populated with getstring... just don't thnk MSHTML recognizes CSS to freeze table headers.
Sorry for not posting something sooner. Trying to sort the Sorting out and my wife keeps finding things that have to be done "Now" :) Sounds like you are past your deadline at the moment but I will post what I have once I work that out, in case it helps.
Jim
For what it is worth...The sorting isn't perfect as you have to click twice sometimes but not sure it can be made better than this due to the way sorting is implemented. I am going to post a question under dialogs about the sorting. You may want different style attributes.
This does use the native dialog constants. Hopefully that won't be a problem.
Jim
Home_Path = DirScript()
DirChange(Home_Path)
GoSub Load_Routines
IntControl(49,3,0,0,0)
My_Connection()
STANFormat=`WWWDLGED,6.2`
STANCaption=`Stan Grid`
STANX=-01
STANY=-01
STANWidth=554
STANHeight=301
STANMinWidth=298
STANMinHeight=301
STANNumControls=003
STANProcedure=`DEFAULT`
STANProcedure=`Stan_Sub`
STANTextColor=`DEFAULT`
STANBackground=`DEFAULT,6|28|85`
STANConfig=4513740
STAN001=`525,003,022,012,PUSHBUTTON,"pb_STA_Exit",DEFAULT,"E&xit",1,3,@csDefButton,DEFAULT,DEFAULT,DEFAULT`
STAN002=`005,003,022,012,PUSHBUTTON,"pb_STA_Load",DEFAULT,"Load",6,27,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
STAN003=`005,017,542,278,REPORTVIEW,"rv_STA_copy_cat",copy_cat_arr,DEFAULT,DEFAULT,39,@csAsort|@csFirstHeader|@csFullSel|@csGrid|@csNoAdjust|@csSingleSel,"Courier New|6144|40|49","0|0|0",DEFAULT`
ButtonPushed=Dialog("STAN")
My_Connection_Close()
Exit
:Load_Routines
#DefineSubRoutine Stan_Sub(STA_Handle,DMsg,DCID,DEInfo,ChangeInfo)
Switch (DMsg)
Case @deInit
DialogProcOptions(STA_Handle, @deClose,1) ; Close selected (IntControl(49....) (1-On, 0-Off).
DialogProcOptions(STA_Handle, @deTimer,0) ; TimerEvent (0- Off).
DialogProcOptions(STA_Handle, @dePbPush,1) ; Pushbutton/PictureButton.
DialogProcOptions(STA_Handle, @deRvrSelect,1) ; RV Item Select Row.
DialogProcOptions(STA_Handle, @deRvhClick,1) ; RV Edit Item Text.
copy_cat_sort_col = 1
Get_CopyCat_List()
Break
Case @deTimer
x = copy_cat_sort_col_new
If copy_cat_sort_col_new == copy_cat_sort_col Then
If copy_cat_sort_ord_%x% == @ASCENDING Then
copy_cat_sort_ord_%x% = @DESCENDING
DialogControlState(STA_Handle,"rv_STA_copy_cat",@dcsRemStyle,@csAsort)
DialogControlState(STA_Handle,"rv_STA_copy_cat",@dcsAddStyle,@csDsort)
Else
copy_cat_sort_ord_%x% = @ASCENDING
DialogControlState(STA_Handle,"rv_STA_copy_cat",@dcsRemStyle,@csDsort)
DialogControlState(STA_Handle,"rv_STA_copy_cat",@dcsAddStyle,@csAsort)
EndIf
Else
copy_cat_sort_ord_%x% = @ASCENDING
DialogControlState(STA_Handle,"rv_STA_copy_cat",@dcsRemStyle,@csDsort)
DialogControlState(STA_Handle,"rv_STA_copy_cat",@dcsAddStyle,@csAsort)
EndIf
copy_cat_sort_col = copy_cat_sort_col_new
DialogProcOptions(STA_Handle, @deTimer,0)
Break
Case @deClose
Return 9
Break
Case @deRvhClick
Switch(DialogProcOptions(STA_Handle,@dpoCtlNumber,DCID))
Case DialogProcOptions(STA_Handle,@dpoCtlNumber,"rv_STA_copy_cat")
copy_cat_sort_col_new = ChangeInfo
DialogProcOptions(STA_Handle, @deTimer, 1)
Break
EndSwitch
Break
Case @deRvrSelect
Switch(DialogProcOptions(STA_Handle,@dpoCtlNumber,DCID))
Case DialogProcOptions(STA_Handle,@dpoCtlNumber,"rv_STA_copy_cat")
copy_cat_idx = ChangeInfo
copy_cat_arr = DialogControlGet(STA_Handle,"rv_STA_copy_cat",@dcSelect)
grid_idx_old = ""
Break
EndSwitch
Break
Case @dePbPush
Switch(DialogProcOptions(STA_Handle,@dpoCtlNumber,DCID))
Case DialogProcOptions(STA_Handle,@dpoCtlNumber,"pb_STA_Exit")
Return -1
Break
Case DialogProcOptions(STA_Handle,@dpoCtlNumber,"pb_STA_Load")
Get_CopyCat_List()
Break
EndSwitch
Break
EndSwitch
Return -2
#EndSubRoutine
#DefineSubRoutine Get_CopyCat_List()
If !IsDefined(copy_cat) Then copy_cat = ""
copy_profile = "Jim"
SQLText = "select copy_order, copy_requestor, copy_requestor_id, copy_budget, copy_area, copy_location, copy_collection, copy_itype, copy_extra1, copy_extra2, copy_extra3, copy_extra4, copy_note, copy_copies, copy_num from copy_cat where copy_profile = '%copy_profile%' order by copy_order"
myREC.Open (SQLText, myConn, adoConst.adOpenStatic, adoConst.adLockReadOnly, adoConst.adCmdText)
If myREC.EOF == @FALSE Then
copy_cat_arr = myREC.GetRows()
DialogControlSet(Sta_Handle,"rv_Sta_copy_cat",@dcContents,copy_cat_arr)
copy_cat = copy_cat_arr[0,0]
col_cnt = myREC.Fields.Count
header_text = ""
col_width = ""
col_form = ""
For x = 0 to col_cnt-1
header_text = header_text:myREC.Fields(x).Name:@TAB
col_width = col_width:"-2 "
col_form = col_width:"0 "
y = x+1
copy_cat_sort_ord_%y% = @ASCENDING
Next
header_text = ItemRemove(1,header_text,@TAB)
col_form = ItemReplace("2",1,col_form," ") ;RIGHT JUSTIFY
col_form = ItemReplace("1",2,col_form," ") ;CENTER
col_form = ItemReplace("2",3,col_form," ") ;RIGHT JUSTIFY
col_form = ItemReplace("2",12,col_form," ") ;RIGHT JUSTIFY
col_form = ItemReplace("2",14,col_form," ") ;RIGHT JUSTIFY
Else
header_text = "No Rows"
col_width = "-2"
col_form = ""
copy_cat = ""
copy_cat_arr = ArrDimension(1,11)
ArrInitialize(copy_cat_arr, " ")
EndIf
myREC.Close
DialogControlSet(Sta_Handle,"rv_Sta_copy_cat", @dcContents, copy_cat_arr)
DialogControlSet(Sta_Handle,"rv_Sta_copy_cat",@dcHeaderText,header_text)
DialogControlSet(Sta_Handle,"rv_Sta_copy_cat",@dcColWidth,col_width)
DialogControlSet(Sta_Handle,"rv_Sta_copy_cat",@dcColFormat,col_form)
DialogControlSet(Sta_Handle,"rv_Sta_copy_cat",@dcSelect,copy_cat)
#EndSubRoutine
#DefineSubRoutine My_Connection()
jserver = ""
jport = ""
juid = ""
jpwd = ""
jdb = ""
;############################
; Establish the connection
;############################
myConn = ObjectOpen('ADODB.Connection')
myREC = ObjectOpen('ADODB.Recordset')
myREC.CursorLocation = 3
myConn.ConnectionString = "Driver={MySQL ODBC 5.1 Driver}; Server=%jserver%; UID=%juid%; PWD=%jpwd%; Option=71303168; Database=%jdb%"
myConn.CommandTimeout = 360
adoConst = ObjectConstantsGet(myConn)
myConn.Open
#EndSubRoutine
#DefineSubRoutine My_Connection_Close()
myConn.Close
ObjectClose(myREC)
ObjectClose(myConn)
#EndSubRoutine
Return
Appreciate this post -- I'm going to steal some ideas for sure.
Thanks!
-K
Jim; what do you mean "for what it's worth"... I appreciate all your responses; just hate to come off as a moocher....
To be honest... I would prefer WB over most other products, but the people who pay me want VS or PS. AS PS now has a -passthru option for their grid output, it is difficult to argue reportview.
You said you had to have your demo in this morning...I just meant I may have been to late to be of any help.
Jim
Quote from: stanl on March 06, 2015, 11:59:00 AM
Jim; what do you mean "for what it's worth"... I appreciate all your responses; just hate to come off as a moocher....
I'm sure you both know this but for anyone else...if you don't like the header names you can change that by aliasing the column name in the select statement. Example:
SELECT copy_order as Order, copy_requestor as Requestor, copy_requestor_id as Requestor_Id,.........
Jim
Quote from: JTaylor on March 06, 2015, 12:11:15 PM
I'm sure you both know this but for anyone else...if you don't like the header names you can change that by aliasing the column name in the select statement. Example:
SELECT copy_order as Order, copy_requestor as Requestor, copy_requestor_id as Requestor_Id,.........
Jim
Guess we can go off-topic a little bit. One important point about alias with MSAccess is whenever you are aliasing to a potential reserved word, like Date, or using spaces, the alias should be enclosed in [ ], but transferring the SQL to say MySQL, they have to be removed.
Back on topic. ReportView did not pan out as an alternative in my situation.
Most other databases you would use double quotes in such a situation.
Jim