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('%',sear ch_string,'%');
SELECT NDB_No,Long_Des c 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:SqlDataSou rce ID="SqlDataSour ce1" runat="server"
ConnectionStrin g="<%$ ConnectionStrin gs:sr19Connecti onString %>"
ProviderName="< %$
ConnectionStrin gs:sr19Connecti onString.Provid erName %>"
SelectCommand=" CALL sp_find_food('@ ss')">
<SelectParamete rs>
<asp:ControlPar ameter
ControlID="sear ch_string" Name="@ss" PropertyName="T ext" />
</SelectParameter s>
</asp:SqlDataSour ce>

Here is the textbox:

<asp:TextBox ID="search_stri ng"
runat="server" AutoPostBack="T rue"></asp:TextBox></td>
And here is the dropdownlistbox :

<asp:DropDownLi st ID="DropDownLis t1"
runat="server" AutoPostBack="T rue" DataSourceID="S qlDataSource1"
DataTextField=" Long_Desc"
DataValueField= "NDB_No" Width="100%">
</asp:DropDownLis t></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 objectdatasourc e 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.Collecti ons.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('%',sear ch_string,'%');
SELECT NDB_No,Long_Des c 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:SqlDataSou rce ID="SqlDataSour ce1" runat="server"
ConnectionStrin g="<%$ ConnectionStrin gs:sr19Connecti onString %>"
ProviderName="< %$
ConnectionStrin gs:sr19Connecti onString.Provid erName %>"
SelectCommand=" CALL sp_find_food('@ ss')">
<SelectParamete rs>
<asp:ControlPar ameter
ControlID="sear ch_string" Name="@ss" PropertyName="T ext" />
</SelectParameter s>
</asp:SqlDataSour ce>

Here is the textbox:

<asp:TextBox ID="search_stri ng"
runat="server" AutoPostBack="T rue"></asp:TextBox></td>
And here is the dropdownlistbox :

<asp:DropDownLi st ID="DropDownLis t1"
runat="server" AutoPostBack="T rue" DataSourceID="S qlDataSource1"
DataTextField=" Long_Desc"
DataValueField= "NDB_No" Width="100%">
</asp:DropDownLis t></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