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))


    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


            End Select

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


        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





        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”)


    End Sub


    <ComRegisterFunction()> _

    Public Shared Sub Register(ByVal t As Type)


    End Sub


    <ComUnregisterFunction()> _

    Public Shared Sub Unregister(ByVal t As Type)


    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:









DEFINE CLASS myform AS form





          ADD OBJECT cmd as commandbutton WITH caption=“Click”

          ADD OBJECT txt as textbox WITH left=121

          PROCEDURE Init





                   LOCAL oConn as ADODB.Connection

                   LOCAL oRS as adodb.recordset


                   *Change path and query to data on your machine

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

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





DEFINE CLASS myoc AS olecontrol


          PROCEDURE init



                   this.height = thisform.height-30






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…