J.Don J.Don - 6 months ago 21
SQL Question

vb.net datagridview filtering colum not running

Recently I developed a pos but the data search is giving errors. Here is some code:

Dim DV As New DataView(dbDataSet)
DV.RowFilter = String.Format("[barkod] Like '%" & TextBoxPrebBarkod.Text & "%'")
DataGridView1.DataSource = DV


at the top i declared:

Dim dbDataSet As New DataTable
konekcija = New MySqlConnection
konekcija.ConnectionString = "server=localhost;userid=root;password=root;database=baza"


Here is the rest of my code:

Dim SDA As New MySqlDataAdapter

Dim bSource As New BindingSource

Try
konekcija.Open()
Dim Query As String
Query = "select barkod as 'Баркод', naziv as 'Назив', kupovna as
'Куповна', prodazna as 'Продажна', opis as 'Опис', profit as 'Профит',
proizvoditel as 'Производител', ddv as 'ДДВ', kolicina as 'Количина'
, makpr as 'Македонски производ' from baza.artikli"
comm = New MySqlCommand(Query, konekcija)
SDA.SelectCommand = comm
SDA.Fill(dbDataSet)
bSource.DataSource = dbDataSet
DataGridView1.DataSource = bSource
SDA.Update(dbDataSet)
konekcija.Close()
Catch ex As Exception

End Try

Answer

There are several ways to filter a query result, but is giving errors is not very helpful as to what the problem is.

First, when you Alias a column (barkod as 'Баркод') the Alias names are added to the DataTable as that name; there would be no point to an Alias (As Foo) it if it didn't use them. If AutoGenerateColumns is true for the DataGridView, those columns names will also be used for the HeaderText.

I am not sure why the OP code is using a BindingSource, the DataTable is really all you need, so I omitted it.

Basic Query and Actors

Private dtSample As DataTable       ' form level object
...

Dim sql = "SELECT Name AS A, Fish AS Баркод, Bird As C, Color From Sample"

dtSample = New DataTable
Using dbcon As New MySqlConnection(MySQLConnStr)
    Using cmd As New MySqlCommand(sql, dbcon)

        dbcon.Open()
        dtSample.Load(cmd.ExecuteReader())

        ' prove that the DT column name is that of the SQL alias:
        Console.WriteLine(dtSample.Columns(1).ColumnName)   ' == "Баркод"
    End Using
End Using
  1. This uses a form level DataTable. Depending on what operations will be implemented, this prevents having to cast the DGV's DataSource in order to work with it elsewhere.
  2. Note the use of Using to close and dispose of the DBConnection and DBCommand objects. Under the hood, these allocate resources which need to be released.

Explicit DataView

Depending on what the code does, a persistent DataView can be useful. This will be used for the filtering:

Private dvSample As DataView         ' declared with dtSample
...
{query code from above}
dvSample = New DataView(dtSample)

dvSample.RowFilter = String.Format("Баркод LIKE '%{0}%'", "erm")
dgv2.DataSource = dvSample

I am not fond of using LIKE, but there you have it.

Built-in DataView

That is slightly overkill because the DataTable has a built in DataView which this one will use:

dtSample.DefaultView.RowFilter = String.Format("Баркод LIKE '%{0}%'", "erm")
dgv2.DataSource = dtSample

With either method, the results are the same: enter image description here

You'd get the same results using the BindingSource.Filter.

enter image description here The Alias columns from the SQL are used (A, C, Баркод)
enter image description here Only rows containing "erm" for Баркод are shown

Escape Illegal Characters

Again, it is not clear what is to be fixed from it gives errors but possibly whatever is in TextBoxPrebBarkod.Text contains illegal characters. Names like O'Brian, Carol's Cookies or D'Angelo will cause problems because of the tick/apostrophe. These can be escaped by replacing one tick with 2:

Dim filterText = "D'Artagnan"    ' e.g. user input like TextBoxPrebBarkod.Text
filterText = filterText.Replace("'", "''")     ' swap 2 ticks for 1
dtSample.DefaultView.RowFilter = String.Format("Баркод LIKE '%{0}%'", filterText)

Of course, there is always the chance that whatever is in that TextBox doesnt exist in the Баркод column.