[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Autocomplete source from SQL

Posted on 2009-05-01
32
Medium Priority
?
979 Views
Last Modified: 2013-11-26
I'm building an autocomplete source from a SQL query where I want to grab both the ID column, as well as the description.
This will populate a text box, but to make it easier to retrieve, I'd love for each item to have some way to remain associated with it's ID, so that I can simply update my other table with the ID, as opposed to re-performing a search once the selection is made, in order to find out what it's ID is.
I know there were possibilities like this with vb6 (display member/ value member if memory serves me correctly).
Yet, is there a way to do this with a text box?  I'm open to any suggestions...
0
Comment
Question by:sirbounty
  • 15
  • 6
  • 4
  • +3
30 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24284610
What if you created a user control that inherited the existing textbox control and you added another member to it. Below, you could data-bind the ValueMember field to your ID column.
public partial class WebUserControl1 : System.Web.UI.WebControls.TextBox
{
    public object ValueMember { get; set; }
}

Open in new window

0
 
LVL 67

Author Comment

by:sirbounty
ID: 24285513
Hmm - I'm developing a winform app, not a web app. Not sure if that matters.
And I'm not really sure how to go about that either, but it sounds promising - can you explain further?
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24286315
Sorry, I thought I read "SqlDataSource" and assumed this was a web project :\

In any event, the same can be accomplished in Forms app. An easier approach, though, would be to use a ComboBox. It has a ValueMember and DataMember already. Are you opposed to using a ComboBox?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 67

Author Comment

by:sirbounty
ID: 24286653
combo box doesn't really work for what I'm doing.
However, the autocomplete is held in a sql (express) table...
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24288277
I'm not sure how your implementing you lookup and populating, but for starters, you can again make a custom textbox and add a value member property. When you set your text, you can also set the value member.
Public Class AutoCompleteTextBox
    Inherits TextBox
 
    Private vMember As Object
 
    Public Property ValueMember()
        Get
            Return Me.vMember
        End Get
        Set(ByVal value)
            Me.vMember = value
        End Set
    End Property
 
End Class

Open in new window

0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24288283
I swear i'm not drunk... I have the most difficult time typing on here :\

The previous should say:


I'm not sure how you're implementing your lookup and populating the text, but for starters, you can again make a custom textbox and add a value member property. When you set your text, you can also set the value member.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24307484
I understood what you meant - no indication that you're drunk.. ;^)
Okay, so I'm back at this - and think I understand what you're stating, but for clarification:
1) Can this be in my module or does it have to be in my form code (presumably it doesn't really matter since it's its own class)
2) Presumably I would have to programmatically add this new control to the form, as opposed to drawing a standard text box where I need it?

0
 
LVL 67

Author Comment

by:sirbounty
ID: 24308262
*Points increased to 500.

My source for the autocomplete would come from a simply sql query (Select FoodDesc From tblFood).
But I need to allow the user to enter other text as well.  Just trying to make it easier, so that if it's a frequently entered food item, it would auto populate the numeric values for the nutrition info (calories, fat, carbs, etc).

So if I type in

Salmon fillet, it would start completing with the S, then a, etc. and once the user tabbed out of the field, it would then auto-populate those values (I know how to accomplish this - just stating it for clarity on what I'm trying to accomplish).

0
 
LVL 48

Expert Comment

by:Mikal613
ID: 24315974
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24316043
Not using AJAX (thought that was more for web apps?).
This is just a simple win form app...
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24316071
SirBounty,
You could use the Tag property of the textbox for the exact same purpose as you would do the valuemember of the extended control. The Tag property is of type Object and can hold any value.
0
 
LVL 14

Expert Comment

by:jake072
ID: 24316223
Why not just use a ComboBox and be done with it?

Jake
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24316330
Here's the scenario:
This is a daily food journal.

I have a textbox for the purpose of entering the food descriptiong (1 Cup Milk, 1 Banana, Salmon Filet, etc).
I then have a search feature that grabs certain related items to that entered from a web site, to populate a combo box with matching options that would determine how many calories, etc are in that item.

I'd like that after some consistent entries are entered (into tblFood), that as the app is opened and I begin typing "1 Cup Coffee", it would auto-complete allowing me to simply select it and it auto populate my calories, carbs, etc (from a quick sql query).

So perhaps what I'm attempting is not possible, in this manner?
I know tag is a great option for a single entry (FoodId = 12 for 1 Cup Coffee), but for populating the autocomplete source with
1  1 Egg
2  Raisin Bran
3  1 Cup OJ
4  Pop Tart
5  Oatmeal

etc - how would autocomplete update the property tag if I selected a pop tart?

I probably could do a combo box though.  Admittedly I haven't thought too far down that train of thought...
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24316372
How would autocomplete update the ValueMember of a custom textbox for that matter? Combobox is a better solution.
0
 
LVL 14

Assisted Solution

by:jake072
jake072 earned 200 total points
ID: 24316745
sirbounty,

I would use 2 comboboxes... 1 for quantity (you shouldn't really have different entries for 1 Egg and 2 Egg should you)???  And another combobox for the item...

I've made a similar recipe program for my mother, and that's what I did =) ... I also included a Unit of Measure too...  eg. cup, teaspoon, etc...

Jake
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24316827
Hmm - good point.
I'm not sure how detailed I really want to get, as I only plan to use this for about a year. But I may share it with one or two other friends.
Perhaps I'll add the counter (numeric up/down should work), but forego the unit of measure (not sure how to track that).
Essentially I want to display both a daily and weekly summary of target ranges (i.e my 'goal' is set to intake no more than 1800 calories...how am I doing for today vs. how am I doing for the week/month).

I'm working on putting the combo solution together.  I'll post back if I get stuck...
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24317287
Well, this may be reliant on http://www.experts-exchange.com/Q_24385140.html
For some reason, I can't see 'any' of the auto complete entries, even though I know they're there and I can step through and see them being added to the collection...
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24317556
What mode are you using for the auto-complete?  How are you adding/binding the auto-complete to the data?
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24317666
I have a function that when passed the sql string returns the collection, then I set the control's autocompletecustom source to that collection...
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24317893
Do you have something like this?



Public Class Form1
 
    Private m_source As New CustomSource()
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        cb.AutoCompleteMode = AutoCompleteMode.Append
        cb.AutoCompleteSource = AutoCompleteSource.CustomSource
 
        m_source.AddEntry("Bob", 1)
        m_source.AddEntry("Neil", 2)
        m_source.AddEntry("Eric", 3)
 
        cb.AutoCompleteCustomSource = m_source.GetStringList()
 
    End Sub
 
    Public Class CustomSource
 
        Private m_collection As AutoCompleteStringCollection = Nothing
        Private m_list As Dictionary(Of String, SourceEntry) = Nothing
 
        Public Sub New()
            m_collection = New AutoCompleteStringCollection()
            m_list = New Dictionary(Of String, SourceEntry)
        End Sub
 
        Public Sub AddEntry(ByVal name As String, ByVal id As Integer)
            m_collection.Add(name)
            m_list.Add(name.ToLower(), New SourceEntry(name, id))
        End Sub
 
        Public Function FindEntry(ByVal name As String) As SourceEntry
            If Not m_list.ContainsKey(name.ToLower()) Then
                Throw New ArgumentException("Invalid entry key")
            End If
            Return m_list(name)
        End Function
 
        Public Function GetStringList() As AutoCompleteStringCollection
            Return m_collection
        End Function
 
        Public Class SourceEntry
            Public Name As String
            Public ID As Integer
            Public Sub New(ByVal newName As String, ByVal newID As Integer)
                Name = newName
                ID = newID
            End Sub
 
        End Class
    End Class
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim entry As CustomSource.SourceEntry = m_source.FindEntry(cb.Text)
 
        MessageBox.Show("Entry=" & entry.Name & ", id=" & entry.ID)
    End Sub
End Class

Open in new window

0
 
LVL 67

Author Comment

by:sirbounty
ID: 24317949
I'll try it - though I've used similar code (to what I have posted in that other thread) with success in the past...
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24317995
So, you're using a button to 'find' the entry?  In another app, I was simply typing and it was completing the text for me - isn't this how it's designed to work (or have I misread the code above?).


0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24317996
When you say, "not showing up", are you saying that it is not responding to typing in the ComboBox?
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24318005
Correct (this was 'first' a textbox).
When I type in the combo, I get no suggestions at all...
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24318007
I was trying to show how you could auto-complete with text (Name), and match that up against value (ID).
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24318017
Ah - light bulb is fizzling on... ;^)
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 1800 total points
ID: 24318018
What are the settings for AutoCompleteMode, AutoCompleteSource, and AutoCompleteCustomSource?
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24318085
Um, I neglected to set the autocompletesource... :$
Perhaps you should point that out in the other question, so that one is resolved (autocomplete is working now - thank you).

So, to resolve this - it looks like I need to build a custom class dependent on a dictionary so that I can search it in a given event (probably _Leave).
Question - as far as timing is concerned, is it just as convenient to simply query the database again, instead of a dictionary object?  The db will be a local user instance...
0
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 1800 total points
ID: 24318167
If you are talking about looking the value up in an in-memory structure, then you can go with that.  If you are talking about hitting an external database, like SQL Server, then look at the process.  Usually, a few trips to the database would not be noticed, but a few hundred might be noticed.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24319779
Local-only SQL Server Express User instance, so I don't think it'd be a problem.


0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question