Skip to Content
author's profile photo Former Member
Former Member

How to fill the first column '#' of matrix with row id in user form?

Hi All,

I am having a user defined form in which, I m having a matrix and m populating the matrix with the output of an sql query.

something like this:

SQL_Str= "select col1,col2,......from.... "

OPopDBTable.ExecuteQuery(SQL_Str)

oMatrix.AddRow()

oMatrix.Columns.Item("Col3").DataBind.Bind("PopTable", "col1")

oMatrix.Columns.Item("Col2").DataBind.Bind("PopTable", "col2")

oMatrix.LoadFromDataSource()

Now, in this, how can I assign row number to first column ('#' column ) ??

regards,

Binita

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Apr 03, 2008 at 01:52 PM

    Dear Petr and David,

    Roland has just posted an answer to similar question of mine. (post:'how to fill the matrix with sql query output')

    row_number() works, but u have to cast it with integar type.

    its now working. M working on SAP 2005 B, PL 38.

    But, still am not able to print proper data in 'Col0' which is '#' column. it prints zero in all the rows.

    if I databind my rowcounter column with a userdefined column of type integer, it prints correct order.

    how to do that?

    regards,

    Binita

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member David Nussböck

      Ah...the ROW_NUMBER() was posted by Petr - also thank you (again)

      With my example-query from the other thread:

      SELECT 
      	CAST(
                   ROW_NUMBER() OVER (ORDER BY T1.U_LogNo ASC) AS INT
              ) AS RowCnt, 
      	T1.Code, 
      	T1.U_ParcelNo, 
      	T1.U_LogNo 
      FROM 
      	[@XXX_EXAMPLE] T1 
      WHERE 
      	T1.U_ParcelNo='132006'  
      ORDER BY 
      	T1.U_LogNo ASC
      

      ...this works for me (it's quickly copied from several subs/func. - I hope it's not too much disordered):

      DtLogs.ExecuteQuery(query)
      
      oMtxLogs.Columns.Item("0").DataBind.Bind("DT_LOGS", "RowCnt")
      oMtxLogs.Columns.Item("1")........
      ..........
      
      oMtxLogs.LoadFromDataSource()
      

  • author's profile photo Former Member
    Former Member
    Posted on Apr 03, 2008 at 01:01 PM

    change the query as

    QL_Str= "select row_number() over (order by col1) as rowid, col1,col2,......from.... "

    and bind rowid to matrix.

    Petr

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 03, 2008 at 01:13 PM

    Hi Petr,

    I have been waiting for some one to say this 😊

    I have used this query and it simply raises 'The server threw exception' error. it runs well if i remove the row_number() over column.

    so only I posted the question.

    even u use this query in query generator wizard, the column remains empty.

    regards,

    Binita

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 11, 2008 at 04:58 AM

    Dear Binita,

    Can you plz tell me whether your code is working.

    Its populating perfectly but its not getting saved into database...

    I am suffering this problem long time back...

    Can you plz help if after doing this data is saving perfectly..

    Thanks

    Regards

    Sanjay

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 11, 2008 at 05:36 AM

    Hi Sanjay,

    even I m stuck up with the data saving part. may be David or Roland can enlighten both of us 😊

    I have already been following up similar question where David is replying.

    please keep looking the thread :

    Inserting data into document type tables

    I will let u know if I have any breakthrough. sure.

    regards,

    Binita

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.