Trying to run search suggest and populate

[mikejs]

Hi I am using the following to auto search db

Dim connString As String = My.Settings.strConn
        Dim conn As New SqlConnection(connString)
        Dim cmd As New SqlCommand("select firstName, existing from tenant Where (existing = 1)", conn)
        Dim da As New SqlDataAdapter(cmd)

        Dim ds As New DataSet
        Try
            conn.Open()
            da.Fill(ds, "List")

            Dim col As New AutoCompleteStringCollection ' From which our names will come

            Dim i As Integer
            For i = 0 To ds.Tables(0).Rows.Count - 1
                col.Add(ds.Tables(0).Rows(i)(0).ToString())
            Next

            ComboBox1.AutoCompleteSource = AutoCompleteSource.CustomSource
            ComboBox1.AutoCompleteCustomSource = col
            ComboBox1.AutoCompleteMode = AutoCompleteMode.Suggest


        Catch ex As Exception
            MsgBox(ex.Message)

        Finally
            conn.Close()

        End Try

I am currently using this to populate the text boxes on the click event for the combo list I tried selectedIndex as well

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.Click
        If ComboBox1.Text.Length > 0 Then
            Dim connString As String = My.Settings.strConn
            Dim conn As New SqlConnection(connString)
            Try
                conn.Open()
                Dim qry As String = "select * from tenant where firstName='" + ComboBox1.Text + "'"

                Dim cmd As New SqlCommand(qry, conn)

                Dim dr As SqlDataReader = cmd.ExecuteReader()
                If dr.Read() Then
                    TextBoxDob.Text = dr(5)
                    TextBoxFirstName.Text = dr(1)
                    TextBoxLastName.Text = dr(2)
                    TextBoxId.Text = dr(0)
                    TextBoxPhone.Text = dr(4)
                    TextBoxEmail.Text = dr(3)
                    TextBoxNotes.Text = dr(13)
                   
                End If
                dr.Close()
            Catch ex As Exception

            Finally

                conn.Close()
            End Try
        Else
            'do nothing
        End If
    End Sub

I want the text boxes to populate when a result is clicked from the drop list of the combo box at the moment

you start typing in the combo box and it starts to populate with first names then you pick one of the names from the list which is displayed in the drop down box you then have to click the box to get it to populate the text boxes.

there are two things I would like it to do one would be to display the first and last name in the combo-box if this is possible and also have the text boxes populate when a name is selected.

any help appreciated

[mikejs]

Not sure if this was the best method I added a timer and just runs a check every sec to see if the combobox lenth is >0

[codeorder]

Use the _TextChanged event of the ComboBox instead of a Timer.

Private Sub ComboBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.TextChanged
        '// search code here to populate items.
    End Sub

>>...one would be to display the first and last name in the combo-box if this is possible...
When searching and getting values from db, add ALL values from the item to an ArrayList and only add the First/Last name to ComboBox.

On cmb_SelectedIndexChanged, get values from ArrayList by using the ComboBox.SelectedIndex.

Private arlDBitems As New ArrayList

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        With ComboBox1
            If Not .SelectedIndex = -1 Then
                MsgBox(arlDBitems(.SelectedIndex))
            End If
        End With
    End Sub

    Private Sub ComboBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.TextChanged
        '// .Clear ArrayList and ComboBox items here.
        '// search code here to populate items to ComboBox with First/Last name only and all other info to the ArrayList.
    End Sub

Hope this helps.

[mikejs]

Hi I am using the method posted above when you start to type in the combo box it will only allow the first char is it possible for it to populate as each char is typed is name john at the moment if I type jo it goes to o rather than continue to populate

thanks

M

[codeorder]

Private Sub ComboBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.TextChanged
        With ComboBox1
            Select Case .Text.Length
                Case Is > 1
                    Me.Text = .Text(.Text.Length - 1)
                Case Else
                    Me.Text = .Text
            End Select
        End With
    End Sub

[mikejs]

That didn't work gave the same result as I type a name it only shows the last char typed so I type mike it only shows e in the combo box ? I have gone back to my timer which seems to work :-)

thanks

Mike

[codeorder]

>>...it to populate as each char is typed is name john at the moment if I type jo it goes to o rather than continue to populate

You get what you ask for.:)

[mikejs]

Hi I am using the following to populate a combo box

conn.Open()
                Dim qry As String = "select * from tenant where existing = 1 AND firstName='" + ComboBoxSearchExist.Text + "'"
                Dim cmd1 As New SqlCommand(qry, conn)
                Dim dr As SqlDataReader = cmd1.ExecuteReader()
                If dr.Read() Then
                    TextBoxTenantIdSearchExist.Text = dr(0)
                    TextBoxTenantFirstNameSearchExist.Text = dr(1)
                    TextBoxTenantLastNameSearchExist.Text = dr(2)
                    TextBoxEmailSearchExist.Text = dr(3)
                    TextBoxPhoneSearchExist.Text = dr(4)
                    TextBoxDobSearchExist.Text = dr(5)
                    TextBoxTenantNotesSearchExist.Text = dr(13)
                    'TextBoxPropRefUp.Text = DropDownListPropRefUp.SelectedText
                End If
                dr.Close()
                conn.Close()

however I have several tenants named John the results only show one name looking at the list of names in the db it looks like its only showing the first one it finds I need it to show the list of john's

thanks

M

[mikejs]

Sorry I am using this to populate the combo box

Dim ds As New DataSet
        Try
            conn.Open()
            da.Fill(ds, "List")

            Dim col As New AutoCompleteStringCollection ' From which our names will come

            Dim i As Integer
            For i = 0 To ds.Tables(0).Rows.Count - 1
                col.Add(ds.Tables(0).Rows(i)(0).ToString())
            Next

            ComboBoxSearchPrev.AutoCompleteSource = AutoCompleteSource.CustomSource
            ComboBoxSearchPrev.AutoCompleteCustomSource = col
            ComboBoxSearchPrev.AutoCompleteMode = AutoCompleteMode.Suggest

[mikejs]

I think I had some of my code mixed up this is what I have now

Private Sub ComboBoxSearchExist_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBoxSearchExist.TextChanged
        If RadioButtonfirstNameSearchExist.IsChecked = True Then
            bindingSource1.DataSource = Nothing
            Dim connString As String = My.Settings.strConn
            Dim conn As New SqlConnection(connString)
            Dim cmd As New SqlCommand("select firstName, existing from tenant Where (existing = 1)", conn)
            Dim da As New SqlDataAdapter(cmd)
            Dim ds As New DataSet
            Try
                conn.Open()
                da.Fill(ds, "List")

                Dim col As New AutoCompleteStringCollection ' From which our names will come

                Dim i As Integer
                For i = 0 To ds.Tables(0).Rows.Count - 1
                    col.Add(ds.Tables(0).Rows(i)(0).ToString())
                Next

                ComboBoxSearchExist.AutoCompleteSource = AutoCompleteSource.CustomSource
                ComboBoxSearchExist.AutoCompleteCustomSource = col
                ComboBoxSearchExist.AutoCompleteMode = AutoCompleteMode.Suggest


            Catch ex As Exception
                My.Computer.Audio.Play(My.Resources.femaleerror, AudioPlayMode.WaitToComplete)
               DesktopAlert1.Show()
                DesktopAlert1.CaptionText = "Sorry!"
                DesktopAlert1.ContentText = "Error: " + ex.Source + ": " + ex.Message
            Finally
                cmd.Dispose()
                conn.Close()

            End Try

        End If

If ComboBoxSearchExist.Text.Length > 0 Then
            Dim connString As String = My.Settings.strConn
            Dim conn As New SqlConnection(connString)
            Try

                If RadioButtonfirstNameSearchExist.IsChecked = True Then
                    conn.Open()
                    Dim qry As String = "select * from tenant where existing= 1 AND firstName='" + ComboBoxSearchExist.Text + "'"
                    Dim cmd1 As New SqlCommand(qry, conn)
                    Dim dr As SqlDataReader = cmd1.ExecuteReader()
                    If dr.Read() Then
                        TextBoxTenantIdSearchExist.Text = dr(0)
                        RadTextBoxTenantFirstNameSearchExist.Text = dr(1)
                        TextBoxTenantLastNameSearchExist.Text = dr(2)
                        TextBoxEmailSearchExist.Text = dr(3)
                        TextBoxPhoneSearchExist.Text = dr(4)
                        TextBoxDobSearchExist.Text = dr(5)
                        TextBoxTenantNotesSearchExist.Text = dr(13)

                    End If
                    dr.Close()
                    conn.Close()

it is only letting me type one char? how can I make it let me type a name ?