Ajax AutoComplete subquery

[EDN Admin]

I am using ASP.Net Ajax Control Toolkit to populate 3 textbox controls.
When any of these controls are used and a postback is performed ... the
remaining 2 controls still have yet to be filtered or used. I want the
remaining 2 source data to be a subset of the original data based off
the control already used.

3 controls include: client name,
employee, client number. If there are 100 total records and a user
chooses an employee name when the postback is done I want the available
choices of "client name" and "client number" to only show relevant /
related content. (i.e. only the data where the employee exists).

I
have created a BindData() method where I bind the datagrid based on
values from the Ajax AutoComplete textboxes. I use a series of if-else
if statements to determine which condition is true. So, following my
example above, if a user selects an employee then in this BindData()
method I populate that value to a label on the page (i.e.
lblemployee.text ). I then use that lblemployee.text value to pass into
my NEW page method.

Now on page_load() I create a series of if
statements checking to see if any label controls contain content. So if
the lblemployee.text != "" then I want to programmatically call a NEW
page method to populate the 2 remaining, un-used controls instead of
using the ORIGINAL page method which pulls all values for the specific
field.

I hope I havent confused or lost anyone ... so here is my coderotected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
if (lblClientName.Text != "")
{
string myclname = lblClientName.Text;
txtClientName.Text = myclname;
}
if (lblAtty.Text != "")
{
string myAtty = lblAtty.Text;
txtBillingAtty.Text = myAtty;
string prefixText = myAtty;
int count = 1;
FilterClientNumber(prefixText, count);
}
if (lblClientNum.Text != "")
{
string myclnum = lblClientNum.Text;
txtClientNum.Text = myclnum;
}

// populate grid
ClientFilterDataContext cfGrid = new ClientFilterDataContext();
var queryGridCount = (from abc in cfGrid.myTable
select abc).Count();
lblRowCount.Text = queryGridCount.ToString(CultureInfo.InvariantCulture);
lblRowCount.DataBind();

var queryGrid = from b in cfGrid.myTable
select b;
GridView1.DataSource = queryGrid;
GridView1.DataBind();

}
}

[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
public static List<string> FilterClientNumber(string prefixText, int count)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager
.ConnectionStrings["myConnectionString"].ConnectionString;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select ClientNum from myTable where " +
"employee like @SearchText + % order by ClientNum";
cmd.Parameters.AddWithValue("@SearchText", prefixText);
cmd.Connection = conn;
conn.Open();
List<string> customers = new List<string>();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(sdr["ClientNum"].ToString());
}
}
conn.Close();

return customers;
}
}
}So what this page method is intending to do is since the employee has
already been chosen when the postback occurs I need to fire off this
page method with a new query using the employee as an additional filter
so that my new result set of "client numbers" is only relating to this
employee.

View the full article