Member Avatar for Marlon189

Please I have an autocomplete textbox which fetches value from a tabe in the database. But after i choose the name i want to get the id of the value selected and save it in the database. Am using mysql and c#.

using (MySqlConnection con = new MySqlConnection(MyConString))
            {
                connection.Open();
                string command = "select * from suppliers";
                MySql.Data.MySqlClient.MySqlCommand myCommand = new MySql.Data.MySqlClient.MySqlCommand(command, connection);
                MySqlDataReader reader = myCommand.ExecuteReader();

                AutoCompleteStringCollection MyCollection = new AutoCompleteStringCollection();
                while (reader.Read())
                {
                 MyCollection.Add(reader["Name_of_Supplier"].ToString());

                }

                txtSupplier.AutoCompleteCustomSource = MyCollection;

                connection.Close();
            }
  • 3 Contributors
  • 7 Replies
  • 3K Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Marlon189

Dani AI

Generated

What you want is the ComboBox-style DisplayMember/ValueMember pattern, but with a TextBox. Since TextBox autocomplete only handles strings, you have to keep your own name->id map and fill it when you build the suggestions. Then, when the user picks a suggestion (on Validated/Leave), look up the id and stash it in your hidden txtsup_id.

Example approach:

// 1) Load names and keep a lookup of name -> id
var nameToId = new Dictionary<string,int>(StringComparer.OrdinalIgnoreCase);
var ac = new AutoCompleteStringCollection();

using (var conn = new MySqlConnection(MyConString))
using (var cmd = new MySqlCommand(
    "SELECT ID, Name_of_Supplier FROM suppliers ORDER BY Name_of_Supplier;", conn))
{
    conn.Open();
    using (var rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            var id = rdr.GetInt32(0);
            var name = rdr.GetString(1);
            if (!nameToId.ContainsKey(name)) nameToId[name] = id;   // handle dup loads
            ac.Add(name);
        }
    }
}

txtSupplier.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
txtSupplier.AutoCompleteSource = AutoCompleteSource.CustomSource;
txtSupplier.AutoCompleteCustomSource = ac;

// 2) When the user finishes editing, look up the id
private void txtSupplier_Validated(object sender, EventArgs e)
{
    if (nameToId.TryGetValue(txtSupplier.Text.Trim(), out var id))
        txtsup_id.Text = id.ToString();
    else
        txtsup_id.Clear(); // unknown text
}

Notes:

  • ’s extra SELECT by name will work, but do it parameterized and only if you truly need a round-trip. Example: SELECT ID FROM suppliers WHERE Name_of_Supplier = @name LIMIT 1 plus cmd.Parameters.AddWithValue("@name", txtSupplier.Text);. This avoids SQL injection and quoting bugs.
  • If supplier names are not unique, you need a disambiguator. Easiest: show suggestions like Name (City) and still map by the chosen name+city key, or switch to a ComboBox where ValueMember/DisplayMember are built in.
  • Avoid SELECT *; fetch only the columns you need.

Recommended Answers

All 7 Replies

Member Avatar for rproffitt

I worry your question was not worded well. Your topic question is "Get id of autocomplete textbox in c# windows form" so in the code provided the id or name of the control appears to be txtsupplier. If that's incorrect, just click once on the object you have on screen in the designer view and get its name/id there.

Member Avatar for Marlon189

Sorry . What i mean was after i select the suggested element from the db i have a hidden text box that get filled with the id of the selected element. say txtsup_id. Your help will be really appreciated. Thnx

Member Avatar for rproffitt

Still unclear. You state "i have a hidden text box that get filled with the id of the selected element. say txtsup_id." So you have that and you have the name of the source object so the question to me is not any clearer.

Member Avatar for Marlon189

look at it like a combo box. which has the id and text(diplaymember and display value). buh this time in a textbox situation

Member Avatar for rproffitt

A textbox has an id which you know and the contents which you know. Your question is unclear to me.

Member Avatar for r310070

If I understand your question correctly, you can use a query like
query = String.Format("select ID from suppliers where Name_of_Supplier = {0}", txtSupplier.Text);
that should return the id

Member Avatar for Marlon189

So should i do another query after my first query. Or i should replace this with the my query. Thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.