locked
how to search the data by letters and display data in datagridview RRS feed

  • Question

  • i have a datagridview and a textbox .. when the user search a data in textbox for example she types "a" all the data that starts in letter a should display in datagridview then when she types again "an" datas that starts with"an" should display only in datagridview.. i have a connection for my vb.net and excel ... thanks

    'connect excel to departmentgrid

    cn =

    New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Documents and Settings\itdsba\My Documents\Visual Studio 2010\ucpb5s\data5s.xls';Extended Properties=Excel 8.0;")

    mc =

    New System.Data.OleDb.OleDbDataAdapter("select * from [Department$]", cn)

    mc.TableMappings.Add(

    "Table", "data5s.xls")

    Dataset =

    New System.Data.DataSet

    mc.Fill(Dataset)

    departmentgrid.DataSource = Dataset.Tables(0)

    cn.Close()

    Monday, October 15, 2012 6:36 AM

Answers

  • As Pratush Krishna asked - where is the procedure which calls Your Form to show?

    In that procedure you should use my code:

    txbSearch.Text = ""

    • Marked as answer by chaapple Wednesday, October 17, 2012 9:22 AM
    Wednesday, October 17, 2012 9:18 AM

All replies

  •  

    I achieved what you want, but I used MSSQL Database. The logic should be the same. In the Select command I use WHERE clause with LIKE and Wildcards. You can read about Wildcard here: http://msdn.microsoft.com/en-us/library/aa933232(v=sql.80).aspx

    So by using this combination, I was able to search and find every data, which contains the searched word in it. For example, if I am searching the word "an", I can get "plane", "man" "woman" etc. as a result. My SELECT command looks like this:

    "SELECT * FROM Table WHERE Field like '%" + TextBoxForSearch.Text +"%'"

    and I enter the word in the TextBoxForSearch TextBox. Then in the Text_Change event of the TextBox, everytime when the text is changed, new character is entered or one is deleted, I am executing this SELECT command and Fill the DataAdapter. 

    If you want only to find results, which start with the desired word, you can remove first '%' character from the SELECT Command:

    "SELECT * FROM Table WHERE Field like" + TextBoxForSearch.Text +"%'"

    I don't know for sure if this will work with your database, but its worth to try. Please inform me about the result and if you have some more question, ask me straight.

    Monday, October 15, 2012 7:49 AM
  • sir the code that you given i insert it here ... can you pleae tell me if it is right ??

    mc =

    New System.Data.OleDb.OleDbDataAdapter("select * from [Department$] where DepartmentName like " + txtchdept.Text + "%'", cn)

    Monday, October 15, 2012 8:56 AM
  • i tried your code but an error occur "Syntax error in query expression 'DepartmentName like%''."
    Monday, October 15, 2012 9:04 AM
  • Sorry, my mistake, you should add ' after "like" and before +txtchdept.Text. Check here:

    mc =
    
    New System.Data.OleDb.OleDbDataAdapter("select * from [Department$] where DepartmentName like '" + txtchdept.Text + "%'", cn)
    Try this one!

    Monday, October 15, 2012 9:44 AM
  • Use Binding Source For filter data

    cn =
    
    New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Documents and Settings\itdsba\My Documents\Visual Studio 2010\ucpb5s\data5s.xls';Extended Properties=Excel 8.0;")
    
    mc =
    
    New System.Data.OleDb.OleDbDataAdapter("select * from [Department$]", cn)
    
    mc.TableMappings.Add(
    
    "Table", "data5s.xls")
    
    Dataset =
    
    New System.Data.DataSet
    
    mc.Fill(Dataset)
    
    Dim BS as new BindingSource
    BS.DataSource=Dataset.Tables(0)
    
    departmentgrid.DataSource = BS
    
    cn.Close()

    And In Textbox ChangedText event Use this code

    BS.Filter = " Field like '" + TextBoxForSearch.Text +"%'"


    Art Of Living Is Art Of Giving

    • Marked as answer by chaapple Tuesday, October 16, 2012 12:48 AM
    • Unmarked as answer by chaapple Tuesday, October 16, 2012 2:25 AM
    Monday, October 15, 2012 10:10 AM
  • thanks you both for helping me ... thanks guys but i used more the code of pratush ... thanks again
    Tuesday, October 16, 2012 12:48 AM
  • one more thing ... when i search a data in datagridview then it displays the data when i back to the searching of the department the data that i searched still there and it didnt go back where all the data are there. adn when i press the enter the data in datagridview will dis appear and when i press the backspace it will go back where stop searching a data ...

    thanks again


    • Edited by chaapple Tuesday, October 16, 2012 1:33 AM
    Tuesday, October 16, 2012 1:07 AM
  • I couldn't understand it quiet well. You want all the data, displayed in the gridview to disappear when you press Enter and you want to show the previous search results, when you press Backspace. Is that so, or I got it wrong? 

    In your case you may need to use KeyPressed event and check with button was pressed. If you explain it a little bit more, I may be able to help you.

    Tuesday, October 16, 2012 5:52 AM
  • no that was the problem when i type in the textbox to search a data and the datagridview display the data that i search but when i press the enter the data in datagridview are all disappear then when i press backspace the previoes data that i searched will show ...

    Tuesday, October 16, 2012 6:01 AM
  • The problem is that when you press Enter you add new line to your textbox. Now in your SELECT Command you will have a character for a new line and because in your database fields you don't have new line, your filter will turn no data. When you press Backspace, that new line is deleted and you have the text, that you enter before that.

    In this way of search, you don't need to press enter in order to search. It's enough just to type the text. If you don't want this to happen, than you need to add one key_press event handler for your textbox, which look for the pressed key, and if that key is Enter will not put new line and suppress that event. Check this code:

     Private Sub txbSearch_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txbMessage.KeyDown
            If (e.KeyCode = Keys.Enter) Then
                e.SuppressKeyPress = True
            End If
       
        End Sub

    Hope, it will help you!

      
    Tuesday, October 16, 2012 7:20 AM
  • thanks sir but another thing .. when i search a data in textbox for example i type "apple" in the textbox then the datagridview displays the data then i click the datagridview to go to another form and when i go back to search another data the "apple" that i searched is still in the textbox and the datagridview only displays the apple... how can i solce this is that the clear or refresh ??

    thanks again

    Tuesday, October 16, 2012 7:26 AM
  • me.refresh ()

    in formload


    adulyajed

    Tuesday, October 16, 2012 7:32 AM
  • it is still the same sir nothing happen
    Tuesday, October 16, 2012 7:39 AM
  • try 

    this sir

    in formload

    dgvStudent.DataSource = studentLoad(txtKeyword.Text)

    and inthe 

     Private Sub txtKeyword_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtKeyword.TextChanged
            dgvStudent.DataSource = studentLoad(txtKeyword.Text)
        End Sub

    its exsample


    adulyajed

    Tuesday, October 16, 2012 7:42 AM
  • sir what is the studentload???
    Tuesday, October 16, 2012 8:23 AM
  • in my program

    studentLoad = 

     Private Function studentLoad(ByVal keyword As String) As DataTable
            strSQL = "sp_studentSele 1, '" & keyword & "'"
            Return runSQL(strSQL, 2)
        End Function


    adulyajed

    Tuesday, October 16, 2012 8:26 AM
  • is the studenetload a form??

    Tuesday, October 16, 2012 8:27 AM
  • studenetload a form?? not form bt you must insert in form load

      Private Sub ucDataMurid_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

     dgvStudent.DataSource = studentLoad(txtKeyword.Text)

    end sub


    adulyajed

    Tuesday, October 16, 2012 8:30 AM
  • what if i dont have a function like that ?? how should i replace that?

    thanks

    Tuesday, October 16, 2012 8:31 AM
  • I would try this with the Defaultview rowfilter and then an expression using like

    http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

    DataSet.Tables(0).Defaultview.RowFilter = "YourExpression"

    DataGridView1.DataSource = DataSet.Tables(0).Defaultview



    Success
    Cor


    Tuesday, October 16, 2012 8:53 AM
  • In the Form, where your DataGrid is, do you have Form_Load event? If you have it, it should contain this code:

    YourTableAdapter.Fill(myDBDataSet.Table)

    If that is there, you need to add this lines of code before that line:

    YourTableAdapter.Adapter.SelectCommand.CommandText = "SELECT * FROM Table"
    YourTableAdapter.Adapter.SelectCommand.ExecuteNonQuery()

    They will refill the datagridview with all data, contained in the table.

    My oppinion is that, the SELECT command in your TableAdapter keeps its last value, which will be with condition for searching some word. Writing this three lines will make the TableAdapter to take the whole data, and fill your GridView with it.

    Please, try it and tell me, if it is working! 

    Tuesday, October 16, 2012 9:33 AM
  • @Ahmed,

    You are replying to me in this thread, I see no relation to my reply. Be aware many at least not most contributors are using thread view (see my settings) then you can reply direct to a thread instead to somebody else.

    Also I don't like your solution because it is accessing the Excel spreadsheet over and over again, without any sense because it still has to access the whole spreadsheet.


    Success
    Cor

    Tuesday, October 16, 2012 11:31 AM
  • Im very sorry Cor, I'm new in this forum and didn't know about these settings. I wanted to reply to the post, but I guess I hit the wrong button. Where can I change these setting, from My Settings? 

    And now after I saw your solution, also started not to like mine :) I will use it too.

    Tuesday, October 16, 2012 2:00 PM
  • Im very sorry Cor, I'm new in this forum and didn't know about these settings. I wanted to reply to the post, but I guess I hit the wrong button. Where can I change these setting, from My Settings? 

    And now after I saw your solution, also started not to like mine :) I will use it too.

    I thought I wrote it, look right. Click My Settings and check threaded list view 

    Success
    Cor

    Tuesday, October 16, 2012 4:45 PM
  • thanks sir but another thing .. when i search a data in textbox for example i type "apple" in the textbox then the datagridview displays the data then i click the datagridview to go to another form and when i go back to search another data the "apple" that i searched is still in the textbox and the datagridview only displays the apple... how can i solce this is that the clear or refresh ??

    thanks again

    Wednesday, October 17, 2012 1:00 AM
  • You just need to clear the textbox by setting its Text Property to empty string:

    txbSearch.Text = ""

    Then you will open the other form. So just add the above line in the sub, where you are opening another form. Should look like this:

    txbSearch.Text = ""
    AnotherForm.Show()//Your code


    Wednesday, October 17, 2012 6:04 AM
  • i placed that code here but when i run and type in textbox it clears the textbox or i was not able to write anything from textbox... is this right where i placed the code??

    Private Sub txtchdept_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtchdept.TextChanged

    bs.Filter =

    " DepartmentName like '" + txtchdept.Text + "%'"

    txtchdept.Text =

    ""

    Me.SearchEmployee.Show()

    End Sub

    Wednesday, October 17, 2012 6:17 AM
  • No, it should not be there. You should write it in the Sub Routine, which handles your DataGridView_Click Event. I suppose, that you have something like this:

     Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
    
    Me.SearchEmployee.Show()
    
        End Sub

    which handles your DataGridView_Click. Could be different, its just the place where you tell your program to open SearchEmployee. There you will add the code. 

    Wednesday, October 17, 2012 7:28 AM
  • still its not working
    Wednesday, October 17, 2012 7:36 AM
  • Did you remove it from there and where did you put it. Can you share your code?
    Wednesday, October 17, 2012 8:12 AM
  • this is the code for searching but the problem is when i type in textbox to search data and datagridview displays the data then when i double clicked my datagridview to proceed in another form and when i go back to the searching form the data that i previously searched is still there.

       'connect excel to view departments
            cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Documents and Settings\itdsba\My Documents\Visual Studio 2010\ucpb5s\data5s.xls';Extended Properties=Excel 8.0;")
            mc = New System.Data.OleDb.OleDbDataAdapter("select * from [Department$] Order By [DepartmentCode]", cn)
            mc.TableMappings.Add("Table", "data5s.xls")
            Dataset = New System.Data.DataSet
            mc.Fill(Dataset)
            'Dim BS As New BindingSource
            bind.DataSource = Dataset.Tables(0)
            dgvdepartment.DataSource = bind
            dgvdepartment.DataSource = Dataset.Tables(0)
            cn.Close()

    Private Sub txtchdept_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtchdept.KeyDown
            If (e.KeyCode = Keys.Enter) Then
                e.SuppressKeyPress = True
            End If
        End Sub

    Private Sub txtchdept_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtchdept.TextChanged
            bs.Filter = " DepartmentName like '" + txtchdept.Text + "%'"

        End Sub

    Wednesday, October 17, 2012 8:38 AM
  • 'connect excel to view departments
            cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Documents and Settings\itdsba\My Documents\Visual Studio 2010\ucpb5s\data5s.xls';Extended Properties=Excel 8.0;")
            mc = New System.Data.OleDb.OleDbDataAdapter("select * from [Department$] Order By [DepartmentCode]", cn)
            mc.TableMappings.Add("Table", "data5s.xls")
            Dataset = New System.Data.DataSet
            mc.Fill(Dataset)
            'Dim BS As New BindingSource
            bind.DataSource = Dataset.Tables(0)
            dgvdepartment.DataSource = bind
            dgvdepartment.DataSource = Dataset.Tables(0)
            cn.Close()

    In which Procedure above code you written .

    and where is Datagridview_DoubleClick Event where you Call Your Form to show.


    Art Of Living Is Art Of Giving

    Wednesday, October 17, 2012 8:51 AM
  • this is my code to proceed in another form

    Private Sub datagridEmp_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles datagridEmp.CellDoubleClick

    frmscoresheets.stextname.Text = datagridEmp.SelectedCells.Item(0).Value.ToString()

    frmscoresheets.stextdesig.Text = datagridEmp.SelectedCells.Item(4).Value.ToString()

    frmscoresheets.stextdept.Text = datagridEmp.SelectedCells.Item(5).Value.ToString()

    frmscoresheets.stextdiv.Text = datagridEmp.SelectedCells.Item(6).Value.ToString()

    frmscoresheets.stextgroup.Text = datagridEmp.SelectedCells.Item(7).Value.ToString()

    frmscoresheets.txtexaminer.Text =

    Me.Label9.Text

    frmscoresheets.Show()

     

     

    End Sub

    Wednesday, October 17, 2012 9:16 AM
  • As Pratush Krishna asked - where is the procedure which calls Your Form to show?

    In that procedure you should use my code:

    txbSearch.Text = ""

    • Marked as answer by chaapple Wednesday, October 17, 2012 9:22 AM
    Wednesday, October 17, 2012 9:18 AM
  • thanks sir.....

    Wednesday, October 17, 2012 9:22 AM
  • You are welcome!
    Wednesday, October 17, 2012 9:28 AM