Hi Karl-Inge,
You might try a SQL Union statement for retrieving the List of
CustomerStatus. Something like this modification to the markup:
<asp:ObjectDataSource ID="ObjectDataSourceCustomerStatus"
runat="server" SelectMethod="GetCustomerStatus"
TypeName="CustomerDBClass">
<SelectParameters>
<asp:QueryStringParameter DefaultValue="1" Name="CustomerId"
QueryStringField="id" Type="Int32" />
</SelectParameters>
</asp:ObjectDataSource>
and the following modification to the business layer:
public List<CustomerStatus> GetCustomerStatus(int CustomerId)
{
System.Data.IDbConnection dbConnection = new
System.Data.SqlClient.SqlConnection(findConnectionString());
System.Data.IDbCommand dbCommand = new
System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = "SELECT status, text from (select status,
text from customerStatus UNION (Select Status, status as text from Customer
where id=@customerid) as B Group by status, text";
dbCommand.Connection = dbConnection;
System.Data.IDbDataAdapter dataAdapeter = new
System.Data.SqlClient.SqlDataAdapter();
dataAdapeter.SelectCommand = dbCommand;
System.Data.DataSet ds = new System.Data.DataSet();
dataAdapeter.Fill(ds);
List<CustomerStatus> customerStatusList = new List<CustomerStatus>();
foreach (DataRow row in ds.Tables[0].Rows)
{
customerStatusList.Add(new CustomerStatus((string)row["status"],
(string)row["text"]));
}
return customerStatusList;
}
--
HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
:
Hi Phillip
Here is an example that shows the problem.
When I retrieve a Customer that has a Customer.Status that exists in table
CustomerStatus everything works fin (Customer.ID = 1 or 2), but when a
retrieve a Customer (Customer.id = 3) with Customer.Status than not exist in
table CustomerStatus a got this error:
'DropDownList1' has a SelectedValue which is invalid because it does not
exist in the list of items.
Parameter name: value
You can say that this is an inconsistent database. Yes, but that is not the
question. I want to find a solution that handles this situation. In this
example I want to set customer.status to a valid value when the database is
inconsistent.
Here is the code:
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="
http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FormView ID="FormView1" runat="server"
DataSourceID="ObjectDataSourceCustomer"
DefaultMode="Edit">
<EditItemTemplate>
Status:
<asp
ropDownList ID="DropDownList1" runat="server"
DataSourceID="ObjectDataSourceCustomerStatus"
DataTextField="Text" DataValueField="Status"
SelectedValue='<%# Bind("Status") %>'>
</asp
ropDownList>
<asp:ObjectDataSource ID="ObjectDataSourceCustomerStatus"
runat="server" SelectMethod="GetCustomerStatus"
TypeName="CustomerDBClass"></asp:ObjectDataSource>
<br />
Id:
<asp:TextBox ID="IdTextBox" runat="server" Text='<%#
Bind("Id") %>'></asp:TextBox><br />
Name:
<asp:TextBox ID="NameTextBox" runat="server" Text='<%#
Bind("Name") %>'></asp:TextBox><br />
<asp:LinkButton ID="UpdateButton" runat="server"
CausesValidation="True" CommandName="Update"
Text="Update"></asp:LinkButton>
<asp:LinkButton ID="UpdateCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel"
Text="Cancel"></asp:LinkButton>
</EditItemTemplate>
<InsertItemTemplate>
Status:
<asp:TextBox ID="StatusTextBox" runat="server" Text='<%#
Bind("Status") %>'>
</asp:TextBox><br />
Id:
<asp:TextBox ID="IdTextBox" runat="server" Text='<%#
Bind("Id") %>'>
</asp:TextBox><br />
Name:
<asp:TextBox ID="NameTextBox" runat="server" Text='<%#
Bind("Name") %>'>
</asp:TextBox><br />
<asp:LinkButton ID="InsertButton" runat="server"
CausesValidation="True" CommandName="Insert"
Text="Insert">
</asp:LinkButton>
<asp:LinkButton ID="InsertCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</InsertItemTemplate>
<ItemTemplate>
Status:
<asp:Label ID="StatusLabel" runat="server" Text='<%#
Bind("Status") %>'></asp:Label><br />
Id:
<asp:Label ID="IdLabel" runat="server" Text='<%# Bind("Id")
%>'></asp:Label><br />
Name:
<asp:Label ID="NameLabel" runat="server" Text='<%#
Bind("Name") %>'></asp:Label><br />
</ItemTemplate>
</asp:FormView>
<asp:ObjectDataSource ID="ObjectDataSourceCustomer" runat="server"
SelectMethod="GetCustomer"
TypeName="CustomerDBClass">
<SelectParameters>
<asp:QueryStringParameter DefaultValue="1" Name="CustomerId"
QueryStringField="id"
Type="Int32" />
</SelectParameters>
</asp:ObjectDataSource>
</div>
</form>
</body>
</html>
Customer.cs
using System;
public class Customer
{
public Customer()
{
}
public Customer(int id, string name, string status)
{
_id = id;
_name = name;
_status = status;
}
private int _id;
private string _name;
private string _status;
public int Id
{
get { return _id; }
set { _id = value; }
}
public string Name
{
get { return _name; }
set { _name = value; }
}
public string Status
{
get { return _status; }
set { _status = value; }
}
}
CustomerStatus.cs
using System;
public class CustomerStatus
{
public CustomerStatus()
{
}
public CustomerStatus(string status, string Text)
{
_status = status;
_text = Text;
}
private string _status;
private string _text;
public string Status
{
get { return _status; }
set { _status = value; }
}
public string Text
{
get { return _text; }
set { _text = value; }
}
}
CustomerDBClass.cs
using System.Web;
using System.Data;
using System.Collections.Generic;
using System.Configuration;
public class CustomerDBClass
{
public CustomerDBClass()
{
}
public List<Customer> GetCustomer(int CustomerId)
{
System.Data.IDbConnection dbConnection = new
System.Data.SqlClient.SqlConnection(findConnectionString());
System.Data.IDbCommand dbCommand = new
System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = "select id, name, status from customer where
id = @Customerid";
dbCommand.Connection = dbConnection;
System.Data.IDataParameter dbParameter_id = new
System.Data.SqlClient.SqlParameter();
dbParameter_id.ParameterName = "@Customerid";
dbParameter_id.Value = CustomerId;
dbParameter_id.DbType = System.Data.DbType.Int16;
dbCommand.Parameters.Add(dbParameter_id);
System.Data.IDbDataAdapter dataAdapeter = new
System.Data.SqlClient.SqlDataAdapter();
dataAdapeter.SelectCommand = dbCommand;
System.Data.DataSet ds = new System.Data.DataSet();
dataAdapeter.Fill(ds);
List<Customer> customerList = new List<Customer>();
foreach (DataRow row in ds.Tables[0].Rows)
{
customerList.Add(new Customer((int)row["id"],
(string)row["name"], (string)row["status"]));
}
return customerList;
