FlexGrid as DataGrid - CodeProject

Download source - 52.18 KB

Introduction

I create a new AciveX control (FDGrid) to let FlexGrid control as DataGrid control. With this ActiveX, you can:

Connect with database
Fill FlexGrid with table
Edit and add records
MoveFirst, MoveLast, MoveNext and MovePrevious
Auto save (Update) after Edit cell
Edit grid without connecting to database file

Background

When using my ActiveX control (FDGrid), if you use it as DataGrid you must load the grid with data from the database file. See the code in the form (Form1). You can edit the grid without loading data. See the code in the form (Form2). Some properties and methods of my ActiveX are in the following table:

Method/Property	Definition	Example
`DatabaseName`	Set database name	`FDGrid1.DatabaseName = FileName`
`DataSource`	Set `Recordset` to grid	`FDGrid1.DataSource = rs`
`AddNewRow`	Add new record to grid	`FDGrid1.AddNewRow`
`DeleteRow`	Delete record	`FDGrid1.DeleteRow`
`Update`	Save record after add	`FDGrid1.Update`
`CancelUpdate`	Cancel update	`FDGrid1.CancelUpdate`
`MoveFirst`	Move to first record	`FDGrid1.MoveFirst`
`MoveLast`	Move to last record	`FDGrid1.MoveLast`
`MoveNext`	Move to next record	`FDGrid1.MoveNext`
`MovePrevious`	Move to previous record	`FDGrid1.MovePrevious`

Other Properties and Methods as (MSFlexGrid) control.

To use my ActiveX (FDGrid), you must add only one file from files: "Microsoft DAO 3.51 (or 3.6) Object Library" to the reference and add (FDGrid) control to the Toolbox.

Using the Code

Form1 (connect to the database file and use ActiveX as MSDataGrid)

LoadData() procedure:

Dim MyDataFile As String
Dim MyDb As Database
Dim MySql As String

   MyDataFile = App.Path + "\DataFile\" + "Sale.mdb"
   Set MyDb = OpenDatabase(MyDataFile, False, False, ";pwd=" & "")
   MySql = "SELECT * FROM Products Order by ProductID"
   FDGrid1.DatabaseName = MyDataFile
   FDGrid1.RecordSource = MySql
   FDGrid1.RecordsetType = vbRSTypeDynaset
   FDGrid1.Refresh

Form_Load() procedure:

'Call LoadData procedure
LoadData

cmdAdd_Click() procedure:

' add new record to data base file
FDGrid1.AddNewRow

cmdCancel_Click() procedure:

' don't save record
FDGrid1.AddNewRow

cmdDelete_Click() procedure:

' delete record from data base file
FDGrid1.DeleteRow

cmdUpdate_Click() procedure:

' save record to data base file
FDGrid1.Update

Refer to Form1 to see code for MoveFirst, MoveLast, MoveNext and MovePrevious.

Form2 (use ActiveX to edit grid)

LoadData() procedure:

FDGrid1.Cols = 8 'grid has 8 columns
FDGrid1.Rows = 15 'grid has 15 rows

'set alignment of fixed row to center
For c = 1 To FDGrid1.Cols - 1
   FDGrid1.TextMatrix(0, c) = "Col " & CStr(c)
   FDGrid1.ColAlignmentHeader(c) = flexAlignCenterCenter
Next c

' fill some rows
For r = 1 To 5
   For c = 1 To FDGrid1.Cols - 1
      FDGrid1.TextMatrix(r, c) = "Cell(" & CStr(r) & "," & CStr(c) & ")"
   Next c
Next r

' You can edit any cell, just click any cell then try to edit it.

Remarks

When extracting the FDGrid.zip file, you can find the FDGrid.ocx file in the ActiveXcontrol folder.

Find the database file Sale.mdb in the DataFile folder.

Find the prjBoundFlex project (to test the ActiveX control) in the FDGrid folder.

This project has three forms (frmMain, Form1 and Form2).

Form1 is to connect grid with database file.
Form2 is to edit grid.

Last Words

I hope this article is useful and helps you when your application needs to connect with the database or needs to edit grid. Please tell me if you have any ideas or if you find any problems. Thanks to Code Project and thanks to all.

History

12^th May, 2008: Initial post

Mostafa Kaisoun
M_Kaisoun@hotmail.com

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Mostafa Kaisoun

Egypt

Member

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board. (secure sign-in)

Search this forum
	Profile popups Noise Layout Per page

fdgrid control in vb.net2003

Mostafa Kaisoun

17:22 7 Sep '08

Hi, Friend

1- Be sure about code of database (database file, connection, SQL string, table... etc.).
2- Perhaps you forgot to specify Ros and Cols for grid.
3- You can try following code (which I test it and no errors) then change following as your application:
name of file.
name of table.
name of fields.

Imports System
Imports System.Data.OleDb
Imports FGrid

Public Class Form1
Inherits System.Windows.Forms.Form
Private Con As OleDbConnection
Private da As OleDbDataAdapter
Private ds As DataSet
Private str As String

Private Sub LoadMyData()
Dim strCon As String
Try
strCon = "provider=microsoft.jet.oledb.4.0;" _
& "Password="""";data source=" & Application.StartupPath & "\MyPhone.mdb"
Con = New OleDbConnection
Con.ConnectionString = strCon
Con.Open()
str = ""
str = "select * from Phone"
da = New OleDbDataAdapter(str, Con)
ds = New DataSet
ds.Clear()
da.Fill(ds, "Phone")
Catch ex As Exception
MsgBox(ex.ToString)
If Con.State = ConnectionState.Open Then
Con.Close()
End If
Exit Sub
End Try
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim i As Integer
fd1.Rows = ds.Tables("Phone").Rows.Count + 1
fd1.Cols = ds.Tables("Phone").Columns.Count + 1
For i = 0 To ds.Tables("Phone").Rows.Count - 1
fd1.set_TextMatrix(i + 1, 1, i + 1)
fd1.set_TextMatrix(i + 1, 2, ds.Tables("Phone").Rows(i).Item("Name"))
fd1.set_TextMatrix(i + 1, 3, ds.Tables("Phone").Rows(i).Item("Phone"))
Next
Con.Close()
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.LoadMyData()
End Sub
End Class

Note: You can use SqlClient instead of OleDb.

Thanks,
Mostafa Kaisoun