To use a combo ornot

[Chris]

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

"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

 

[Guest]

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

 

[Guest]

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

:

 

[Cor Ligthert [MVP]]

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

 

[Guest]

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



:

 

[Aziz]

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

 

[Seth]

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

 

[Cor Ligthert [MVP]]

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

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



:

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