coordinating items in dropdown listbox control with value in a textbox

[Ted]

Here is a stored procedure I created in MySQL:

CREATE PROCEDURE `sp_find_food`(
IN search_string varchar(255)
)
BEGIN
DECLARE ss VARCHAR(257);
SET ss = CONCAT('%',search_string,'%');
SELECT NDB_No,Long_Desc FROM food_des WHERE Long_Desc LIKE ss;
END

If I execute this within MySQL's Query Browser, passing a suitable
argument such as 'Butter', it works fine showing 151 unique records.

Obviously, I want to allow a user to provide a string representing
part of a food name and populate the dropdownlistbox with the items
returned.

Here is the definition of my datasource:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:sr19ConnectionString %>"
ProviderName="<%$
ConnectionStrings:sr19ConnectionString.ProviderNam e %>"
SelectCommand="CALL sp_find_food('@ss')">
<SelectParameters>
<asp:ControlParameter
ControlID="search_string" Name="@ss" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>

Here is the textbox:

<asp:TextBox ID="search_string"
runat="server" AutoPostBack="True"></asp:TextBox></td>
And here is the dropdownlistbox:

<asp:DropDownList ID="DropDownList1"
runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="Long_Desc"
DataValueField="NDB_No" Width="100%">
</asp:DropDownList></td>

I used a pattern like that which I used to determine the items in one
dropdownlistbox from the selected item in another. That worked fine,
because, I think, the AutoPostBack-true setting resulted in the
dependant dropdownlistbox to be recomputed each time a new item is
selected. I assumed there is an analogous event that happens whever a
new value is entered into the textbox.

Unfortunately, my dropdown listbox connected to the textbox is NEVER
populated. Why? How do I get this to work?

Thanks

Ted

[=?Utf-8?B?TWlsb3N6IFNrYWxlY2tpIFtNQ0FEXQ==?=]

Howdy Ted,

You code seems to be OK. I do not have MySQL server installed so i could not
test it properly, however i replaced sqldatasource with objectdatasource and
everything works like a charm, when text changes, page is automatically
posted back to server, and drop down list is populated with new values.
Therefore my quess would be sqldatasource is not retrieving any results at
all. To find out if this is the case, perform select manually to test for any
result:

System.Collections.IEnumerable results = SqlDataSource1.Select();

Hope this helps
--
Milosz
"Ted" wrote:

Here is a stored procedure I created in MySQL:

CREATE PROCEDURE `sp_find_food`(
IN search_string varchar(255)
)
BEGIN
DECLARE ss VARCHAR(257);
SET ss = CONCAT('%',search_string,'%');
SELECT NDB_No,Long_Desc FROM food_des WHERE Long_Desc LIKE ss;
END

If I execute this within MySQL's Query Browser, passing a suitable
argument such as 'Butter', it works fine showing 151 unique records.

Obviously, I want to allow a user to provide a string representing
part of a food name and populate the dropdownlistbox with the items
returned.

Here is the definition of my datasource:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:sr19ConnectionString %>"
ProviderName="<%$
ConnectionStrings:sr19ConnectionString.ProviderNam e %>"
SelectCommand="CALL sp_find_food('@ss')">
<SelectParameters>
<asp:ControlParameter
ControlID="search_string" Name="@ss" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>

Here is the textbox:

<asp:TextBox ID="search_string"
runat="server" AutoPostBack="True"></asp:TextBox></td>
And here is the dropdownlistbox:

<asp:DropDownList ID="DropDownList1"
runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="Long_Desc"
DataValueField="NDB_No" Width="100%">
</asp:DropDownList></td>

I used a pattern like that which I used to determine the items in one
dropdownlistbox from the selected item in another. That worked fine,
because, I think, the AutoPostBack-true setting resulted in the
dependant dropdownlistbox to be recomputed each time a new item is
selected. I assumed there is an analogous event that happens whever a
new value is entered into the textbox.

Unfortunately, my dropdown listbox connected to the textbox is NEVER
populated. Why? How do I get this to work?

Thanks

Ted