Posts tagged datagridview
Assuming you are searching for the name John, the following procedure will search all existing columns regardless
Dim x As Integer = 0 While x < DataGridView1.Rows.Count Dim y As Integer = 0 While y < DataGridView1.Rows(x).Cells.Count Dim c As DataGridViewCell = DataGridView1.Rows(x).Cells(y) If Not c.Value Is DBNull.Value Or Nothing Then If CType(c.Value, String) = "John" Then MessageBox.Show("Found!") End If End If System.Math.Min(System.Threading.Interlocked.Increment(y), y - 1) End While System.Math.Min(System.Threading.Interlocked.Increment(x), x - 1) End While MessageBox.Show("Search complete!") End Sub
assuming you have a datagridview that has data in it.
you want to avoid recalling sql from the database & just want to filter the datagrid.
you can do the following, What you need (1 datagridview (data), 1 button (search), 1 textbox (string to search), 1 variable to precise the criteria your searching for)
Dim findcrit as string Dim dt As DataTable = Ds_Pos.Employees Dim dv As New DataView(dt) Dim _RowFilter As String = "" Dim _FieldType = dt.Columns(findcrit).DataType.ToString Select Case _FieldType Case "System.Int32" _RowFilter = "convert(" & findcrit & ", 'System.String') like '" & Me.TextBox1.Text & "'" Case "System.Int64" _RowFilter = "convert(" & findcrit & ", 'System.String') like '" & Me.TextBox1.Text & "'" Case "System.Double" _RowFilter = "convert(" & findcrit & ", 'System.String') like '" & Me.TextBox1.Text & "'" Case "System.String" _RowFilter = findcrit & " like '" & TextBox1.Text & "*'" End Select dv.RowFilter = _RowFilter EmployeesDataGridView.DataSource = dv
The cases are to cast the type of variable from any type to string because you can only use the method on string data types.
the idea is to move the content to a data table then filter then return it back to the datatable.
To validate the text entered into a datagrid add a handler to the DatagridTextboxColumn’s validating event. Here is some sample code.
Dim ds As New DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim conn As SqlConnection
Dim strConn As String
Dim strSQL As String
Dim da As SqlDataAdapter
strConn = “Server = (local);”
strConn &= “Database = NorthWind;”
strConn &= “Integrated Security = SSPI;”
conn = New SqlConnection(strConn)
da = New SqlDataAdapter(“Select * From Products”, conn)
Dim ts As New DataGridTableStyle
ts.MappingName = ds.Tables(“Products”).TableName
Dim colDiscontinued As New DataGridBoolColumn
.MappingName = “Discontinued”
.HeaderText = “Discontinued”
.Width = 80
Dim colName As New DataGridTextBoxColumn
.MappingName = “ProductName”
.HeaderText = “Product Name”
.Width = 180
AddHandler colName.TextBox.Validating, AddressOf CellValidating
ts = Nothing
colDiscontinued = Nothing
colName = Nothing
DataGrid1.DataSource = ds.Tables(“Products”)
Private Sub CellValidating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs)
A common problem faced in datagridview is how to format the date in the grid, here is a small way to do it:
Private Sub dataGridView1_CellParsing(ByVal sender As Object, ByVal e As DataGridViewCellParsingEventArgs)
If e.ColumnIndex = 0 Then
Dim [date] As String = DirectCast(e.Value, String)
Dim month As String = [date].Substring(0, 2)
Dim day As String = [date].Substring(2, 2)
Dim year As String = [date].Substring(4, 4)
e.Value = New DateTime(Convert.ToInt32(year), Convert.ToInt32(month), Convert.ToInt32(day))
e.ParsingApplied = True
Catch ex As Exception
If you are working with a where a user can create and edit data in the same form you may run into the problem of trying to set the BindingSource to a given record in a DataSet you want to work with.
For example let’s say you have a in a . When the user double clicks a given row in the DataGridView a form opens so they can edit the data. More times than not (no matter how many demos you watch) user’s do not edit data values in DataGridViews. Although this is a supported feature, are too complex. Imagine having to edit a contact in a in Outlook for example, not fun and not very user friendly. Why every I watch shows editing in DataGridViews is beyond me. I digress though.
When the new form is instantiated we need to pass two things: the dataset we are working with, and the IDof the item we want to . We can pass this information into the constructor of the form without any trouble as seen below. By passing in the DataSet we can add new records, delete records or whatever we need to do. The other benefit is since the dataset is passed by reference, any changes to it will automatically appear in the row the just double clicked on. In order for the form fields to be auto bound to the record the user clicked on we need to set the DataBinding of the controls in the form to a BindingSource. The BindingSource serves as between the UI and data store. Here’s a sample “CellDoubleClick” event that would happen in the DataGridView:
1 int id = Convert.ToInt32(DataGridView.Rows[e.RowIndex].Cells.Value);
2 MyForm myForm = new MyForm(dataSet, id);
As the form loads the problem we face is having to tell the BindingSource in the form which record we want to set as the BindingSource.Current item. At first glance it looks like we would just get the DataRow and then set it as the Current property has only a get accessor. Instead we need to use the . Wrong. The BindingSource.Current BindingSource.Position property. Note: Any change to the position property updates the current property used by the bindingsource. The problem we face now is the BindingSource.Position property wants to know the index of the item in the underlying DataSource.
We don’t know the index value used by the BindingSource but we do know the ID we want to use. This is where the BindingSource.Find() method comes in. Using the find method we can basically search the underlying datasource and retrieve the index of a given item. Here’s an example:
contactBindingSource.Position = contactBindingSource.Find(“Id”, 2);
Once the line above runs, it is going to search the column “Id” in the DataSource associated with the BindingSource and return the index of that item.
What if you use the same form to create a new item? Simple, call the BindingSource.AddNew() method which will add a new item to the underlying DataSource. Then move the index to the last item. Here’s a sample:
Since items are always added at the end of the associated data source the MoveLast() method will set to the last item in the underlying datasource.
I hope this makes sense because it is an easy way to have a form bound to a DataSet and use the same form to perform “CRUD” operations with.