-
Oct 30th, 2012, 01:23 AM
#1
Thread Starter
Member
search filter for datagridview
Hello,
I have a datagridview and a search textbox that filters the dgv on button click with whatever the user inputs in that textbox.
The problem is, when the user tries to update a row AFTER a search, the datarow grabbed is NOT the current one but the one that was selected BEFORE the search.
I think this is because I am creating 2 datatables (one for form_load and another for the search filter), and the user hits the update button, the row grabbed is from the form_load datatable.
Please take a look at my (simplified) code below:
Code:
Public Class VBApp
Private Sub fetchData(ByVal tableName As String)
Dim dt As New DataTable
adapter = New MySqlDataAdapter
command.Connection = conn
command.CommandText = "select * from TableName"
adapter.SelectCommand = command
reader = command.ExecuteReader
dt.Load(reader)
DataGridView1.DataSource = dt
reader.Close()
conn.Close()
End Sub
Private Sub updateData()
Dim row As DataRow = DirectCast(BindingSource.Current, DataRowView).Row
Using dialogue As New updatePopup(row)
Dim dr As DialogResult = dialogue.ShowDialog()
End Using
'etc. etc. (skipping code)
Private Sub searchData()
Dim search As String = KeywordTextbox.Text
Dim tableName As String = TableList.Text
Dim dt As New DataTable
If (conn.State = ConnectionState.Closed) Then
setConnection()
End If
command.Connection = conn
command.CommandText = ""
command.CommandText = "select * from persons where `columnA` like '%" & search "%'"
reader = command.ExecuteReader
dtable.Load(reader)
DataGridView1.DataSource = dt
reader.Close()
conn.Close()
End Sub
End Class
Thanks for your help.
-
Oct 30th, 2012, 01:36 AM
#2
Re: search filter for datagridview
This is actually very easy. You just query the database once. Get all the data into a DataTable, bind that to a BindingSource and bind that to your control(s). When you want to filter the data you simply set the Filter property of the BindingSource. The data in the DataTable is unaffected but the BindingSource only exposes rows that match the Filter, so that's all the user sees. Change the filter as much as you like and the view will refresh almost instantly as there is no database query.
The value you assign to the Filter property is basically a SQL WHERE clause, although only a subset of SQL syntax is supported. If you need help then the documentation for the DataColumn.Expression property explains all the supported syntax.
Also, if you want to access the data in code, you should do that via the BindingSource too.
-
Oct 31st, 2012, 02:19 AM
#3
Thread Starter
Member
Re: search filter for datagridview
So do I not need a separate function for filtering? Are you saying that I can use BindingSource.Filter to filter my dataGridView in my fetchData sub?
Also, how can bind the bindingSource to my control(s)?
Last edited by echoUser; Oct 31st, 2012 at 02:24 AM.
-
Oct 31st, 2012, 02:32 AM
#4
Re: search filter for datagridview
Originally Posted by echoUser
So do I not need a separate function for filtering?
No.
Originally Posted by echoUser
Are you saying that I can use BindingSource.Filter to filter my dataGridView in my fetchData sub?
Yes.
Originally Posted by echoUser
Also, how can bind the bindingSource to my control(s)?
You bind the DataTable to the BindingSource the same way you bound it to the DataGridView. You bind the DataGridView to the BindingSource the same way you bound it to the DataTable.
-
Oct 31st, 2012, 02:49 AM
#5
Thread Starter
Member
Re: search filter for datagridview
I am getting errors with my bindingsource.filter syntax and I cannot figure out why. Could you please take a look?
Code:
Private Sub fetchData(ByVal tableName As String)
Try
command = New MySqlCommand
adapter = New MySqlDataAdapter
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
dt = New DataTable
If (conn.State = ConnectionState.Closed) Then
setConnection()
End If
command.Connection = conn
command.CommandText = "select * from `" & tableName & "`"
Catch ex As MySqlException
MessageBox.Show("Error1: " & ex.Message)
End Try
adapter.SelectCommand = command
reader = command.ExecuteReader
dt.Load(reader)
DataGridView1.DataSource = dt
BindingSource.DataSource = dt
DataGridView1.DataSource = BindingSource
Dim search As String = KeywordTextbox.Text
BindingSource.Filter = "[Book ID] = " & search & " or ISBN like '%" & search & "%' or Title like '%" & search & "%'"
reader.Close()
conn.Close()
End Sub
-
Oct 31st, 2012, 03:13 AM
#6
Thread Starter
Member
Re: search filter for datagridview
Oh nvm, I got it!!!!!! Thank you so much for your help!
-
Oct 31st, 2012, 03:40 AM
#7
Re: search filter for datagridview
You should get rid of this line:
Code:
DataGridView1.DataSource = dt
BindingSource.DataSource = dt
DataGridView1.DataSource = BindingSource
The next two lines do the binding now.
Also, I strongly suggest using String.Format. Long strings with multiple & operators get confusing very quickly. They are hard to read and therefore error-prone. I find something like this much clearer:
Code:
BindingSource.Filter = String.Format("[Book ID] = {0} or ISBN like '%{0}%' or Title like '%{0}%'", search)
You've got a problem with that code too. I'm not sure what the error was that you fixed because you didn't say. If you solve an issue yourself you should always provide your solution. It might help others and we can tell you how to improve it if it's not optimal. This may have been it but that filter will be fine when you enter a number but as soon as you enter a non-numeric value it will cause a syntax error. You should check whether the input is a number first and only include the [Book ID] column in the filter if it is.
-
Oct 31st, 2012, 08:14 PM
#8
Thread Starter
Member
Re: search filter for datagridview
It was a syntax error, like you said. I got rid of the line DataGridView1.DataSource = dt, thank you for the advice!
After I changed my code to String.Format, I started getting errors, saying the application cannot find the "column" of my search...
What I tried though, was removing the int column (Book ID) from the bildingSource.Filter query, and it worked fine.
Could you suggest how I can fix this? Thank you.
-
Oct 31st, 2012, 08:21 PM
#9
Re: search filter for datagridview
Originally Posted by echoUser
Could you suggest how I can fix this?
I thought that I already had. Maybe if I knew what the actual value was when the syntax error occurred I could be more specific. You're not the only one but far too many people expect us to just know stuff about their project without actually giving us the information.
-
Oct 31st, 2012, 08:28 PM
#10
Thread Starter
Member
Re: search filter for datagridview
Here is my code:
Code:
Private Sub searchData()
Dim search As String = KeywordTextbox.Text
Dim tableName As String = TableList.Text
BindingSource.Filter = String.Format("ISBN like '%{0}%' or Title like '%{0}%' or Author like '%{0}%' or Publisher like '%{0}%'", search)
End Sub
This works. But as soon as I insert the [Book ID] = {0} or into the query, I get this error: "Evaluate Exception was unhandled. Cannot find column [whatever I typed into the search box]."
-
Oct 31st, 2012, 08:56 PM
#11
Re: search filter for datagridview
Please read what I posted. I did not ask for the code. I asked for the VALUE.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|