Skip to Content
avatar image
Former Member

How to fill matrix with the output of recordset query?

Hi all,

I want a user matrix to load data when I hit a button and the data to be filled is the output of an sql query. binding the matrix columns with table columns and then load the matrix with DBDatasource attached to that table will not do in this case as the columns in the sql query are from several different tables or views.

writing following line to do this takes several minutes to load the data:

For j = 0 To Reordset.RecordCount - 1

Matrix.AddRow()

Matrix.Columns.Item("col1").Cells.Item(i).Specific.value = Recordset.Fields.Item("cardcode").Value

Recordset.movenext()

next

Is there any other way to fill the matrix in this case, which loads the data faster?

Regards,

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

18 Answers

  • Best Answer
    avatar image
    Former Member
    Apr 02, 2008 at 10:34 AM

    HI Roland,

    if u can remember, u had asked for some SQL rowcounter function so that u can fetch the row number in the sql query itself.

    following is the function (row_number() over (order by NameOfColumn)

    select row_number() over (order by docentry) as rowcount, docentry,docnum,..... from ...

    hope it helps. (I know is too late, but I just happend to knnw it now )

    regards,

    Binita

    'rowcount' is a keyword so use some other alias for that column.

    Edited by: Binita Joshi on Apr 2, 2008 12:37 PM

    Sorry Roland.one bad news is, row_number does not work in executequery of datatable(even if u write it in view and fetch it from there).u wont catch any exception but it wont simply print data. 😔

    Edited by: Binita Joshi on Apr 2, 2008 12:55 PM

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Roland,

      I face the same problem in matrix.loadfromdatasource for the second time will show garbage value in numeric column.

      How did you solve it???

      Following is my query, I execute the query with B1 datatable. then bind it to matrix column.

      SELECT

      CAST(row_number() OVER (ORDER BY ORDR.DocEntry) AS INT) AS RowNo,

      ORDR.DocEntry, ORDR.DocNum, ORDR.CardCode,

      ORDR.DocDueDate, ORDR.DocStatus, RDR1.LineNum,

      RDR1.ItemCode, RDR1.Dscription, CAST(RDR1.Quantity AS NUMERIC(19,6)) AS Quantity,

      RDR1.Price, RDR1.LineTotal, RDR1.TotalFrgn,

      RDR1.WhsCode, RDR1.U_LotNo, RDR1.U_CalWeight,

      RDR1.U_WeightQty, RDR1.U_CallOffQty,

      ISNULL(RDR1.Quantity,0)-ISNULL(RDR1.U_CallOffQty,0) AS OpenQty,

      RDR1.TreeType

      FROM

      ORDR

      INNER JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry

      WHERE

      ORDR.DocStatus='O' AND RDR1.TreeType='S' AND RDR1.Quantity>0

      Thanks for any help...

      Hock Soon

  • avatar image
    Former Member
    Feb 13, 2008 at 11:07 AM

    Hi Roland and David,

    Thank u so very much for replying back. the code given by roland worked perfactly fine. but, It has one problem. in my user defined form, one of the column of the matrix has system CFL attached (CFL for itemcode). to do this, I will have to bind that column to user datasource. now, for attaching datatable, I will have to bind my columns with the columns in the datatable query. after, the matrix get loaded with the code given by Roland, I am adding one empty row and trying to bind the column with user datatsource. it gives me "Matrix-line exists' error. as it requires "matrix.clear()" to work. if I do this, my matrix gets wiped out. now, How can I do this?

    Regards..............

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member David Nussböck

      Hello Binita,

      I've tested the CFL for DataTable-Matrixes in my test-AddOn. But there's still one problem left. Maybe you find it out (I will need this in the future too...).

      The ChooseFromList must be added behind the DataTable-Bind, which is done on every MTX-Load (it's not from the sample above, so the UIDs are new ones):

                  oDt = oDts.Item("dt_test")
      
                  query = "SELECT  * FROM [@T_CONVTOOLS01]"
                  oDt.ExecuteQuery(query)
      
                  oMtx.Columns.Item("0").DataBind.Bind("dt_test", "Code")
                  oMtx.Columns.Item("1").DataBind.Bind("dt_test", "U_Alpha01")
                  oMtx.Columns.Item("2").DataBind.Bind("dt_test", "U_Price01")
                  oMtx.Columns.Item("3").DataBind.Bind("dt_test", "U_Quant01")
                  oMtx.Columns.Item("4").DataBind.Bind("dt_test", "U_Date01")
      
      
                  AddChooseFromLists()
      
                  oMtx.LoadFromDataSource()
      

      ...where AddChooseFromLists() is...

        Private Shared Sub AddChooseFromLists()
              Try
                  Dim oCfls As SAPbouiCOM.ChooseFromListCollection
                  Dim oCons As SAPbouiCOM.Conditions
                  Dim oCon As SAPbouiCOM.Condition
                  Dim oCfl As SAPbouiCOM.ChooseFromList
                  Dim oCflCreationParams As SAPbouiCOM.ChooseFromListCreationParams
                  Dim oBtn As SAPbouiCOM.Button
                  Dim oMtx As SAPbouiCOM.Matrix
                  Dim oCol As SAPbouiCOM.Column
                  Dim oEtx As SAPbouiCOM.EditText
      
                  oMtx = oForm.Items.Item("MTX_TEST02").Specific
                  oCfls = oForm.ChooseFromLists
      
      
                  oCflCreationParams = SboCon.SboUI.CreateObject(SAPbouiCOM.BoCreatableObjectType.cot_ChooseFromListCreationParams)
                  oCflCreationParams.MultiSelection = False
                  oCflCreationParams.ObjectType = SAPbouiCOM.BoLinkedObject.lf_BusinessPartner
                  oCflCreationParams.UniqueID = "CFL_C1" 
                  oCfl = oCfls.Add(oCflCreationParams)
                  '#
                  '### OPTION:
                  '# Shown ChooseFromList restricted by Conditions 
                  oCons = oCfl.GetConditions()
                  oCon = oCons.Add()
                  oCon.Alias = "CardType"
                  oCon.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL
                  oCon.CondVal = "C"
                  oCfl.SetConditions(oCons)
                  '#
                  '###
                  '#
                  oCol = oMtx.Columns.Item("1")
                  oCol.ChooseFromListUID = "CFL_C1"
                  oCol.ChooseFromListAlias = "CardCode"
                  '#########################################################
              Catch e As Exception
                  Microsoft.VisualBasic.MsgBox(className & ".AddChooseFromLists()" & vbCrLf & "Exception:" & vbCrLf & e.Message.ToString)
              End Try
          End Sub
      

      But I'm running into problems when trying to set the MTX-Cell via DataTable at the CFL-Event (see comment):

      
                  If pVal.EventType = SAPbouiCOM.BoEventTypes.et_CHOOSE_FROM_LIST Then
                      Dim oMtx As SAPbouiCOM.Matrix
                      Dim oCFLEvent As SAPbouiCOM.IChooseFromListEvent
                      Dim oCFL As SAPbouiCOM.ChooseFromList
                      Dim oDataTable As SAPbouiCOM.DataTable
                      Dim cflID As String
                      Dim oDt As SAPbouiCOM.DataTable = oForm.DataSources.DataTables.Item("dt_test")
      
                      oCFLEvent = pVal
                      cflID = oCFLEvent.ChooseFromListUID
                      'Dim oForm As SAPbouiCOM.Form
                      'oForm = SboConnection.SboUI.Forms.Item(FormUID)
                      oCFL = oForm.ChooseFromLists.Item(cflID)
                      If Not oCFLEvent.BeforeAction Then
                          oDataTable = oCFLEvent.SelectedObjects
                          If oDataTable Is Nothing Then Exit Sub
                          '############## Matrix Test #######################################################################
                          If (oCFLEvent.ItemUID = "MTX_TEST02") Then
                              oMtx = oForm.Items.Item("MTX_TEST02").Specific
      
                              '### CFL Results to UserForm
                              '#
                              oDt.Rows.Offset = oCFLEvent.Row - 1
                              MsgBox(oDt.GetValue("U_Alpha01", oCFLEvent.Row - 1))
                              MsgBox(oDataTable.GetValue("CardCode", 0))
      
                              ' PROBLEM - can't get this working - don't know at the moment:
                              ' on other forms SetValue works....
                              oDt.SetValue("U_Alpha01", oCFLEvent.Row - 1, oDataTable.GetValue("CardCode", 0))
                              ' this also gives me problems...:
                              'oMtx.Columns.Item("1").Cells.Item(oCFLEvent.Row).Specific.value = oDataTable.GetValue("CardCode", 0)
      
      
                              oForm.Update()
                              '#
                              '###
      
      
                          End If
      
                      End If
                  End If
      

      The CFL-Form opens and returns the choosen value(s) at the CFL-event. But writing to MTX still not works with this code... 😕

      Maybe you find the last piece. If so: please write here. 😊

      Cheers,

      Roland

      p.s.:

      David Nussböck wrote

      LOL - ROLAND WAS FASTER 😊))

      ...just one minute... 😉

  • avatar image
    Former Member
    Feb 21, 2008 at 10:44 AM

    HI Roland,

    I have a reply from SAP and it works fine:

    wht u need to do is add the following line to you CFL event.

    matrix.Columns.Item("col_1").DataBind.SetBound(True, "", "itcodeDS")

    form.DataSources.UserDataSources.Item("itcodeDS").ValueEx = val

    matrix.SetLineData(pVal.Row)

    even if u have bound the column while loading the form or menu , you will again have to bind it in CFL event..

    try it...

    Binita

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello Binita,

      thank you very much for that tip! 😊)

      I think it is the best to rebind the CFL-column back to the UserDataSource directly after every oMtx.LoadFromDataSource() with DataTable...

      ...because if there are functions which are doing calculations on the MTX-rows, these functions can always rely on a UserDataSource for the CFL-column.

      If we would do the rebind only at CFL those functions would have to read values sometimes from DataTable and sometimes from UserDataSource.

      OK: Cell.Item("XX").specific.value should always work but I try to get rid of EditText.Value/String as much as I can... 😉

      Cheers,

      Roland

  • avatar image
    Former Member
    Mar 28, 2008 at 12:39 PM

    thts what David. thts what I am wondering. if I am doing databind to column of itemcode in datatable query to the column of matrix which is attached to userdatasource of type shorttext, it fetches data. but, if I am fetching quantity column to the matrix column which is attached to userdatasource of type dt_quantity, it fetches data for the first time, when I again populate it, it prints garbage, while itemcode shows perfact values, though it is also attached to userdatasource. so whts wrong with quantity type userdatasource???

    or I doubt I m doing something wrong!!

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 07, 2008 at 12:54 PM

    Roland,

    if possible, please let me know how can i wipe out the matrix completely without having the quantity/rate type columns with 0.00 values. (please see my previous reply to u in the same thread above ) .its urgent.

    regards,

    Binita

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Roland,

      i was thinking of suggesting her to use CASE.

      SELECT CASE IsNull(Quantity,0) WHEN 0 THEN null ELSE Quantity END as 'Quantity'

      or maybe to convert it to nvarchar before and than use '' instead of null

      what do you think ?

      regards

      David

  • avatar image
    Former Member
    Feb 13, 2008 at 08:38 AM

    Hi!

    You may use the DataTable:

    Every time the form loads:

    oDts = oForm.DataSources.DataTables
    oDts.Add("DT_MYDATATABLE")
    

    Every time the MTX loads:

    Dim .......
    Dim oMtx......
    Dim query As String
    
    query="SELECT U_one, U_two [...] FROM [@MY_TABLE] [...] WHERE [...]"
    
    oDt = oDts.Item("DT_MYDATATABLE")
    
    oDt.ExecuteQuery(query)
    
    With oMtx.Columns
                    .Item("0").DataBind.SetBound(True, "", "UDS_0") ' <- one column as UserDataSource
                    .Item("1").DataBind.Bind("DT_MYDATATABLE", "U_one")
                    .Item("2").DataBind.Bind("DT_MYDATATABLE", "U_two")
    End With
    
    oMtx.LoadFromDataSource()
    

    A disadvantage is that there is no line-numbering. Except you bind the first col to a UserDataSource and fill it again line by line after the "power"-LoadFromDataSource. But this is time consuming again.

    But the best thing would be if there were a SQL-function which could select the row-number directly in the query. Does anybody know such a wished function "ResultRowCounter()" ?

    SELECT dbo.ResultRowCounter(), U_Col1, U_Col2 FROM.......

    Cheers,

    Roland

    Edited by: Roland Toschek on Feb 13, 2008 11:23 AM

    wrong UID at "oDts.Add"

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 13, 2008 at 08:39 AM

    Hi Binita,

    here's a sample

    oForm.DataSources.DataTables.Add ("RESULT")

    oForm.DataSources.DataTables.Item("RESULT").ExecuteQuery ("SELECT U_Ausdruck, U_Verbuch FROM

    ")

    oForm.Items.Item("mtxList").Specific.Columns.Item("ColAusdruc").DataBind.Bind "RESULT", "U_Ausdruck"

    oForm.Items.Item("mtxList").Specific.Columns.Item("ColVerbuch").DataBind.Bind "RESULT", "U_Verbuch"

    regards

    LOL - ROLAND WAS FASTER 😊))

    David

    Edited by: David Nussböck on Feb 13, 2008 9:39 AM

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 14, 2008 at 10:26 AM

    Hi Roland,

    Thanks again for the code. I already tried it with no luck 😔

    according to me, to get system CFL work on matrix column, u will have to bind it with user data source and do matrix.setlinedata(rowindex).

    But this works only when u don't load the matrix with the datatable but copy it with 'specific.value' property to each column, as I have mentioned in my first scrap. but again , we will be back to square one as it takes minutes to load the data if done that way.

    I Have posted the issue to SAP as I want it with datatable only.....will let u know for sure as soon as I get it.

    till then..........

    regards,

    Binita

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 28, 2008 at 07:48 AM

    HI Roland and David,

    I know , I am posting this thing really late but, I happend to implement it only now.

    one strange error I am facing is, if the column which is attached to userdatasource of type dt_Quantity is databound to one of the column of datatable query, first time it shows proper data. on subsequent immediate fetch(or execution of the same query), all other rows show same data while this particular column shows some garbage like encrypted varchar chararcters!!!

    Dont really know, what to do with this.

    (also, if i remove userdatasource and attach it to the table column of type quantity, it works just fine. I know, it has been discussed before in the same post that you cant have userdatasource attached to the same column to which you are binding column of datatable query but this thing works just fine with itemcode column of userdatasource of type shorttext. so I was just wondering what's wrong with quantity??)

    any clues ??? its urgent 😢

    thanks in advance.

    regards,

    Binita

    Add comment
    10|10000 characters needed characters exceeded

    • do you want to say that the problem is when you use this

      oForm.Items.Item("mtxList").Specific.Columns.Item("ColAusdruc").DataBind.Bind "RESULT", "U_Quantity"

      that the value is not displayed correctly ?

      regards

      David

  • avatar image
    Former Member
    Mar 28, 2008 at 07:48 AM

    implementation error for datatable query

    Add comment
    10|10000 characters needed characters exceeded