Results 1 to 19 of 19

Thread: MSFlexgrid Data Retrieval With ADO....

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jan 2006
    Location
    PUNE
    Posts
    222

    Exclamation MSFlexgrid Data Retrieval With ADO....

    Hi All,

    For Saving a Form i have to MAke Two Tables in Database.
    Inward_local & Inward_item as One to MAny Relationship.

    While in TAble 1 Primary Key = Receipt_no
    While in Table 2 i made Receipt_no + Sr_no = Primary Key

    For Item_details I Have used MS Flexgrids. Working Fine For Save MEthod Using Textmatrix

    am Fetching Records With ADO & Displaying on Form Working Fine

    Problem :

    How do I fetch Records From Flexgrids against Particular Receipt_no

    Pls Guide

    Thanks

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: MSFlexgrid Data Retrieval With ADO....

    I'm not too clear on what you are trying to acheive, could you explain in more detail?

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jan 2006
    Location
    PUNE
    Posts
    222

    Exclamation Re: MSFlexgrid Data Retrieval With ADO....

    i Have attached a form,
    How to Open a Record on a Form Using ADO

    as I have two tables in Access databases for Storing Database

    so How di Open a Reocrd on A Form Using ADO
    How do i fill Flexgrid with Data in Table

    Ur Help is Highly Apperciicated
    Attached Files Attached Files

  4. #4
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: MSFlexgrid Data Retrieval With ADO....

    Haven't looked into your attachment but in your sql you could add the WHERE Clause so that only records that meets your condition would be returned...
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  5. #5
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: MSFlexgrid Data Retrieval With ADO....

    i couldnt open the attachement, i have some problem of downloading into my system. if you provide the code here i might help you

  6. #6
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: MSFlexgrid Data Retrieval With ADO....

    specify the msflex.col and .row of the msflex and use .text method to display the value into the grid.

    ex :
    do while not rs.eof
    msflex1.col=1
    msflex1.row=1
    msflex1.text=rs!ecode
    msflex1.col=2
    msflex1.row=1
    msflex1.text=rs!ename
    msflex1.col=3
    msflex1.row=1
    msflex1.text=rs!desig
    rs.movenext
    loop
    and so on

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Jan 2006
    Location
    PUNE
    Posts
    222

    Re: MSFlexgrid Data Retrieval With ADO....

    Hi Ganesh,

    VB Code:
    1. rs.Open "Item_desc_local", cn, adOpenKeyset, adLockPessimistic, adCmdTable 'opening the recordset explained in the notes
    2.  
    3.     'For X = 1 To (MDIchild3.MSF7.Rows)
    4.     X = 1
    5.     MSF7.TextMatrix(X, 0) = rs.Fields("Receipt_no")
    6.     MSF7.TextMatrix(X, 1) = rs.Fields("Sr_no")
    7.     MSF7.TextMatrix(X, 2) = rs.Fields("Item_code")
    8.     MSF7.TextMatrix(X, 3) = rs.Fields("Item_name")
    9.    MSF7.TextMatrix(X, 4) = rs.Fields("Item_type")
    10.     MSF7.TextMatrix(X, 5) = rs.Fields("Item_class")
    11.     MSF7.TextMatrix(X, 6) = rs.Fields("Product_code")
    12.     MSF7.TextMatrix(X, 7) = rs.Fields("Product_srno")
    13.     MSF7.TextMatrix(X, 8) = rs.Fields("Case_no")
    14.     MSF7.TextMatrix(X, 9) = rs.Fields("Tot_pkgs")
    15.     MSF7.TextMatrix(X, 10) = rs.Fields("Tot_qty")
    16.     MSF7.TextMatrix(X, 11) = rs.Fields("Item_location")
    17.     MSF7.TextMatrix(X, 12) = rs.Fields("Unit_cost")
    18.     MSF7.TextMatrix(X, 13) = rs.Fields("Total_cost")
    19.     MSF7.TextMatrix(X, 14) = rs.Fields("Received_by")
    20.     MSF7.TextMatrix(X, 15) = rs.Fields("Checked_by")
    21.     MSF7.TextMatrix(X, 16) = rs.Fields("Warehouse_name")
    22.     MSF7.TextMatrix(X, 17) = rs.Fields("Remarks")
    23.  
    24.     Next
    25.     'MSF7.Rows = MSF7.Rows + 1

    Its Working Fine for First Row then but its not working
    But How Do i Check Condtion
    Because, Receipt_no+ Sr_no # Primary Key

    Receipt_no = 1+ (SrNo) 1 One Row
    1 +(Srno) 2 IInd Row
    1 +(Srno) 3 III rd Row

    So How Do i Check the False Condition

    False Condtion When Receipt_no Changes

    Thanks

  8. #8
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: MSFlexgrid Data Retrieval With ADO....

    i hope you have to increase x=x+1
    and set the row=row+1
    i couldnt understand the last

    Receipt_no = 1+ (SrNo) 1 One Row
    1 +(Srno) 2 IInd Row
    1 +(Srno) 3 III rd Row

    please explain

  9. #9
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: MSFlexgrid Data Retrieval With ADO....

    put all your display code in the loop and let me know

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Jan 2006
    Location
    PUNE
    Posts
    222

    Re: MSFlexgrid Data Retrieval With ADO....

    am Saving Database in TWo Tables
    ie. Inward_local Receipt_no # P Key
    Inward_items Receipt_no & Sr_no -> # P Key

    Invoice Details are Storing in Inward_local Database
    While Item_details are Storing in Inward_items as Aganist one Receipt_no Many Items Can Inwarded. One to Many Relationship.

    i Wants to Fetch the Records From Inward_items Into Flexgrids to the Correspodning Receipt_no Where Receipt_no & Sr_no

    Suppose Against Receipt_no There 3 Items are Inwarded

    3 rows should be filled

    i Used A Fillflex() Function for that


  11. #11
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: MSFlexgrid Data Retrieval With ADO....

    use the for loop like

    ictr=rs.recordcount

    for i=1 to ictr

    n

  12. #12
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: MSFlexgrid Data Retrieval With ADO....

    use the for loop like

    x=rs.recordcount
    msf7.row=1

    for i=1 to x
    MSF7.TextMatrix(X, 0) = rs.Fields("Receipt_no")
    MSF7.TextMatrix(X, 1) = rs.Fields("Sr_no")
    MSF7.TextMatrix(X, 2) = rs.Fields("Item_code")
    MSF7.TextMatrix(X, 3) = rs.Fields("Item_name")
    MSF7.TextMatrix(X, 4) = rs.Fields("Item_type")
    MSF7.TextMatrix(X, 5) = rs.Fields("Item_class")
    MSF7.TextMatrix(X, 6) = rs.Fields("Product_code")
    MSF7.TextMatrix(X, 7) = rs.Fields("Product_srno")
    MSF7.TextMatrix(X, 8) = rs.Fields("Case_no")
    MSF7.TextMatrix(X, 9) = rs.Fields("Tot_pkgs")
    MSF7.TextMatrix(X, 10) = rs.Fields("Tot_qty")
    MSF7.TextMatrix(X, 11) = rs.Fields("Item_location")
    MSF7.TextMatrix(X, 12) = rs.Fields("Unit_cost")
    MSF7.TextMatrix(X, 13) = rs.Fields("Total_cost")
    MSF7.TextMatrix(X, 14) = rs.Fields("Received_by")
    MSF7.TextMatrix(X, 15) = rs.Fields("Checked_by")
    MSF7.TextMatrix(X, 16) = rs.Fields("Warehouse_name")
    MSF7.TextMatrix(X, 17) = rs.Fields("Remarks")
    msf7.row=msf7.row+1
    next

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Jan 2006
    Location
    PUNE
    Posts
    222

    Re: MSFlexgrid Data Retrieval With ADO....

    Dear Ganesh,

    For i=1 to X

    Where X is Not all the Record in A Table
    but X = Suppose Receipt_no 1 Has 5 Items ie
    Receipt_no 1 SerialNo 1
    Receipt_no 1 SerialNo 2
    Receipt_no 1 SerialNo 3
    Receipt_no 1 SerialNo 4
    Receipt_no 1 SerialNo 5

    Recordcount will give all records but i want only Records Against Corresponding Receipt_no 1
    When Receipt_no 2 Then Condition should fail

    Yes Sir,

  14. #14
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: MSFlexgrid Data Retrieval With ADO....

    for that case use the sql query like

    select * from <tablename> where <fieldname>=<value>

    so that it will return only the 5 records which satisfies the conditio, then you can use the x=rs.recordcound method.

    could you show ur sql statement so that i can help

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    Jan 2006
    Location
    PUNE
    Posts
    222

    Exclamation Re: MSFlexgrid Data Retrieval With ADO....

    [Highlight=VB]
    Select * from Inward_items Where Receipt+no = txtreceipt_no.text
    [Highlight=VB]

    But How do i Show in Flexgrid

  16. #16
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: MSFlexgrid Data Retrieval With ADO....

    Here's a Sub I recently wrote, you can just put this into your project and use it as in the "'Example usage" comments.
    Code:
    Public Sub FillFromRecordset_FlexGrid(p_ctlFlexGrid As Control, _
                                          p_objRecordset As Object, _
                                          p_booFieldNamesAsHeaders As Boolean)
    'Fill an MSFlexGrid control with values from a recordset
    'By Si_the_geek, VBForums
    
    'Parameters:
    '  p_ctlFlexGrid            - The FlexGrid control to fill
    '  p_objRecordset           - The Recordset to get the data from (can be ADO/DAO)
    '  p_booFieldNamesAsHeaders - True to show field names as column headers
    
    'Example usage:
    '  Call FillFromRecordset_FlexGrid(MSFlexGrid1, objRS, True)
    
    
    Dim lngCol As Long
    Dim lngRow As Long
    Dim booOldRedraw As Boolean
    
      With p_ctlFlexGrid
          'Set up the required number of columns
        .Cols = p_objRecordset.Fields.Count
        .FixedCols = 0
    
          'If specified, show field names as headers
        If p_booFieldNamesAsHeaders Then
          .FixedRows = 1
          For lngCol = 0 To p_objRecordset.Fields.Count - 1
            .TextMatrix(0, lngCol) = p_objRecordset.Fields(lngCol).Name
          Next lngCol 
        End If
    
          'remove all rows except any headers, and the first data row (cannot be removed)
        .Rows = .FixedRows + 1
    
          'Turn off screen updates (much faster to fill the data)
        booOldRedraw = .Redraw
        .Redraw = False
    
          'Check if there is any data
        If p_objRecordset.EOF Then
            'if there is no data, only allow the required blank row, and hide it (height=0)
          .AddItem ""
          .RemoveItem .FixedRows
          .RowHeight(.FixedRows) = 0
        Else
    
            'We have data, add it one row at a time
            '(nb: there are various ways to do this, this way is quick, and easy to read)
          lngRow = .Rows
          Do While Not p_objRecordset.EOF
              'Add the row (empty)
            .AddItem ""
              'Set the values once cell at a time (avoids problems with Nulls and data containing grid delimiters)
            For lngCol = 0 To p_objRecordset.Fields.Count - 1
                'if you want to format the text for some columns differenly, you can _
                'use If/Else or Select Case here - but its better to do that in your SQL _
                'statement, as it is more efficient, and allows this sub to be re-used easily
              .TextMatrix(lngRow, lngCol) = p_objRecordset.Fields(lngCol).Value & ""
            Next lngCol
              'Increment our row counter
            lngRow = lngRow + 1
              'Move to the next row of data
            p_objRecordset.MoveNext
          Loop
    
            'Remove the blank row we left at the top
          .RemoveItem .FixedRows
    
        End If
    
          'Re-enable screen updates (if was previously enabled)
        .Redraw = booOldRedraw
    
          'Force a redraw of the grid
        .Refresh
      End With
    
    End Sub
    Last edited by si_the_geek; Nov 21st, 2007 at 06:43 PM. Reason: changed to Code tags for easier copy&paste, and extended comments

  17. #17

    Thread Starter
    Addicted Member
    Join Date
    Jan 2006
    Location
    PUNE
    Posts
    222

    Re: MSFlexgrid Data Retrieval With ADO....

    Thanks Super Moderator

    I will Do it........


  18. #18
    New Member
    Join Date
    Nov 2016
    Location
    Bali, Indonesia
    Posts
    14

    Re: MSFlexgrid Data Retrieval With ADO....

    Quote Originally Posted by si_the_geek View Post
    Here's a Sub I recently wrote, you can just put this into your project and use it as in the "'Example usage" comments.
    Code:
    Public Sub FillFromRecordset_FlexGrid(p_ctlFlexGrid As Control, _
                                          p_objRecordset As Object, _
                                          p_booFieldNamesAsHeaders As Boolean)
    'Fill an MSFlexGrid control with values from a recordset
    'By Si_the_geek, VBForums
    
    'Parameters:
    '  p_ctlFlexGrid            - The FlexGrid control to fill
    '  p_objRecordset           - The Recordset to get the data from (can be ADO/DAO)
    '  p_booFieldNamesAsHeaders - True to show field names as column headers
    
    'Example usage:
    '  Call FillFromRecordset_FlexGrid(MSFlexGrid1, objRS, True)
    
    
    Dim lngCol As Long
    Dim lngRow As Long
    Dim booOldRedraw As Boolean
    
      With p_ctlFlexGrid
          'Set up the required number of columns
        .Cols = p_objRecordset.Fields.Count
        .FixedCols = 0
    
          'If specified, show field names as headers
        If p_booFieldNamesAsHeaders Then
          .FixedRows = 1
          For lngCol = 0 To p_objRecordset.Fields.Count - 1
            .TextMatrix(0, lngCol) = p_objRecordset.Fields(lngCol).Name
          Next lngCol 
        End If
    
          'remove all rows except any headers, and the first data row (cannot be removed)
        .Rows = .FixedRows + 1
    
          'Turn off screen updates (much faster to fill the data)
        booOldRedraw = .Redraw
        .Redraw = False
    
          'Check if there is any data
        If p_objRecordset.EOF Then
            'if there is no data, only allow the required blank row, and hide it (height=0)
          .AddItem ""
          .RemoveItem .FixedRows
          .RowHeight(.FixedRows) = 0
        Else
    
            'We have data, add it one row at a time
            '(nb: there are various ways to do this, this way is quick, and easy to read)
          lngRow = .Rows
          Do While Not p_objRecordset.EOF
              'Add the row (empty)
            .AddItem ""
              'Set the values once cell at a time (avoids problems with Nulls and data containing grid delimiters)
            For lngCol = 0 To p_objRecordset.Fields.Count - 1
                'if you want to format the text for some columns differenly, you can _
                'use If/Else or Select Case here - but its better to do that in your SQL _
                'statement, as it is more efficient, and allows this sub to be re-used easily
              .TextMatrix(lngRow, lngCol) = p_objRecordset.Fields(lngCol).Value & ""
            Next lngCol
              'Increment our row counter
            lngRow = lngRow + 1
              'Move to the next row of data
            p_objRecordset.MoveNext
          Loop
    
            'Remove the blank row we left at the top
          .RemoveItem .FixedRows
    
        End If
    
          'Re-enable screen updates (if was previously enabled)
        .Redraw = booOldRedraw
    
          'Force a redraw of the grid
        .Refresh
      End With
    
    End Sub
    Hi how to set column width to auto-fit with this ?
    i usually set it manually with msflexgrid1.FormatString

  19. #19
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: MSFlexgrid Data Retrieval With ADO....

    You can auto-fit columns using the code linked in my signature ("FlexGrid: AutoSize columns"): http://www.vbforums.com/showpost.php...0&postcount=18

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width