Use a different kind of grid in your applications


My prior post (Create a .Net UserControl that calls a web service that acts as an ActiveX control to use in Excel, VB6, Foxpro) shows how to create a .Net control that you can use in VFP or VB6 that takes advantage of the .Net framework.


 


Here’s an example of how to use the same steps to create a DataGridView ActiveX control that you can populate using an ADO RecordSet. Follow the steps of the prior post, but instead of adding a reference to a web service, add a reference to .Net ADODB.( C:\Program Files\Microsoft.NET\Primary Interop Assemblies\adodb.dll)


Then add a button and a DataGridView. Change the Anchor property of the DataGridView to Top,Left,Bottom,Right.


 


The button code gets all the Customer data into an ADODB Recordset on the .Net side using the VFP OleDB provider. The Fox form code gets all the Orders data from the OleDB provider, then calls the SetRS method to send the data to the .Net side, which converts the Recordset to a DataTable,which can then be bound to the DataGridView.


 


The Fox code sets the Anchor of the control, so when the form resizes, the grid resizes too. It also adds a Fox button and a Fox textbox, so you can experiment with how focus changes between the ActiveX Control and the Fox native controls with the mouse and keyboard (Tab, Ctrl-Tab, arrow keys)


 


Notice how the column colors are changed based on the data. It’s a little different from the VFP way using DynamicBackColor, DynamicForecolor. Try clicking on the column headers to sort the columns, or rearrange the order of the columns by dragging/dropping the headers.


 


If you hit the control’s button, the data will switch to the Customer table, showing how the data can be populated from either .Net or VFP code.


 


For another DataGridView sample, see The VB version of the Blog Crawler


 


The VB.Net code (be sure to change the data paths to your machine):


 


Imports System.Runtime.InteropServices


 


<Microsoft.VisualBasic.ComClass()> Public Class VBNetCtrlGrid


    Private _TestString As String


    Public Sub SetRs(ByVal rs As Object)


        Me.DataGridView1.AutoGenerateColumns = True


        Me.DataGridView1.AllowUserToOrderColumns = True


        Me.DataGridView1.DataSource = RStoDT(CType(rs, ADODB.Recordset))


        Me.DataGridView1.AutoResizeColumns()


    End Sub


 


    Function RStoDT(ByVal rs As ADODB.Recordset) As DataTable


        Dim dt As New DataTable(“Test”)


        For Each fld As ADODB.Field In rs.Fields


            Dim ttype As Type = Nothing


            Select Case fld.Type


                Case ADODB.DataTypeEnum.adNumeric


                    ttype = GetType(Double)


                Case ADODB.DataTypeEnum.adCurrency


                    ttype = GetType(Decimal)


                Case ADODB.DataTypeEnum.adChar


                    ttype = GetType(String)


                Case ADODB.DataTypeEnum.adWChar


                    ttype = GetType(String)


                Case ADODB.DataTypeEnum.adInteger


                    ttype = GetType(Integer)


                Case ADODB.DataTypeEnum.adDBTimeStamp


                    ttype = GetType(DateTime)


                Case ADODB.DataTypeEnum.adDBDate


                    ttype = GetType(DateTime)


                Case Else


                    System.Diagnostics.Debug.Assert(False)


            End Select


            dt.Columns.Add(New DataColumn(fld.Name, ttype))


        Next


        Dim vals(rs.Fields.Count – 1) As Object


        Do While Not rs.EOF


            Dim nFldCnt = 0


            For Each fld As ADODB.Field In rs.Fields


                vals(nFldCnt) = fld.Value


                nFldCnt += 1


            Next


            dt.Rows.Add(vals)


            rs.MoveNext()


        Loop


        Return dt


    End Function


 


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click


        Dim oConn As New ADODB.Connection


        oConn.Open(“Provider=vfpoledb.1;data source=d:\fox90\samples\data\testdata.dbc”)


        Dim oRS As ADODB.Recordset = oConn.Execute(“select * from customer”)


        Me.SetRs(oRS)


    End Sub


 


    <ComRegisterFunction()> _


    Public Shared Sub Register(ByVal t As Type)


        ComRegistration.RegisterControl(t)


    End Sub


 


    <ComUnregisterFunction()> _


    Public Shared Sub Unregister(ByVal t As Type)


        ComRegistration.UnregisterControl(t)


    End Sub


 


    Private Sub DataGridView1_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting


        If Me.DataGridView1.Columns(e.ColumnIndex).Name.ToLower = “cust_id” Then


            If e.Value IsNot Nothing AndAlso e.Value.ToString < “M” Then


                e.CellStyle.BackColor = Color.CadetBlue


                e.CellStyle.SelectionBackColor = Color.Blue


                e.CellStyle.ForeColor = Color.Crimson


            End If


        End If


    End Sub


End Class


 


 


The Fox code:


 


CLEAR ALL


CLEAR


 


PUBLIC ox


ox=CREATEOBJECT(“myform”)


ox.show


 


 


DEFINE CLASS myform AS form


          left=300


          allowoutput=.f.


          width=800


          height=600


          ADD OBJECT cmd as commandbutton WITH caption=“Click”


          ADD OBJECT txt as textbox WITH left=121


          PROCEDURE Init


                   this.AddObject(“oc”,“myoc”)


                   this.oc.width=thisform.Width


                   this.oc.height=thisform.Height


                   this.oc.visible=1


                   LOCAL oConn as ADODB.Connection


                   LOCAL oRS as adodb.recordset


                   oConn=CREATEOBJECT(“adodb.connection”)


                   *Change path and query to data on your machine


                   oConn.open(“Provider=vfpoledb.1;data source=d:\fox90\samples\data\testdata.dbc”)


                   oRS= oconn.Execute(“select * from orders”)


                   this.oc.object.SetRs(ors)


ENDDEFINE


 


 


DEFINE CLASS myoc AS olecontrol


          oleclass=“VBNetCtrlGrid.VBNetCtrlGrid”


          PROCEDURE init


                   this.top=30


                   this.width=thisform.width


                   this.height = thisform.height-30


                   this.anchor=15


 


ENDDEFINE


 


 

Comments (3)

  1. Good day, my personal experience says to me that having used controls activex inside applications Fox is not the most advisable thing since these degrade the performance of the solutions, in this example it is needed of a connection from .Net up to VFP, this will be late a few instants before being able to see the information, for what I suggest that VFP’s controls should fortify for do not see us obliged to use activex that can present loss of efficiency of applications in production.

  2. Rnamro says:

    It would be nice if you can comment your code so that it is easier to understand what and why…

    Thanks