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.

 

[Guest]

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.SelectedValue +
"%'";

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
AppendDateBoundItems="True" along with <asp:ListItem Value=""></
asp:ListItem> and 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.


<aspropDownList AppendDataBoundItems="true" SelectedValue='<
%#Bind("II_InsuranceType") %>' ID="ddlInsuranceType_ET" runat="server"
DataSourceID="sdsInsuranceType" DataTextField="InsuranceType"
DataValueField="InsuranceType" ToolTip="Select Insurance Type.">
<asp:ListItem Value=""></asp:ListItem>
</aspropDownList>

 

[Guest]

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 "ddlInsuranceType_ET" is called
"sdsInsuranceType"
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_InsuranceType (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.


<aspropDownList AppendDataBoundItems="true" SelectedValue='<
%#Bind("II_InsuranceType") %>' ID="ddlInsuranceType_ET"
runat="server"
DataSourceID="sdsInsuranceType" DataTextField="InsuranceType"
DataValueField="InsuranceType" ToolTip="Select Insurance Type.">
<asp:ListItem Value=""></asp:ListItem>
</aspropDownList>

Hope that helps,

Luis.

 

[Guest]

Thanks Alexey,

I hope this is what you are looking for:

The SQL Data Source populating the "ddlInsuranceType_ET" is called
"sdsInsuranceType"
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_InsuranceType (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.

<aspropDownList AppendDataBoundItems="true" SelectedValue='<
%#Bind("II_InsuranceType") %>' ID="ddlInsuranceType_ET"
runat="server"
DataSourceID="sdsInsuranceType" DataTextField="InsuranceType"
DataValueField="InsuranceType" ToolTip="Select Insurance Type.">
<asp:ListItem Value=""></asp:ListItem>
</aspropDownList>

Hope that helps,

Luis.




- 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

ddlInsuranceType_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 sdsInsuranceType 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].[GetInsuranceType]
(
@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

"GetInsuranceType @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)
{
ddlInsuranceType_ET.DataSource = ds;
ddlInsuranceType_ET.DataBind();
} else { // for Luis/IBC
ddlInsuranceType_ET.Items.Clear();
ddlInsuranceType_ET.Items.Add(new ListItem(lookFor, "-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!

Thanks Alexey,

I hope this is what you are looking for:

The SQL Data Source populating the "ddlInsuranceType_ET" is called
"sdsInsuranceType"
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_InsuranceType (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.

<aspropDownList AppendDataBoundItems="true" SelectedValue='<
%#Bind("II_InsuranceType") %>' ID="ddlInsuranceType_ET"
runat="server"
DataSourceID="sdsInsuranceType" DataTextField="InsuranceType"
DataValueField="InsuranceType" ToolTip="Select Insurance Type.">
<asp:ListItem Value=""></asp:ListItem>
</aspropDownList>

Hope that helps,

On Mar 16, 11:58 am, "Anon User" <[email protected]>
wrote:

- 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

ddlInsuranceType_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 sdsInsuranceType 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].[GetInsuranceType]
(
@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

"GetInsuranceType @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)
{
ddlInsuranceType_ET.DataSource = ds;
ddlInsuranceType_ET.DataBind();} else { // for Luis/IBC

ddlInsuranceType_ET.Items.Clear();
ddlInsuranceType_ET.Items.Add(new ListItem(lookFor, "-1"));

}

or something like this- Hide quoted text -

- Show quoted text -