How to make DataGridView move to next cell instead of next row after AutoComplete string selection made VB.net?

Question

I have a DataGridView where some of the cells only allow values from an AutoCompleteStringCollection. When I enter the cell and begin to type a value, a drop-down window appears with viable options. If I finish typing it out and hit enter or tab, I go to the next cell. However, if I start typing and then select an option from the drop-down menu, it moves one row down in the same column. I’d like it to move to the next cell/next column SAME row after selecting from the drop-down box. Any idea how I go about doing this. Thanks in advance for your help!!

Private Sub getData(ByVal DataCol As AutoCompleteStringCollection)
    Dim comm As SqlCommand
    Dim adapt As New SqlDataAdapter
    Dim ds As New DataSet

    Dim sql As String = "SELECT DISTINCT ValueOption From HR.dbo.RecruitVal WHERE ColumnName = 'Source'"

    Try

        If SqlConn.State = ConnectionState.Broken Then SqlConn.Close()
        If SqlConn.State = ConnectionState.Closed Then SqlConn.Open()

        comm = New SqlCommand(sql, SqlConn)
        adapt.SelectCommand = comm
        adapt.Fill(ds)
        adapt.Dispose()
        comm.Dispose()
        For Each row As DataRow In ds.Tables(0).Rows
            DataCol.Add(row(0).ToString())
        Next
    Catch ex As Exception
        MessageBox.Show("Error")
    End Try

End Sub


Private Sub RAppSrc(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles RAppGrid.EditingControlShowing
    Dim titleText As String = RAppGrid.Columns(RAppGrid.CurrentCell.ColumnIndex).HeaderText
    If titleText.Equals("Source") Then
        Dim autoText As TextBox = TryCast(e.Control, TextBox)
        If autoText IsNot Nothing Then
            autoText.AutoCompleteMode = AutoCompleteMode.SuggestAppend
            autoText.AutoCompleteSource = AutoCompleteSource.CustomSource
            Dim dataCol As New AutoCompleteStringCollection()
            getData(dataCol)
            autoText.AutoCompleteCustomSource = dataCol
        End If
    End If
End Sub

Cell - Validating

    If (appCol.Name = "Source") Then
        Dim sql As String = " SELECT 1 FROM Hr.dbo.RecruitVal WHERE ColumnName = 'Source'AND ValueOption = @Source "
        Dim sql2 As String = "SELECT DISTINCT ValueOption from HR.dbo.RecruitVal WHERE ColumnName = 'Source' "
        Dim com As New SqlCommand(sql, SqlConn)
        Dim com2 As New SqlCommand(sql2, SqlConn)
        Dim reader As SqlDataReader
        Dim val As String
        Dim val2 As String
        com.Parameters.Add("@Source", SqlDbType.VarChar).Value = e.FormattedValue
        val = com.ExecuteScalar()
        reader = com2.ExecuteReader()
        If reader.HasRows Then
            While reader.Read()
                val2 += reader.GetValue(0) & "," & Space(3)
            End While
        End If
        If String.IsNullOrEmpty(e.FormattedValue) Then
            'Do Nothing
        ElseIf val = Nothing Then
            MessageBox.Show(" Please Enter a valid Source Option" & vbCrLf & vbCrLf & "Valid Options are as Follows:" & vbCrLf & vbCrLf + val2.ToString(), "VALIDATION ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
            RAppGrid.EndEdit()
        End If
    End If

I've just tested few scenarios and I was unsuccessfull. The function to move to the next cell was easy, but it all ends up on the fact that EditingControlShowing eats up the Enter key and won't pass it to i.e. further KeyDown, which I hooked up. It works with all keys but Enter, doh. Of course, one way is to use TAB key instead :-). But that's not an anwer to your question... — Oak_3260548
In my scenario, I make autocomplete by myself, by using event of textchange, keydown and keypress, I directly find just top 1 of record each time to I change my text in cell. — user11982798
In another schenario, autoText declaration is put generally withevents, end then use previewkeyevent of autotext to run code of If e.KeyCode = Keys.Enter Then, DataGridView1.EndEdit(), do validation, and last time set pos of cell, example: DataGridView1.CurrentCell = DataGridView1.Item(1, 0), End If — user11982798

Oak_3260548 Oak_3260548 · Accepted Answer · 2019-09-11T15:25:52

I've tested few scenarios and found out, that the Enter key wouldn't work (all but Enter works). I found this answer on MSDN:

For the enter key, ProcessDataGridViewKey will return true meaning that it has handled the key so you won't get a KeyDown event for it.

If you override ProcessDataGridViewKey and handle the enter key making it return false, you will get the key events you are expecting.

{
 Keys key = (e.KeyCode & Keys::KeyCode); //removes control, alt modifiers
  if(key == Keys.Enter)
 {
   //Still do the default processing for enter so it will move down to the next row
   Base.ProcessDataGridViewKey(e);
   return false; //say we have *not* handled the key
 }
 else
 {
   return Base.ProcessDataGridViewKey(e);
 }
}

The above code is in C#, obvously.

The move function then may look like this (tested only VB.NET version bellow and only with other keys than Enter):

Private Sub DataGridView1_EditingControlShowing(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
    If IsNothing(TryCast(e.Control, DataGridViewTextBoxEditingControl)) = False Then
        Dim tb As DataGridViewTextBoxEditingControl = e.Control
        AddHandler e.Control.KeyDown, AddressOf DataGridView1_Keydown
    End If
End Sub

Private Sub DataGridView1_Keydown(sender As Object, e As KeyEventArgs) ' Handles DataGridView1.KeyDown
    Dim dgv As DataGridView = DataGridView1
    Dim tb As TextBox = CType(sender, TextBox)
    If e.KeyCode = Keys.Enter Then
        e.SuppressKeyPress = True
        dgv.EndEdit()
        If IsNothing(dgv.CurrentCell) = False Then
            Dim cc As DataGridViewCell = dgv.CurrentCell
            If cc.ColumnIndex < dgv.Columns.Count - 2 Then ' if there's a cell to the right of the current one
                dgv.CurrentCell = dgv.Rows(cc.RowIndex).Cells(cc.ColumnIndex + 1)
            ElseIf cc.RowIndex < dgv.Rows.Count - 1 Then   ' if there's a row bellow
                dgv.CurrentCell = dgv.Rows(cc.RowIndex + 1).Cells(0)
            Else
                ' do nothing, stay where you are
            End If
            dgv.CurrentCell.Selected = True
        End If
    End If
End Sub

How to make DataGridView move to next cell instead of next row after AutoComplete string selection made VB.net?

1 Answers