[vb.net expr]send text onchange form <---> class --> update dgv

[freakyme]

I have a question and hope it's possible

i have a form with a datagridview with textboxes above (see picture).
What i want to do is from the moment i type a character in a textbox he start searching in the datagridview. With other words... he has to look for it on textChanged.

i taught i make in my form a function

Function selectCustomer() As String
        Dim i, t As Integer
        Dim searchstring As String
        Dim string1, string2, string3 As String
        Dim name, lastname, address, zipcode, city, speciality, phone As String
 
        name = Me.txtSearchCustomerName.Text
        lastname = Me.txtSearchCustomerLastname.Text
        address = Me.txtSearchCustomerAddress.Text
        zipcode = Me.txtSearchCustomerZipcode.Text
        city = Me.txtSearchCustomerCity.Text
        speciality = Me.cmbSearchCustomerSpeciality.Text
        phone = Me.txtSearchCustomerTelephone.Text
 
 
        string1 = "SELECT ... FROM ... "
        string3 = ";"
        string2 = String.Concat("WHERE ", strCustomerName, " LIKE '%", name, "%' AND '", strCustomerLastName, "LIKE '%", lastname, "%'etc...")
 
        searchstring = String.Concat(string1, string2, string3)
        Return searchstring
    End Function

from the moment the text in a textbox change, it has to send (don't know how) the string (searchstring) to the class data_function where it makes a connection with the databank, get the data from the databank and send it back to the form and update the datagridview. And that for all the characters i type in all the textboxes (+1 combobox) i have above the datagridview.

is this possible and how...
(if it is not a problem for you, please explain with some code...)

i hope its clear for you

greetz freakyme

[jmcilhinney]

You do NOT want to be querying the database every time the user changes a character. What if changing a few characters doesn't change the result? You've just queries the database several times for exactly the same data.

You should perform one query to get ALL the data into a single DataTable or collection of business objects. You then bind that table or collection to a BindingSource and bind that to your grid. You now write a single method to handle the TextChanged event of all TextBoxes as well as the SelectedIndexChanged event of the ComboBox. In that event handler you call your 'selectCustomer' method and assign the result to the Filter property of the BindingSource. Voila!

Also, "selectCustomer" is a bad name for that method. The name of a method is supposed to correspond to what it does and that method does NOT select a customer. What is does do is get a string to filter your customer records, so it should be named something like "GetCustomerFilter".

Finally, String.Concat is the wrong method to use in that case. You should be using String.Format. For example, to create a string suitable to assign to a BindingSource.Filter property you'd do something like this:

myBindingSource.Filter = String.Format("FirstName LIKE '{0}%' AND LastName LIKE '{1}%'", Me.firstNameText.Text, Me.lastNameText.Text)

[freakyme]

hmmm... that don't work


form

Dim filldatagrid As New Data_functions
        Dim dt As DataTable
        Dim bs As New BindingSource
        Dim rowcount As Integer
        dt = filldatagrid.fillCustomerSearchField
        bs.DataSource = dt
        bs.Filter = String.Format("Lastname LIKE '{0}%' AND Firstname LIKE '{1}%'", Me.txtSearchCustomerName.Text, Me.txtSearchCustomerLastname.Text)
        Me.dgrSearchCustomer.DataSource = bs

class:

Function fillCustomerSearchField() As DataTable
        Dim connectionstring As New Connection
        Dim dt As DataTable
        Dim connstr As String = connectionstring.getConnectionString
        Dim sqlstr As String
        Dim command As New SqlClient.SqlCommand
        Dim connection As New Data.SqlClient.SqlConnection(connstr)
        sqlstr = String.Concat("SELECT c.pkCustomerId 'PkCust.',c.strCustomerName 'Lastname', c.strCustomerFirstname 'Firstname', replace(a.strAddressFull, '_', ' ') 'Address', ci.strCityPostalCode 'Zipc.', ci.strCityName 'City', cg.strCustomerGenderName 'Speciality', c.strCustomerGsmNumber 'GSM Number' FROM tblAddress a, tblCity ci, tblCustomer c, tblCustomerAddress ca, tblCustomerGender cg WHERE c.pkCustomerId = ca.fkCustomerId AND ca.fkAddressId = a.pkAddressId AND a.fkCityId = ci.pkCityId AND cg.pkCustomerGenderId = c.fkCustomerGenderId ORDER BY 2")
        command.CommandText = sqlstr
        Try
            connection.Open()
            command.CommandType = CommandType.Text
            command.Connection = connection
            Dim da As New SqlClient.SqlDataAdapter(command)
            dt = New DataTable
            da.Fill(dt)
 
        Catch ex As Exception
            MsgBox(Err.Description)
            Throw
        Finally
            connection.Close()
        End Try
        Return dt
    End Function

[freakyme]

... what do i wrong, i read the help about bindginsource en msdn, but still not found a proper sollution...

[sparrow1]

Hi,

I think you can use the auto-complete textbox for that.
Here's a link with an example:

http://www.codeproject.com/cs/miscct...eteControl.asp

Wkr,

sparrow1

[freakyme]

no that's not the problem... he still don't want to filter...
and i don't really want a autocomplete txtbx, i want the datagridview directly filtering his data, by typing in the textbox's above

[freakyme]

is there someone knows where the probleme in the code is why he don't filter the data???

[jmcilhinney]

It will work as I said if you follow the advice I gave. Start a new project, add two Labels, two TextBoxes, a DataGridView and a BindingSource so that the form looks like the screen shot below. Name the TextBoxes "firstNameText" and "lastNameText". Now add this code:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim dt As New DataTable
 
    dt.Columns.Add("ID", GetType(Integer))
    dt.Columns.Add("FirstName", GetType(String))
    dt.Columns.Add("LastName", GetType(String))
 
    'Populate the table with all the data once and once only.
    dt.Rows.Add(1, "John", "Smith")
    dt.Rows.Add(2, "Peter", "Johnson")
    dt.Rows.Add(3, "Emma", "James")
    dt.Rows.Add(4, "Paul", "Gutheridge")
    dt.Rows.Add(5, "Patty", "Jacobson")
    dt.Rows.Add(6, "Andrew", "Peterson")
    dt.Rows.Add(7, "Linda", "Smith")
    dt.Rows.Add(8, "John", "Williams")
    dt.Rows.Add(9, "Jenny", "Andrews")
    dt.Rows.Add(10, "Karen", "Williamson")
 
    Me.BindingSource1.DataSource = dt
    Me.DataGridView1.DataSource = Me.BindingSource1
End Sub
 
Private Sub lastNameText_TextChanged(ByVal sender As System.Object, _
                                     ByVal e As System.EventArgs) Handles lastNameText.TextChanged, _
                                                                          firstNameText.TextChanged
    'Set a new filter each time the text changes in any search field.
    Me.BindingSource1.Filter = String.Format("FirstName LIKE '{0}%' AND LastName LIKE '{1}%'", _
                                             Me.firstNameText.Text, _
                                             Me.lastNameText.Text)
End Sub

Now run the project and type into the TextBoxes and you'll see that the data in the grid gets filtered accordingly.

[freakyme]

wooooooooooops, me stupid
somthimes i have to think more logical...
sorry...

thanks...
greetz freakyme

[freakyme]

bs.Filter = String.Format("Lastname LIKE '%{0}%' AND Firstname LIKE '%{1}%' AND Address LIKE '%{2}%' AND Zip LIKE '%{3}%' AND City LIKE '%{4}%' AND Speciality LIKE '{5}%' AND GSM_Number LIKE '%{6}%'", Me.txtSearchCustomerName.Text, Me.txtSearchCustomerLastname.Text, Me.txtSearchCustomerAddress.Text, Me.txtSearchCustomerZipcode.Text, Me.txtSearchCustomerCity.Text, Me.cmbSearchCustomerSpeciality.Text, Me.txtSearchCustomerTelephone.Text)

in this code, GSM_Number is somtimes NULL...
when i make a selection on all the other textboxes (or combobox) he don't show the result if there is a null in the gsm_number column...
how do i have to resolve this in vb.net?

greetz freakyme

[freakyme]

isn't that possible???
hmmm... i thought i use nvl() but that just don't work :s