CODEWITHSUNDEEP
c#sql-server
user1431743
user1431743

Reputation: 107

C# text box values from SQL server as typing

I am creating a C# application to user for my brother in his store.

I have a SQL Server database that stores all sold items, The items are added from the application.

When the user enters text in the 'CustomerName' field in the application, i want it to do a DB call and check for any previous sales with the same customer name (or what has been typed of the name so far) and fill in the text box will the name.

The problem i am having is: if there is a customer called John and another called Joe and i need to select John, if i type in J in the box, it will just select Joe, and then return the text cursor to the beginning of the text box which is rather annoying and inconvenient.

The ideal solution would be is if i type J in the textbox, a dropdown box just below the text box would appear displaying all the Customers with J, allowing the user to select a customer and then filling in the textbox with the value. If i then type Jo, all the records for Jo would appear in the dropdown etc.

The coding part shouldn't be a problem (hopefully), i just wanted to know the best way to approach this.

Upvotes: 2

Views: 3725

Answers (3)

sihirbazzz
sihirbazzz

Reputation: 718

First you need to set these properties of your textbox or write them to your form :

yourTextbox.AutoCompleteMode = AutoCompleteMode.Suggest;
yourTextBox.AutoCompleteSource = AutoCompleteSource.CustomSource;

and then you can create an instance of an ArrayList, a String array or a List (or a dictonary if you will need ID of the customer name) as you prefer..

or just you can use AutoCompleteStringCollection -it is also an implemented string array/collection in C#- class instance to retrieve data into..

then set txtName.AutoCompleteCustomSource = yourListOfTheMatchedNames;

a small example to give you an idea:

void yourTextBox_TextChanged (object sender, EventArgs e)
{
   SqlDataReader dReader;
   SqlConnection conn = new SqlConnection();
   conn.ConnectionString = strConnection;
   SqlCommand cmd = new SqlCommand();
   cmd.Connection = conn;
   cmd.CommandType = CommandType.Text; //better is to use a stored proc or if you use a .NET 3.5 or higher framework then LinqtoSQL
   cmd.CommandText = "Select [yourNameColumn] from yourNameTable where yourNameColumn LIKE" + yourTextBox.Text +"%"; //before lines from this you can set them initializing code part of your form..it will be your choice
   conn.Open();
   dReader = cmd.ExecuteReader();
   if (dReader.HasRows == true)
   {
       yourListOfTheMatchedNames.Clear(); // to clear previous search..its optional to depends of your choice
       while (dReader.Read())
       {                  
              yourListOfTheMatchedNames.Add(dReader["Name"].ToString());    
       }
    }
    else
    {
        MessageBox.Show("There is No Customer Name Starts with You Typed");
    }
    dReader.Close();

    txtName.AutoCompleteMode = AutoCompleteMode.Suggest;
    txtName.AutoCompleteSource = AutoCompleteSource.CustomSource;
    txtName.AutoCompleteCustomSource = yourListOfTheMatchedNames;

}

Upvotes: 0

Steve
Steve

Reputation: 216293

First add/change these properties in your textbox

textBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;

then you could use this method to get a List of customer names that start with your prefix.

public List<string> GetCustomerInfo(string custName)
{
    List<string> result = new List<string>();
    string sql = "Select Name from Customers Where Name like @partName";
    using(SqlConnection con = GetConnection())
    using(SqlCommand cmd = new SqlCommand(sql, con))
    {
         con.Open();
         cmd.Parameters.AddWithValue("@partName", custName + "%"); 
         using(SqlDataReader r = cmd.ExecuteReader())
         {
              while(r.Read()) 
              {
                  if(!r.IsDbNull(0)) 
                      result.Add(r.GetString(0)); 
              }
         }
    } 
    return result; 
}

As a safeguard I will check if the text typed in your combobox is at least three chars

if(textBox1.Text.Length >= 3)
    textBox1.AutoCompleteCustomSource = GetCustomerInfo(textBox1.Text);
else
    textBox1.AutoCompleteCustomSource = null;

Upvotes: 2

jaressloo
jaressloo

Reputation: 210

The best way to do this would be to use jQuery to make an AJAX call to a server side function to do the lookup for you.

Consider this C#:

public string getNames(string prefix)
{
     //logic to perform your name retrieval from the SQL Server database based on the passed in string parameter: "prefix"
     return "John,Joe,Joseph";
}

jQuery:

$(document).ready(function(){
    //assign the getNames() function each time the key is 
    //pressed on the txtNames input
    $('#txtName').keyup(function(){
        getNames();
    });
});

function getNames(){
    $.ajax({
        type: 'POST',
        url: 'page-to-post-to.ashx',
        data: {
            prefix: 'J'
        },
        success: function(data){
            //data will return : 'John,Joe,Joseph'
            //use your javascript logic to separate these names by the comma delimiter
            //and use them!
        }
    });
}

HTML:

<input id="txtName" />

This may not be 100% correct, but it should at least get you started down the right path.

Upvotes: 0

Related Questions