Paul Edward Pagente Paul Edward Pagente - 2 months ago 17
Vb.net Question

Find and move to a specific cell in a datagridview using textbox

Good Morning

I have a program in VB.Net that looks like this.

enter image description here

It has a lot of data and I have a search module like this.

enter image description here

Here is the code on how I populate,

Dim con1 As MySqlConnection = New MySqlConnection("server=192.168.2.250;userid=root;password=admin1950;database=inventory")
Dim sql1 As MySqlCommand = New MySqlCommand("Select Location,Category,ItemCode,Description,UOM,BegInv,Receiving,AvailableStocks,StockIssuance,EndingStocks,StoretoWarehouse,WarehouseToSup,StockAdjust,TheoCount,PhysicalCount,Variance,Remarks from variance", con1)
Dim ds1 As DataSet = New DataSet
Dim adapter1 As MySqlDataAdapter = New MySqlDataAdapter
con1.Open()
adapter1.SelectCommand = sql1
adapter1.Fill(ds1, "MyTable")
DataGridView1.DataSource = ds1.Tables(0)
con1.Close()
With DataGridView1
.RowHeadersVisible = False
.Columns(0).HeaderCell.Value = "Location"
.Columns(1).HeaderCell.Value = "Category"
.Columns(2).HeaderCell.Value = "Item Code"
.Columns(3).HeaderCell.Value = "Description"
.Columns(4).HeaderCell.Value = "UOM"
.Columns(5).HeaderCell.Value = "Beg. Inventory"
.Columns(6).HeaderCell.Value = "Receiving"
.Columns(7).HeaderCell.Value = "Available Stocks"
.Columns(8).HeaderCell.Value = "Stock Issuance"
.Columns(9).HeaderCell.Value = "Ending Stocks"
.Columns(10).HeaderCell.Value = "Store to Warehouse"
.Columns(11).HeaderCell.Value = "Warehouse to Supplier"
.Columns(12).HeaderCell.Value = "Stock Adjustment"
.Columns(13).HeaderCell.Value = "Theoretical Qty."
.Columns(14).HeaderCell.Value = "Physical Count"
.Columns(15).HeaderCell.Value = "Variance"
.Columns(16).HeaderCell.Value = "Remarks"
.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
.Columns.Item(0).Width = 125
.Columns.Item(1).Width = 102
.Columns.Item(2).Width = 77
.Columns.Item(3).Width = 236
.Columns.Item(4).Width = 53
.Columns.Item(5).Width = 73
.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
.Columns(5).Frozen = True
End With


Seriously I tried this one but its not working.

TextBox1.Text = ""
Dim FirstValue As Boolean = True
Dim cell As DataGridViewCell
For Each cell In DataGridView1.SelectedCells
If Not FirstValue Then
TextBox1.Text += ", "
End If
TextBox1.Text += cell.Value.ToString()
FirstValue = False
Next

Answer

I myself honestly call this the long approach because I know there are better answers integrating LINQ (not that proficient with LINQ)

First, declare this variable in your search form:

Public dt as New Datatable

Second, declare this in your Form1 (where your DataGridView1 is placed), this will hold the records temporarily before opening your search form:

Dim tempdt as New Datatable

Then include this code before you set your DataGridView1 data source. Right after:

...
DataGridView1.DataSource = ds1.Tables(0)
tempdt  = New Datatable
tempdt  = ds1.Tables(0)
...

Then, call your search form like this (if you have an existing code, replace it with this):

Dim srchForm as New YourSearchFormName
srchForm.dt = tempdt
srchForm.Show()

Then include this code in your Find button in your search form:

    Dim drow() As DataRow
    drow = dt.Select("ItemCode LIKE '%" & txtSearch.Text & "%' OR Description LIKE '%" & txtSearch.Text & "%'")
    If drow.Count > 0 Then
        'THIS WILL LOOP THE GRID BASED ON THE RECORDS FOUND BY TXTSEARCH.TEXT
        For Each row As DataRow In drow
            Dim ItemCodeStr As String = row.Item("ItemCode").ToString
            For Each dgrow As DataGridViewRow In Form1.DataGridView1.Rows
                If ItemCodeStr = dgrow.Cells(2).Value.ToString Then
                    dgrow.DefaultCellStyle.BackColor = Color.Pink
                End If
            Next
        Next
    Else
        MsgBox("There are no matches found.", MsgBoxStyle.Information, "Result")
    End If

In this code, I used ItemCode and Description as the criteria. It will change the backcolor to Pink when it meets the search criteria. I used Pink because it is my favorite color.