none
how to get autocomplete on when we select state and related cities in other textbox? RRS feed

  • Question

  • I have two textboxes named: txtReceiver_State and txtReceiver_City

    Problem: when i select state then pressed tab to jump to next textbox and then i start typing it does not show any cities related to selected state

    How to get rid of this problem!!

    Private Sub AutoCompleteState()
            con = New OleDbConnection(connString)
            con.Open()
            query = "SELECT * FROM [State]"
            cmd = New OleDbCommand(query, con)
            Dim ds As New DataSet
            da = New OleDbDataAdapter(cmd)
            da.Fill(ds, "State")

            Dim col As New AutoCompleteStringCollection
            Dim i As Integer
            For i = 0 To ds.Tables(0).Rows.Count - 1
                col.Add(ds.Tables(0).Rows(i)("State_Name").ToString())
            Next
            txtReceiver_State.AutoCompleteSource = AutoCompleteSource.CustomSource
            txtReceiver_State.AutoCompleteCustomSource = col
            txtReceiver_State.AutoCompleteMode = AutoCompleteMode.SuggestAppend
            con.Close()
        End Sub


        Private Sub AutoCompleteCity()
            con = New OleDbConnection(connString)
            con.Open()
            query = "Select City_Name from City" _
                & "where "
            cmd = New OleDbCommand(query, con)
            Dim ds As New DataSet
            Dim da As New OleDbDataAdapter(cmd)
            da.Fill(ds, "City")

            Dim col As New AutoCompleteStringCollection
            Dim i As Integer
            For i = 0 To ds.Tables(0).Rows.Count - 1
                col.Add(ds.Tables(0).Rows(i)("City_Name").ToString())
            Next
            txtReceiver_City.AutoCompleteSource = AutoCompleteSource.CustomSource
            txtReceiver_City.AutoCompleteCustomSource = col
            txtReceiver_City.AutoCompleteMode = AutoCompleteMode.SuggestAppend
            con.Close()
        End Sub

    Monday, March 19, 2018 8:39 AM

All replies

  • Hello,

    You have a incomplete SELECT statement in AutoCompleteCity

    query = "Select City_Name from City" _
                & "where " 

    You need to finish by doing WHERE =  to be set to the text of the other text box.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, March 19, 2018 9:13 AM
    Moderator
  • I just realized I answered a question pretty much the same as your but with SQL-Server which you might benefit from. In this case data is loaded from a class

    Imports System.Data.SqlClient
    
    Public Class Operations
    
        Private _connectionString As String =
            "Data Source=KARENS-PC;Initial Catalog=ForumExample;" &
            "Integrated Security=True"
    
        Private _stateTable As DataTable
        Public ReadOnly Property StateTable As DataTable
            Get
                Return _StateTable
            End Get
        End Property
        Private _cityTable As DataTable
        Public ReadOnly Property CityTable As DataTable
            Get
                Return _CityTable
            End Get
        End Property
        ''' <summary>
        ''' Load country and city data at once
        ''' </summary>
        Private Sub LoadStatesCityTables()
            _StateTable = New DataTable
            _CityTable = New DataTable
    
            Using cn As New SqlConnection With {.ConnectionString = _connectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = "SELECT  Id,Name " &
                        "FROM dbo.StateMaster ORDER BY Name"
    
                    cn.Open()
    
                    _StateTable.Load(cmd.ExecuteReader)
    
                    cmd.CommandText = "SELECT ID,Name,StateID " &
                                      "FROM dbo.CityMaster " &
                                      "ORDER BY Name"
    
                    _CityTable.Load(cmd.ExecuteReader)
                End Using
            End Using
        End Sub
        Public Sub New()
            LoadStatesCityTables()
        End Sub
    End Class
    

    Form code

    Public Class Form1
        Private cityTable As DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) _
            Handles MyBase.Load
    
            Dim ops As New Operations
    
            cboStates.DataSource = ops.StateTable
            cboStates.DisplayMember = "Name"
    
            cityTable = ops.CityTable
    
            cboCity.DataSource = cityTable
            cboCity.DisplayMember = "Name"
    
    
            cboCity.AutoCompleteMode = AutoCompleteMode.SuggestAppend
            cboCity.AutoCompleteSource = AutoCompleteSource.CustomSource
    
    
            AddHandler cboStates.SelectedIndexChanged,
                AddressOf cboStates_SelectedIndexChanged
    
            GetCities()
    
        End Sub
        Private Sub cboStates_SelectedIndexChanged(sender As Object, e As EventArgs)
            GetCities()
        End Sub
        Private Sub GetCities()
            Dim source As New AutoCompleteStringCollection
    
            Dim stateId = CType(cboStates.SelectedItem, DataRowView).
                    Row.Field(Of Integer)("Id")
    
            cityTable.DefaultView.RowFilter = $"StateId = {stateId}"
    
            for each item as DataRowView In cityTable.DefaultView
                source.Add(item("Name").ToString())
            Next
    
            cboCity.AutoCompleteCustomSource = source
    
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, March 19, 2018 10:42 AM
    Moderator
  • Also put some spaces before and after the word "where".

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Monday, March 19, 2018 11:46 AM
    Moderator