Bound Dropdown List - doens't allow for user input?

[blouie]

Is there a way that a bound ddl control can:

1. display the bound data, even if it does not match a selection in
the DDL?
2. allow the user to enter something other than the look up values?

I have a bound dropdown list that retrieves it's selections from a
look-up table. The DDL is bound to a database table and unless the
value in the (text) field matches one of the look-up values, the page
craps out.

Thank you, Luis.

[Alexey Smirnov]

On Mar 16, 2:52 pm, "blouie" <lrblo...@gmail .comwrote:

Is there a way that a bound ddl control can:

1. display the bound data, even if it does not match a selection in
the DDL?

Depends on your need, if it's just a search by the string

Your query to the table can be following:

string sql = "...from table where col1 LIKE '%" + ddl.SelectedVal ue +
"%'";

2. allow the user to enter something other than the look up values?

You will need another TextBox Control

[blouie]

Thanks Alexey,

For #1, I mean if there is a ddl property that allows the ddl to show
an item that is not a member of the look-up table.

For example, to show "NULL" values, I use the property
AppendDateBound Items="True" along with <asp:ListItem Value=""></
asp:ListItemand it lets me have NULLS in the database w/o craping
out the page because the look-up table doesn't match the "NULL"
values.

I need the same type of solution but for items in the db that there is
no cooresponding item in the look-up table in my DataSourceID for the
ddl.
<asp:DropDownLi st AppendDataBound Items="true" SelectedValue=' <
%#Bind("II_Insu ranceType") %>' ID="ddlInsuranc eType_ET" runat="server"
DataSourceID="s dsInsuranceType " DataTextField=" InsuranceType"
DataValueField= "InsuranceT ype" ToolTip="Select Insurance Type.">
<asp:ListItem Value=""></asp:ListItem>
</asp:DropDownLis t>

On Mar 16, 10:01 am, "Alexey Smirnov" <alexey.smir... @gmail.com>
wrote:

On Mar 16, 2:52 pm, "blouie" <lrblo...@gmail .comwrote:

Is there a way that a bound ddl control can:

1. display the bound data, even if it does not match a selection in
the DDL?

Depends on your need, if it's just a search by the string

Your query to the table can be following:

string sql = "...from table where col1 LIKE '%" + ddl.SelectedVal ue +
"%'";

2. allow the user to enter something other than the look up values?

You will need another TextBox Control

[Alexey Smirnov]

On Mar 16, 4:42 pm, "blouie" <lrblo...@gmail .comwrote:

I need the same type of solution but for items in the db that there is
no cooresponding item in the look-up table in my DataSourceID for the
ddl.

Can you give us an example of your data in the InsuranceType field of
your table and a final list (lookup table + ...) you would like to get?

[blouie]

Thanks Alexey,

I hope this is what you are looking for:

The SQL Data Source populating the "ddlInsuranceTy pe_ET" is called
"sdsInsuranceTy pe"
It contains the following look up records:
- InsTypeID, InsuranceType (FIELD NAMES)
- 1, "Medicare"
- 2, "MedicAID"

Therefore, the ddl shows Medicare and MedicAID

Now my customer record contins:
- CustID, CustName, II_InsuranceTyp e (FIELD NAMES)
- 1, "Luis", "IBC"
- 2, "Alexey", "MedicAID"

Now, when look the record for Customer "Alexey", the "Selected Value"
of the ddl show "MedicAID". HOWEVER, when I look at the Customer
"Luis" record, the page craps out because "IBC" is not a valid item in
the ddl.
<asp:DropDownLi st AppendDataBound Items="true" SelectedValue=' <
%#Bind("II_Insu ranceType") %>' ID="ddlInsuranc eType_ET"
runat="server"
DataSourceID="s dsInsuranceType " DataTextField=" InsuranceType"
DataValueField= "InsuranceT ype" ToolTip="Select Insurance Type.">
<asp:ListItem Value=""></asp:ListItem>
</asp:DropDownLis t>

Hope that helps,

Luis.
On Mar 16, 11:58 am, "Alexey Smirnov" <alexey.smir... @gmail.com>
wrote:

On Mar 16, 4:42 pm, "blouie" <lrblo...@gmail .comwrote:

I need the same type of solution but for items in the db that there is
no cooresponding item in the look-up table in my DataSourceID for the
ddl.

Can you give us an example of your data in the InsuranceType field of
your table and a final list (lookup table + ...) you would like to get?

[Alexey Smirnov]

On Mar 16, 6:27 pm, "blouie" <lrblo...@gmail .comwrote:

Thanks Alexey,

I hope this is what you are looking for:

The SQL Data Source populating the "ddlInsuranceTy pe_ET" is called
"sdsInsuranceTy pe"
It contains the following look up records:
- InsTypeID, InsuranceType (FIELD NAMES)
- 1, "Medicare"
- 2, "MedicAID"

Therefore, the ddl shows Medicare and MedicAID

Now my customer record contins:
- CustID, CustName, II_InsuranceTyp e (FIELD NAMES)
- 1, "Luis", "IBC"
- 2, "Alexey", "MedicAID"

Now, when look the record for Customer "Alexey", the "Selected Value"
of the ddl show "MedicAID". HOWEVER, when I look at the Customer
"Luis" record, the page craps out because "IBC" is not a valid item in
the ddl.

<asp:DropDownLi st AppendDataBound Items="true" SelectedValue=' <
%#Bind("II_Insu ranceType") %>' ID="ddlInsuranc eType_ET"
runat="server"
DataSourceID="s dsInsuranceType " DataTextField=" InsuranceType"
DataValueField= "InsuranceT ype" ToolTip="Select Insurance Type.">
<asp:ListItem Value=""></asp:ListItem>
</asp:DropDownLis t>

Hope that helps,

Luis.

On Mar 16, 11:58 am, "Alexey Smirnov" <alexey.smir... @gmail.com>
wrote:

On Mar 16, 4:42 pm, "blouie" <lrblo...@gmail .comwrote:

I need the same type of solution but for items in the db that there is
no cooresponding item in the look-up table in my DataSourceID for the
ddl.

Can you give us an example of your data in the InsuranceType field of
your table and a final list (lookup table + ...) you would like to get?- Hide quoted text -

- Show quoted text -

Okay, I got it! The only question is what do you want to do with that
InsuranceType, which does not exist. The

ddlInsuranceTyp e_ET contains a key (which we don't have) and its
value.

1) SQL way.

Use a stored procedure to get the data.
I suppose, the sdsInsuranceTyp e has something like this:

"SELECT InsTypeID, InsuranceType FROM ... WHERE
InsuranceType=@ InsuranceType"

If you have a stored procedure (or maybe you have one already), you
can return @InsuranceType if it doesn't exist.
Here's the code you can use

CREATE PROCEDURE [dbo].[GetInsuranceTyp e]
(
@InsuranceType varchar(100)
)
AS
BEGIN

IF EXISTS(SELECT InsTypeID, InsuranceType FROM ... WHERE
InsuranceType=@ InsuranceType)
SELECT InsTypeID, InsuranceType FROM ... WHERE
InsuranceType=@ InsuranceType
ELSE
SELECT -1, @InsuranceType

END
GO

You can call this procedure from your code as

"GetInsuranceTy pe @InsuranceType"

In case of Luis/IBC it returns -1, IBC.

Here you have to think regarding the "-1" as I don't know what you
wanted to do with that key - it doesn't exist in the

database table.

2) Code-behind way.

string lookFor = "IBC";
DataSet ds = ... get a data set ... WHERE InsuranceType=' " + lookFor
+"'";

if (ds.Tables[0].Rows.Count 0)
{
ddlInsuranceTyp e_ET.DataSource = ds;
ddlInsuranceTyp e_ET.DataBind() ;
} else { // for Luis/IBC
ddlInsuranceTyp e_ET.Items.Clea r();
ddlInsuranceTyp e_ET.Items.Add( new ListItem(lookFo r, "-1"));
}

or something like this

[blouie]

Alexey,

Thank you for such a comprehensive response.

I'm going to apply your stored procedure logic to a few of my drop-
downs. My application is full of drop downs and it would be nuts to
do it for all of them. This should be something that is
straightforward .

The reason it's such a big issue for me is that my new application is
a merger of three completely separate but related systems. Where
related data was stored in different ways. What I didn't want to do
is populate my drop downs with "similar" selections when they should
all be consolidated. At the same time, there are so many drop downs
that to go through the data and try to consolidate in the main data
itself (not look up data) would be a monumental task that the app
users are certainly not willing to do on their own. We have data
ownership issues! Therefore, the burden falls on the little ol'
developer!!!

Thanks again!
On Mar 16, 2:24 pm, "Alexey Smirnov" <alexey.smir... @gmail.comwrote :

On Mar 16, 6:27 pm, "blouie" <lrblo...@gmail .comwrote:

Thanks Alexey,

I hope this is what you are looking for:

The SQL Data Source populating the "ddlInsuranceTy pe_ET" is called
"sdsInsuranceTy pe"
It contains the following look up records:
- InsTypeID, InsuranceType (FIELD NAMES)
- 1, "Medicare"
- 2, "MedicAID"

Therefore, the ddl shows Medicare and MedicAID

Now my customer record contins:
- CustID, CustName, II_InsuranceTyp e (FIELD NAMES)
- 1, "Luis", "IBC"
- 2, "Alexey", "MedicAID"

Now, when look the record for Customer "Alexey", the "Selected Value"
of the ddl show "MedicAID". HOWEVER, when I look at the Customer
"Luis" record, the page craps out because "IBC" is not a valid item in
the ddl.

<asp:DropDownLi st AppendDataBound Items="true" SelectedValue=' <
%#Bind("II_Insu ranceType") %>' ID="ddlInsuranc eType_ET"
runat="server"
DataSourceID="s dsInsuranceType " DataTextField=" InsuranceType"
DataValueField= "InsuranceT ype" ToolTip="Select Insurance Type.">
<asp:ListItem Value=""></asp:ListItem>
</asp:DropDownLis t>

Hope that helps,

Luis.

On Mar 16, 11:58 am, "Alexey Smirnov" <alexey.smir... @gmail.com>
wrote:

On Mar 16, 4:42 pm, "blouie" <lrblo...@gmail .comwrote:

I need the same type of solution but for items in the db that there is
no cooresponding item in the look-up table in my DataSourceID for the
ddl.

Can you give us an example of your data in the InsuranceType field of
your table and a final list (lookup table + ...) you would like to get?- Hide quoted text -

- Show quoted text -

Okay, I got it! The only question is what do you want to do with that
InsuranceType, which does not exist. The

ddlInsuranceTyp e_ET contains a key (which we don't have) and its
value.

1) SQL way.

Use a stored procedure to get the data.
I suppose, the sdsInsuranceTyp e has something like this:

"SELECT InsTypeID, InsuranceType FROM ... WHERE
InsuranceType=@ InsuranceType"

If you have a stored procedure (or maybe you have one already), you
can return @InsuranceType if it doesn't exist.
Here's the code you can use

CREATE PROCEDURE [dbo].[GetInsuranceTyp e]
(
@InsuranceType varchar(100)
)
AS
BEGIN

IF EXISTS(SELECT InsTypeID, InsuranceType FROM ... WHERE
InsuranceType=@ InsuranceType)
SELECT InsTypeID, InsuranceType FROM ... WHERE
InsuranceType=@ InsuranceType
ELSE
SELECT -1, @InsuranceType

END
GO

You can call this procedure from your code as

"GetInsuranceTy pe @InsuranceType"

In case of Luis/IBC it returns -1, IBC.

Here you have to think regarding the "-1" as I don't know what you
wanted to do with that key - it doesn't exist in the

database table.

2) Code-behind way.

string lookFor = "IBC";
DataSet ds = ... get a data set ... WHERE InsuranceType=' " + lookFor
+"'";

if (ds.Tables[0].Rows.Count 0)
{
ddlInsuranceTyp e_ET.DataSource = ds;
ddlInsuranceTyp e_ET.DataBind() ;} else { // for Luis/IBC

ddlInsuranceTyp e_ET.Items.Clea r();
ddlInsuranceTyp e_ET.Items.Add( new ListItem(lookFo r, "-1"));

}

or something like this- Hide quoted text -

- Show quoted text -