Autocomplete of text in datagridview..

  • (2 Pages)
  • +
  • 1
  • 2

17 Replies - 7273 Views - Last Post: 15 May 2013 - 11:54 AM Rate Topic: -----

#1 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Autocomplete of text in datagridview..

Posted 12 April 2013 - 12:24 AM

 Try
            q1 = "select * from stock_left WHERE item_name like '" & TextBox1.Text & "%'"
                        cn = New SqlConnection(My.Settings.PharmacyConnectionString)
            cn.Open()
            ds1 = New DataSet
            ad = New SqlDataAdapter(q1, cn)
            ad.Fill(ds1, "DGV")
            DataGridView1.DataSource = ds1.Tables(0)
            cn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try


i am typing in textbox and datagridview is getting data whatever im typing in textbox..

but i want to autocomplete of text whenever i type on datagridview .what is the way to do that

This post has been edited by vks.gautam1: 12 April 2013 - 12:25 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Autocomplete of text in datagridview..

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Autocomplete of text in datagridview..

Posted 12 April 2013 - 05:30 AM

You could use the editingControlShowing event of the datagridview to instantiate a textbox and set its AutoComplete features to your list.

Something like:
If myDGV.CurrentCell.ColumnIndex = 1 '(2nd column in the row) only do it if you're in the cell of your choosing.

   Create a new AutoCompleteStringCollection and fill it 
   
   Declare a textbox off the e.Control eventarg (suggest using TryCast here)
   check to see if the new textbox isnot nothing and if it is not nothing then
     set your autocompletemode for new textbox
     set your autocompletecustomesource for new textbox 'This would be your list
     set your Autocompletesource = Customsource
   end if
End if

This post has been edited by CharlieMay: 12 April 2013 - 05:31 AM

Was This Post Helpful? 0
  • +
  • -

#3 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Autocomplete of text in datagridview..

Posted 12 April 2013 - 06:40 AM

I tried but still not working
Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs)
        If DataGridView1.CurrentCell.ColumnIndex = 1 AndAlso TypeOf e.Control Is TextBox Then
            With DirectCast(e.Control, TextBox)
                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                .AutoCompleteSource = AutoCompleteSource.CustomSource
                .AutoCompleteCustomSource = scAutoComplete
            End With
        End If
    End Sub


i written this in sub

 q = "select item_name from item_name "
        cn = New SqlConnection(My.Settings.PharmacyConnectionString)
        cn.Open()
        Dim cmd As New SqlCommand(q, cn)
        dr = cmd.ExecuteReader

        For i As Integer = 1 To DataGridView1.RowCount
            scAutoComplete.Add(dr.Item(0))
        Next
        dr.Close()
        cn.Close()


Is this write way of filling AutoCompleteStringCollection

This post has been edited by vks.gautam1: 12 April 2013 - 06:59 AM

Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Autocomplete of text in datagridview..

Posted 12 April 2013 - 07:08 AM

Here's an example of what I did:
If DataGridView1.CurrentCell.ColumnIndex = 1 Then
   Dim ItemCode As TextBox = TryCast(e.Control, TextBox)
   If ItemCode IsNot Nothing Then
      ItemCode.AutoCompleteMode = AutoCompleteMode.SuggestAppend
      ItemCode.AutoCompleteCustomSource = sourceList
      ItemCode.AutoCompleteSource = AutoCompleteSource.CustomSource
   End If
End If


I do know that in your column check, e.Control is not a Textbox, I think it's a DataGridViewTextBoxEditingControl but even changing that, I still stepped over. I may give it a closer look when time permits.
Was This Post Helpful? 0
  • +
  • -

#5 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Autocomplete of text in datagridview..

Posted 12 April 2013 - 07:16 AM

View PostCharlieMay, on 12 April 2013 - 07:08 AM, said:

Here's an example of what I did:
If DataGridView1.CurrentCell.ColumnIndex = 1 Then
   Dim ItemCode As TextBox = TryCast(e.Control, TextBox)
   If ItemCode IsNot Nothing Then
      ItemCode.AutoCompleteMode = AutoCompleteMode.SuggestAppend
      ItemCode.AutoCompleteCustomSource = sourceList
      ItemCode.AutoCompleteSource = AutoCompleteSource.CustomSource
   End If
End If


I do know that in your column check, e.Control is not a Textbox, I think it's a DataGridViewTextBoxEditingControl but even changing that, I still stepped over. I may give it a closer look when time permits.

yes it is DataGridViewTextBoxEditingControl ..im quite confused nevertheless thnx..im trying on all suggestion u have given
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Autocomplete of text in datagridview..

Posted 12 April 2013 - 07:24 AM

OK, my code has a major issue. Once on, it will display for every textbox column and will probably crash if not a textbox column.

Try this instead:
    Private Sub DataGridView1_EditingControlShowing(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
        Dim ItemCode As TextBox = TryCast(e.Control, TextBox)
        If ItemCode IsNot Nothing Then
            If DataGridView1.CurrentCell.ColumnIndex = 1 Then
                ItemCode.AutoCompleteMode = AutoCompleteMode.SuggestAppend
                ItemCode.AutoCompleteCustomSource = xsource
                ItemCode.AutoCompleteSource = AutoCompleteSource.CustomSource
            Else
                'Not columnn 1, turn of Autocomplete
                ItemCode.AutoCompleteMode = AutoCompleteMode.None
            End If
        End If
    End Sub

This post has been edited by CharlieMay: 12 April 2013 - 07:25 AM

Was This Post Helpful? 1
  • +
  • -

#7 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Autocomplete of text in datagridview..

Posted 12 April 2013 - 07:31 AM

View PostCharlieMay, on 12 April 2013 - 07:24 AM, said:

OK, my code has a major issue. Once on, it will display for every textbox column and will probably crash if not a textbox column.

Try this instead:
    Private Sub DataGridView1_EditingControlShowing(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
        Dim ItemCode As TextBox = TryCast(e.Control, TextBox)
        If ItemCode IsNot Nothing Then
            If DataGridView1.CurrentCell.ColumnIndex = 1 Then
                ItemCode.AutoCompleteMode = AutoCompleteMode.SuggestAppend
                ItemCode.AutoCompleteCustomSource = xsource
                ItemCode.AutoCompleteSource = AutoCompleteSource.CustomSource
            Else
                'Not columnn 1, turn of Autocomplete
                ItemCode.AutoCompleteMode = AutoCompleteMode.None
            End If
        End If
    End Sub

Thnx but there is nothing happening.i am typing but no suggestion.i think im doing something very wrong.. there is something a basic thing wrong in my code.i have changed it ..
Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            q = "select maker_name from item_maker_master"
            cn = New SqlConnection(My.Settings.PharmacyConnectionString)
            Dim cmd As New SqlCommand(q, cn)

            cn.Open()
            ad = New SqlDataAdapter(q, cn)
            Dim ds As New DataSet
            ad.Fill(ds, "item_maker_master")
            DataGridView1.DataSource = ds.Tables(0)
            cn.Close()
            Call setAutoComplete() ' calling AutoCompleteStringCollection 

        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Private Sub setAutoComplete()
        Try
            q = "select maker_name from item_maker_master"
            cn = New SqlConnection(My.Settings.PharmacyConnectionString)
            Dim cmd As New SqlCommand(q, cn)
            cn.Open()
            cmd = New SqlCommand(q, cn)
            dr = cmd.ExecuteReader
            While dr.Read
                scAutoComplete.Add(dr.Item(0))
            End While
            dr.Close()
            cn.Close()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing

        Dim ItemCode As TextBox = TryCast(e.Control, TextBox)

        If ItemCode IsNot Nothing Then

            If DataGridView1.CurrentCell.ColumnIndex = 1 Then

                ItemCode.AutoCompleteMode = AutoCompleteMode.SuggestAppend

                ItemCode.AutoCompleteCustomSource = scAutoComplete

                ItemCode.AutoCompleteSource = AutoCompleteSource.CustomSource

            Else
               
                ItemCode.AutoCompleteMode = AutoCompleteMode.None

            End If

        End If

    End Sub

End Class


Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Autocomplete of text in datagridview..

Posted 12 April 2013 - 07:42 AM

put a breakpoint on your autocomplete sub and step through it, make sure it found rows and see what Item(0) is during the add.

You could also put a MsgBox(scAutoComplete.Count) after the while loop finishes to see if you have any items in the collection
Was This Post Helpful? 0
  • +
  • -

#9 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Autocomplete of text in datagridview..

Posted 12 April 2013 - 07:54 AM

View PostCharlieMay, on 12 April 2013 - 07:42 AM, said:

put a breakpoint on your autocomplete sub and step through it, make sure it found rows and see what Item(0) is during the add.

You could also put a MsgBox(scAutoComplete.Count) after the while loop finishes to see if you have any items in the collection

it is showing the data in msgbox.but when im typing in gridview it is not showing any data..i am typing on datagridview to get the data

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Autocomplete of text in datagridview..

Posted 12 April 2013 - 09:34 AM

Ok, we can see that the query is pulling data and should add that data to the scAutoComplete. I would start looking at the occurrences of scAutoComplete throughout your code to see if you might be re-declaring it or clearing it before its used in the dgv.

You are trying this in the 2nd column of the datagridview right? (column index 1 is column 2 of your datagridview) Just want to make sure you're testing the proper cell.

This post has been edited by CharlieMay: 12 April 2013 - 09:36 AM

Was This Post Helpful? 1
  • +
  • -

#11 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Autocomplete of text in datagridview..

Posted 13 April 2013 - 12:32 AM

View PostCharlieMay, on 12 April 2013 - 09:34 AM, said:

Ok, we can see that the query is pulling data and should add that data to the scAutoComplete. I would start looking at the occurrences of scAutoComplete throughout your code to see if you might be re-declaring it or clearing it before its used in the dgv.

You are trying this in the 2nd column of the datagridview right? (column index 1 is column 2 of your datagridview) Just want to make sure you're testing the proper cell.

Thnx it's working now...i have few questions now..
1) i need to work on multiple columns on same table..i want i type on first column and all related data will get selected.
Maker_Name and Maker_ID
This is what i tried so far as per your code.
Private Sub setAutoComplete()
        Try
            q = "select maker_name,maker_id from item_maker_master"
            cn = New SqlConnection(My.Settings.PharmacyConnectionString)
            Dim cmd As New SqlCommand(q, cn)
            cn.Open()
            cmd = New SqlCommand(q, cn)
            dr = cmd.ExecuteReader
            While dr.Read
                scAutoComplete.Add(dr.Item(0))
                scAutoComplete1.Add(dr.Item(1))
            End While
            dr.Close()
            cn.Close()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

  Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing

        Dim MakerName As TextBox = TryCast(e.Control, TextBox)
        Dim MakerId As TextBox = TryCast(e.Control, TextBox)
        If MakerName IsNot Nothing Then
            If DataGridView1.CurrentCell.ColumnIndex = 0 Then
                MakerName.AutoCompleteMode = AutoCompleteMode.SuggestAppend
                MakerName.AutoCompleteCustomSource = scAutoComplete
                MakerName.AutoCompleteSource = AutoCompleteSource.CustomSource

                MakerId.AutoCompleteMode = AutoCompleteMode.SuggestAppend
                MakerId.AutoCompleteCustomSource = scAutoComplete1
                MakerId.AutoCompleteSource = AutoCompleteSource.CustomSource
            Else
                MakerName.AutoCompleteMode = AutoCompleteMode.None
            End If
        End If
    End Sub


This post has been edited by vks.gautam1: 13 April 2013 - 12:45 AM

Was This Post Helpful? 0
  • +
  • -

#12 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Autocomplete of text in datagridview..

Posted 13 April 2013 - 05:26 AM

You should be able to use the same code, only I would assign the currentcell.columnindex to an integer

If indx = 1 or indx = 2 'This will determine if we're in a cell where we want the autocomplete
   Same code as before except

   If indx = 1 
     set autocompletecustomsource = scAutocomplete1
   else
     set autocompletecustomsource = scAutocomplete2
end if
  set autocompletesource = customsource
... 

So you are basically first checking which cell you're in and if it's a cell that you want a autocomplete in, you start setting up the autocomplete code but when it's time to set the autocompletecustomsource, you again need to determine which cell you're in and set it accordingly.
Was This Post Helpful? 1
  • +
  • -

#13 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Autocomplete of text in datagridview..

Posted 14 April 2013 - 02:48 AM

View PostCharlieMay, on 13 April 2013 - 05:26 AM, said:

You should be able to use the same code, only I would assign the currentcell.columnindex to an integer

If indx = 1 or indx = 2 'This will determine if we're in a cell where we want the autocomplete
   Same code as before except

   If indx = 1 
     set autocompletecustomsource = scAutocomplete1
   else
     set autocompletecustomsource = scAutocomplete2
end if
  set autocompletesource = customsource
... 

So you are basically first checking which cell you're in and if it's a cell that you want a autocomplete in, you start setting up the autocomplete code but when it's time to set the autocompletecustomsource, you again need to determine which cell you're in and set it accordingly.

Thnx its working now :alucard:/>/> but this is not what i want...
ok i tell you when i only need this in column 1 but when i type in column and i select one the item from selected option i should get whole row with item as my current row,


Item_name item_id
Disprin 1
Decosil 2
Perise 3
like i type "pe" and select item_name "perise" so i want "item_id" also in current row with item_name..






i tried this but i think this one is not that good idea.
Private Sub DataGridView1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView1.KeyDown
        If e.KeyCode = Keys.Enter Then
            Try
                q = "select maker_name,maker_id from item_maker_master where maker_name='" & DataGridView1.CurrentRow.Cells(0).Value & "'"
                cn = New SqlConnection(My.Settings.PharmacyConnectionString)
                Dim cmd As New SqlCommand(q, cn)
                cn.Open()
                ad = New SqlDataAdapter(q, cn)
                ds = New DataSet
                ad.Fill(ds, "item_maker_master")
                DataGridView1.DataSource = ds.Tables(0)
                ad.Dispose()
                cn.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
    End Sub



This post has been edited by vks.gautam1: 14 April 2013 - 03:20 AM

Was This Post Helpful? 0
  • +
  • -

#14 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Autocomplete of text in datagridview..

Posted 16 April 2013 - 07:19 AM

is there any way to move my whole row with the current search typing
Was This Post Helpful? 0
  • +
  • -

#15 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Autocomplete of text in datagridview..

Posted 16 April 2013 - 08:10 AM

ISSUE1:
Datagrid is bound to a datasource. Changing the datasource will affect the entire datagrid. Meaning if you type in a cell and query to return a row and then set that result as the datasource for the datagrid, the datagrid will only display that row.

Can you explain why this method you are trying to perform is essential because it seems completely out of the norm.

You would probably be better off designing your grid at design time with the columns you want to display. Then autofill the first column with the row criteria that would populate it.

Then query the database from that result and use the returned columns, to fill the columns of the DGV by specifying each one.

Another option would be to populate the grid with a group of data. Then limit the view based on what you type in a autocomplete textbox that would then reduce the view of the datatable to the row(s) that result in that filter.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2