Hi all,

A portion of the app I am writing needs to have the user enter/select and
authors name.  Currently I have a combobox filled with all of the available
authors, but it is getting to the point where there can be a few hundred
names in the combox.  The purpose of the combo box was to make available the
auto complete (typing in part of the name and automatically filling
in/selecting the rest).  This allows seemless entry, and if no match was
found, then it was considered a new author and was added to the authors table.

With so many authors in the combobox and the possiblility of it increasing
by a huge amount, what would be an alternative way to allow the entry/lookup
and selection/addition of the authors? I thought about having a textbox to
type in part of the name, then cliking on a button to popup another form to
show all of the authors that match, but that is a bit clunky and would slow
down the process.

Any ideas and/or input would be appreciated.

George

George wrote:
Show quoteHide quote"but that is a bit clunky and would slow down the process."

Yes, but having hundreds of authors in a combobox would slow down the
process too.  You'll have to make some sort of sub-searching and the way
you describe would work.  You wouldn't have to pop up another form, you
could do it on the same form though.  Another idea is to not populate
the combobox until they type in the first letter, then just go and get
all the authors that start with that letter.

Chris

So I guess I would look at the length of the text in the combo, and anytime
it is 1, I would clear the combo list, connect to the database and run a qry
on the authors table?

I'll give that a try...thank you.

George

Show quoteHide quote

George,

I think that this solution will slow down the process even more while it is
as well around a famous Combobox bug with the first position .

http://support.microsoft.com/default.aspx?scid=kb;en-us;814346

And how to handle as the user does not know the first character but just
want to scroll.

In my country by instance is a name as Peter van der Goes mostly written in
a system as LastName Goes MiddleName van der FirstName Peter. While it is
sometimes as well written as LastName Van der Goes, FirstName Peter. This
kind of thing will probably be in every country, I know it especially from
Holland. However in Holland is this name always in a phonebook located at
the G.

What is the source of the table. (In other words are you using the items or
are you using a datasource). Maybe we can find a solution in the way as you
said, combined with filling it complete.

Just my thought,

Cor


Show quoteHide quote

The source is a local Access database.  This is the routine I came up with. 
It is run when the combox entry is a length of 1:

        cmbDesigner.Items.Clear()

        ' Retrieve designer information from the table
        Dim DSet As New DataSet, SQLStr As String
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim dbAdaptr As System.Data.OleDb.OleDbDataAdapter = New
System.Data.OleDb.OleDbDataAdapter
        dbConn.Open()

        Dim tRow As DataRow, tTbl As DataTable
        With dbAdaptr
            .TableMappings.Add("Table", "designers")
            SQLStr = "SELECT * FROM designers "
            SQLStr = SQLStr & "WHERE (Left([designers]![name],1) Like '" &
Microsoft.VisualBasic.Left(cmbDesigner.Text, 1) & "')"
            cmd = New System.Data.OleDb.OleDbCommand(SQLStr, dbConn)
            cmd.CommandType = CommandType.Text
            .SelectCommand = cmd
            .Fill(DSet)
            .Dispose()
        End With

        tTbl = DSet.Tables.Item(0)
        DSet.Dispose()
        dbConn.Close()

        For Each tRow In tTbl.Rows
            cmbDesigner.Items.Add(New ComboItem(tRow("name").ToString,
tRow("designer").ToString))
        Next

        DSet.AcceptChanges()
        tTbl = DSet.Tables.Item(0)
        DSet.Dispose()
        dbConn.Close()

        cmbDesigner.SelectionStart = 2



Show quoteHide quote

George,

I have made a new sample and put it on our website.

http://www.vb-tips.com/default.aspx?ID=aaf8f7e5-d5e8-4532-980f-c22411591992

I hope this helps,

Cor


Show quoteHide quote

I'm at the moment doing something similar.

I have a database of  product codes (potentially very large), currently
listed in a combobox.

I have used a combination of InStr and a DataGrid so that when a user
types in a partial code, it will change the contents of the DataGrid to
show the product codes that match.
What you need to do is create the DataColumns (for the DataGrid, as
many as necessary), create the DataTable, Add the DataColumns to the
DataTable, then iterate through each InStr match and add it to the
DataTable (Using a DataRow). Then just display DataTable in a DataGrid.

This might give you some hints:


Private Sub txtProductCode1_TextChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles txtProductCode1.TextChanged

            Dim dcProductSearch As DataColumn
            Dim dtProductSearch As DataTable = New
DataTable("ProductSearch")
            Dim drProductSearch As DataRow 'An individual DataRow of
the searched product
                    Dim dtProductSearch As DataTable = New
DataTable("ProductSearch")

            Dim strSearchedString As String
            strProductCode = txtProductCode1.Text.ToUpper

            'Create new columns and adds it to the dtProductSearch
DataTable, which in turn is dispalyed in the datagrid
            dcProductSearch = New DataColumn
            dcProductSearch.ColumnName = "ProductCode"
            dtProductSearch.Columns.Add(dcProductSearch)
            dcProductSearch = New DataColumn
            dcProductSearch.ColumnName = "ProductDescription"
            dcProductSearch.DataType =
System.Type.GetType("System.String")
            dtProductSearch.Columns.Add(dcProductSearch)


            Dim i As Integer
            For i = 0 To 5
                strSearchedSring =
dsDataset.Product.Rows(i).Item("ProductCode").ToString

                If InStr(strSearchedString, strProductCode) > 0 Then
                    drProductSearch = dtProductSearch.NewRow()
                    drProductSearch("ProductCode") =
dsDataset.Product.Rows(i).Item("ProductCode").ToString
                    drProductSearch("ProductDescription") =
dsDataset.Product.Rows(i).Item("ProductDescription").ToString
                    dtProductSearch.Rows.Add(drProductSearch)

                    dgrProductSearch.DataSource = dtProductSearch
                End If
            Next i


Don't forget to put this code into the text entry boxes TextChanged
event

I was reading the post, and can relate to your question.  I have had
similar situations where I had a combobox with many items.  The
solution I used was a popup search dialog.  My colleague developed a
nice solution where you type in the first part of a string, and then
when you tab out of the text box, it searches the database, only
returning records that contain the search string.  In my opinion, this
is much better than putting them all in a combobox.

Another idea is to use the same method that the Firefox browser uses
for searching within bookmarks.  Take a look at firefox bookmarks.
Basically, it would look like a textbox ontop of a listbox.  The
textbox would be where you type and the listbox would automatically
shrink / grow depending on the matches.  The match criteria would
probably be "contains the search string".  You would do this in the
text box's change property (not sure off hand what it is called).

Now for performance, you may want to use the dataset's (or recordset's)
filter property instead of re-querying the database.  I'm not sure
which method (requerying or filtering) has better performance, but I
would think that filtering would be better.

Let me know how you make out.

Seth

Error: Login failed for user ????
How to retrive Outlook 2003 style gradient color schemes?
Printing the contents of a textbox
Copy List(Of type) to another List(Of type)
Accessing Properties within a Panel
Inheritance
Example of encryption
Base Class Method to use Shadow'ed member variable of Derived Class?
Reading a Webpage Source with Cyrillic Characters
Serialization of Object Graph (Inheriting ArrayList)