Filtering a datagridview with a datagridviewcomboboxcell in a frozen row using vb.net

• • Reply

  8 years ago

  by DonOne

  Don Sutton

  Sydney, Australia

  Joined 11 years ago

  I have been trying to construct a datagridview with an Excel style autofilter in the first row in VB.net 3.5 I Searched and downloaded the DatagridViewAutofilter from the MSDN website but this appears to work only from the design mode where you have database already assigned as a datasource. I am retuning data from a remote database then storing this data in a local MS Server table which I create according to the columns retrieved from the remote database. I then want to autofilter on various columns and then export the filtered data to Excel or chart the data within my VB project. It is the AutoFilter that I am having problems with. The other option is to write code for Excel after autofiltering in Excel to count and calculate specific values to produce a chart. It is the Autofilter in the DataGridView I am finding as the stumbling block. Code below Private Sub ApplyAutoFilterButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ApplyAutoFilterButton1.Click Dim Empty As String = "" Call ConnectDatabase() errStage = 3000 If Connect = False Then Msg = "Unable to filter data using a AutoFilter as there is no" & vbCrLf Msg = Msg & "database connection. Try again later." Title = "Cannot Connect to SQL Server Error" MsgBox(Msg, MsgBoxStyle.Information, Title) End If errStage = 3001 dgv = DGVSummary ColumnCnt = dgv.Columns.Count Dim r As Integer = 0 errStage = 3002 Try errStage = 3003 'Dim DatGrid As DataGridViewRow Dim FilterName(dgv.Columns.Count - 1) As String Dim nrow As DataGridViewRow = New DataGridViewRow Dim col As Integer 'Dim SelRow As Integer With dgv ColumnCnt = .Columns.Count errStage = 3004 'Use a query to return Distinct row data then add to each row of the columns data into each columns combobox in the first row 'Use an array of query statements or assign the column/field name as a parameter in the queries - iterate the columns '1) get the column header names '2) to create ComboBox cells '3) Add item rows to the comboBox as a inner outer loop '4) freeze the first row 'Job 1 - Get the column names in to array FilterName(column) For col = 0 To ColumnCnt - 1 errStage = 3005 FilterName(col) = .Columns(col).HeaderText Next errStage = 3006 Dim ComboCell As DataGridViewComboBoxCell = New DataGridViewComboBoxCell .Rows.Insert(0) 'Insert a blank row at top 'Job 2 - Add a combobox cell 'Create and Populate Combobox dropdown list errStage = 3007 .EditMode = DataGridViewEditMode.EditProgrammatically .Visible = True 'Now show it For col = 0 To ColumnCnt - 1 errStage = 3008 Dim ListStr As New List(Of String) 'Obsolete - Debug then remove and retest Dim ListDataSet As New System.Data.DataSet errStage = 3009 MySQL = "SELECT DISTINCT " & FilterName(col) & " FROM " & TblName & " ORDER BY " & FilterName(col) & ";" Dim objDataAdaptor As New SqlDataAdapter(MySQL, MySQLConnection) Dim ListCnt As Integer errStage = 3010 objDataAdaptor.Fill(ListDataSet) r = 0 ListCnt = ListDataSet.Tables(0).Rows.Count - 1 errStage = 3011 If ListCnt > 0 Then errStage = 3012

  Report abuse