Posts tagged datagridview

Searching through all columns in datagridview

0
15 years
by in Visual Basic.Net

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

Filtering a datagridview

0
15 years
by in Visual Basic.Net

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.

Validate data entered into textboxcolumn

0
15 years
by in Visual Basic.Net

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)
da.Fill(ds, “Products”)

Dim ts As New DataGridTableStyle
ts.MappingName = ds.Tables(“Products”).TableName

Dim colDiscontinued As New DataGridBoolColumn
With colDiscontinued
.MappingName = “Discontinued”
.HeaderText = “Discontinued”
.Width = 80
End With

Dim colName As New DataGridTextBoxColumn
With colName
.MappingName = “ProductName”
.HeaderText = “Product Name”
.Width = 180
End With
AddHandler colName.TextBox.Validating, AddressOf CellValidating

ts.GridColumnStyles.Add(colName)
ts.GridColumnStyles.Add(colDiscontinued)

DataGrid1.TableStyles.Add(ts)

ts = Nothing
colDiscontinued = Nothing
colName = Nothing

DataGrid1.DataSource = ds.Tables(“Products”)
End Sub

Private Sub CellValidating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs)
Debug.WriteLine(DirectCast(sender, DataGridTextBox).Text)
End Sub

Format input date in datagridview

0
15 years
by in Visual Basic.Net

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)

Try

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

MessageBox.Show(“parsing error!”)

End Try

End If
End Sub

Managing BindingSource Position in Data Driven WinForms

0
15 years
by in Visual Basic.Net

If you are working with a Windows Form 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 DataGridView in a Smart Client.  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, business objects are too complex.  Imagine having to edit a contact in a single row in Outlook for example, not fun and not very user friendly.  Why every Microsoft demo 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 edit.  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 user 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 the go between 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[0].Value);

2 MyForm myForm = new MyForm(dataSet, id);

3 myForm.Show();

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.  Wrong.  The BindingSource.Current property has only a get accessor.  Instead we need to use the 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:

bindingSource.AddNew();
 bindingSource.MoveLast();

Since items are always added at the end of the associated data source the MoveLast() method will set the index 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.

Go to Top