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