none
grid with two data bases

    Question

  • Hi,
    is it possible to connect a grid control with two data bases (who are in relation) so I won't have to create one database that will merge the other two. Please give me a example or an easy solution.
    Thanks.
    Wednesday, September 02, 2009 11:11 AM

Answers

  • Simply have a select query and you may use as recorsource.

    Selet table1.field1 as My_Field1,table2.field1 as My_Field2 from tabl2,table2 where table1.field1=table2.field1 into cursor My_Cursor Readwrite

    thisform.grid1.recordsource="My_Cursor"
    U.PARANTHAMAN
    • Marked as answer by gorgiRankovski Thursday, September 03, 2009 12:05 AM
    Wednesday, September 02, 2009 11:56 AM
  • Ledger is not a style peroperty of the grid (grid do not have such a property), it is something builders\wizards use to name a style. Instead when you say ledger, it is DynamicBackColor property of each column that is set to an expression like:

    iif( recno() % 2 = 0, rgb(128,128,255), rgb(255,255,255))

    * note color is not the one used in 'ledger' I write one off the top of my head

    When you change the recordsource 'directly' grid drops off all existing columns and their objects, recreates the columns from new source. There are many ways to solve it and the simplest solution is to temporarily set recordsource to an empty string. ie:

    local lcRecordSource
    lcRecordSource = thisform.myGrid.RecordSource && save source
    thisform.myGrid.RecordSource = ""
    * do whatever with the source here
    thisform.myGrid.RecordSource = m.lcRecordSource && assign back the source


    This way you can keep the settings you have defined at design time. One other simple solution is to prevent it from the start by using a recordsourcetype of 4 as I said before. Then all you need is:

    with thisform.myGrid
     .RecordSource = .RecordSource
    endwith

    to 'refresh' the contents. 

    And of course you can set anything related to a grid down to the smallest detail in code using Grid, Column, Header and contained controls' property/event/methods. ie:

    Public oForm
    oForm = Createobject('myForm')
    oForm.Show()
    Messagebox('Press OK to continue and add grid in code')
    oForm.AddGrid()

    Define Class myForm As Form
      Height = 400
      Width = 600


      Procedure AddGrid
        Thisform.Newobject("myGrid",'Grid')
        Thisform.Tag = 'USA'
        With Thisform.myGrid
          .RecordSourceType = 4
          .RecordSource = 'select * from Customer'+;
            ' inner join orders'+;
            '     on customer.cust_id = orders.Cust_id'+;
            ' where Customer.Country = thisform.Tag into cursor crsMygrid'
          .SetAll('DynamicBackColor', '(iif(recno()%2=0, 0xFFFF00, 0xFFFFFF))')
          .Height = 400
          .Width = 600
          .Anchor = 15
          .Visible = .T.
          .AutoFit()
        Endwith
      Endproc

    Enddefine

    • Marked as answer by gorgiRankovski Thursday, September 03, 2009 1:15 PM
    Thursday, September 03, 2009 12:18 PM

All replies

  • Simply have a select query and you may use as recorsource.

    Selet table1.field1 as My_Field1,table2.field1 as My_Field2 from tabl2,table2 where table1.field1=table2.field1 into cursor My_Cursor Readwrite

    thisform.grid1.recordsource="My_Cursor"
    U.PARANTHAMAN
    • Marked as answer by gorgiRankovski Thursday, September 03, 2009 12:05 AM
    Wednesday, September 02, 2009 11:56 AM
  • is it possible to connect a grid control with two data bases (who are in relation) so I won't have to create one database that will merge the other two.

    You can create a local view, which is just a SQL staement that is stored in the DBC,  that joins the 2 tables and use the view as the grid's RecordSource. See the topic "How to: Create Local Views" in the VFP help file for step by step instructions on how to create a local view.
    Marcia G. Akins
    Wednesday, September 02, 2009 1:59 PM
  • Hi,
    is it possible to connect a grid control with two data bases (who are in relation) so I won't have to create one database that will merge the other two. Please give me a example or an easy solution.
    Thanks.
    Simply add columns and use alias.fieldname as controlsource. If it is a 1-to-many relation you may need to set skip on. If it is a many-to-1 you don't. Yet another and maybe the easiest solution is to set grid's recordsourcetype to 4 (SQL) and recordsource to an SQL statement. ie:

    * Grid init
    with this 
     .RecordSourcetype = 4
     text to .RecordSource noshow pretext 15
    select cus.Cust_Id, cus.Company, ord.Order_Id, ord.Order_Date 
      from customer cus
      left join orders ord on cus.Cust_id == ord.Cust_Id
      into cursor crsMyData
     endtext
    
    endwith


    Wednesday, September 02, 2009 7:18 PM
  • Thanks guys it worked. I've never used SQL commands with FoxPro till today, but they are cool so I will continue my work with SQL. Thanks a lot again
    Thursday, September 03, 2009 12:07 AM
  • One more question. Can I change the grid style dynamically (with code, not with builder)? In the builder I set the style to ledger, but every time I change the recordsource, the style changes.
    I've searched the properties of the grid and I couldn't find a solution.
    Thursday, September 03, 2009 11:25 AM
  • Ledger is not a style peroperty of the grid (grid do not have such a property), it is something builders\wizards use to name a style. Instead when you say ledger, it is DynamicBackColor property of each column that is set to an expression like:

    iif( recno() % 2 = 0, rgb(128,128,255), rgb(255,255,255))

    * note color is not the one used in 'ledger' I write one off the top of my head

    When you change the recordsource 'directly' grid drops off all existing columns and their objects, recreates the columns from new source. There are many ways to solve it and the simplest solution is to temporarily set recordsource to an empty string. ie:

    local lcRecordSource
    lcRecordSource = thisform.myGrid.RecordSource && save source
    thisform.myGrid.RecordSource = ""
    * do whatever with the source here
    thisform.myGrid.RecordSource = m.lcRecordSource && assign back the source


    This way you can keep the settings you have defined at design time. One other simple solution is to prevent it from the start by using a recordsourcetype of 4 as I said before. Then all you need is:

    with thisform.myGrid
     .RecordSource = .RecordSource
    endwith

    to 'refresh' the contents. 

    And of course you can set anything related to a grid down to the smallest detail in code using Grid, Column, Header and contained controls' property/event/methods. ie:

    Public oForm
    oForm = Createobject('myForm')
    oForm.Show()
    Messagebox('Press OK to continue and add grid in code')
    oForm.AddGrid()

    Define Class myForm As Form
      Height = 400
      Width = 600


      Procedure AddGrid
        Thisform.Newobject("myGrid",'Grid')
        Thisform.Tag = 'USA'
        With Thisform.myGrid
          .RecordSourceType = 4
          .RecordSource = 'select * from Customer'+;
            ' inner join orders'+;
            '     on customer.cust_id = orders.Cust_id'+;
            ' where Customer.Country = thisform.Tag into cursor crsMygrid'
          .SetAll('DynamicBackColor', '(iif(recno()%2=0, 0xFFFF00, 0xFFFFFF))')
          .Height = 400
          .Width = 600
          .Anchor = 15
          .Visible = .T.
          .AutoFit()
        Endwith
      Endproc

    Enddefine

    • Marked as answer by gorgiRankovski Thursday, September 03, 2009 1:15 PM
    Thursday, September 03, 2009 12:18 PM
  • It worked with saving the old source for the grid. Thanks a lot, it really helped me and saved me lots of time ;]
    Thursday, September 03, 2009 1:17 PM