How can I do this

  • Thread starter Thread starter Tony Johansson
  • Start date Start date
T

Tony Johansson

How can I do this

This example is based on the northwind database.
I assume my problem has very little do do with web programming but is of a
general nature.
Below is listed both markup and relevant code behind.

In this example I only use database table Territories and list the contents
in this table in a gridView using templatefield.
The fields in this table Territories is TerritoryID, TerritoryDescription
and RegionID
This field RegionID is a foreign key in table Territories.

Here is what I want to accomplish.
I want to be able to list the RegionDescription in the database table region
in the GridView for corresponding RegionID but I still want to be able to
update database table Territories.
I can join the database tables Territories and Region to get
RegionDescription but if I do so I can't update Territories becuse
RegionDescription does not exist in Territories.

I can use a separate GridView do display RegionID and RegionDescription:

So as a summary be able to display RegionDescription in the GridView but
still be able to update Territories.
This kind of problem is very common so somebody must have a good solution to
how I solve this.

private void BindGridData()
{
using (SqlConnection conn = new
SqlConnection(WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
{
conn.Open();
using (SqlCommand comm = new SqlCommand("select TerritoryID,
TerritoryDescription, RegionID from Territories", conn))
{
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
}

%@ 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></title>
</head>
<body>
<form id="form1" runat="server">

<asp:GridView ID="GridView1" runat="server" GridLines="None"
AutoGenerateColumns="false"
AlternatingRowStyle-BackColor="#EEEEEE" EditRowStyle-BorderColor="Red"
onrowcancelingedit="GridView1_RowCancelling"
onrowdeleting="GridView1_RowDeleting"
onrowediting="GridView1_RowEditing"
onrowupdating="GridView1_RowUpdating" DataKeyNames="TerritoryID">
<Columns>
<asp:TemplateField Visible="false" HeaderText="TerritoryID">
<ItemTemplate>
<asp:Label runat="server" ID="lblTerritoryID"
Text='<%#Eval("TerritoryID")%>' />
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="TerritoryDescription">
<ItemTemplate>
<asp:Label ID="lblTerritoryDescription" Text = '<%#
Eval("TerritoryDescription") %>' runat="server" />
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="RegionID">
<ItemTemplate>
<asp:Label ID="lblRegionID" Text = '<%# Eval("RegionID") %>'
runat="server" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</form>
</body>
</html>

//Tony
 
I found a solution to this problem.

//Tony

Tony Johansson said:
How can I do this

This example is based on the northwind database.
I assume my problem has very little do do with web programming but is of a
general nature.
Below is listed both markup and relevant code behind.

In this example I only use database table Territories and list the
contents in this table in a gridView using templatefield.
The fields in this table Territories is TerritoryID, TerritoryDescription
and RegionID
This field RegionID is a foreign key in table Territories.

Here is what I want to accomplish.
I want to be able to list the RegionDescription in the database table
region in the GridView for corresponding RegionID but I still want to be
able to update database table Territories.
I can join the database tables Territories and Region to get
RegionDescription but if I do so I can't update Territories becuse
RegionDescription does not exist in Territories.

I can use a separate GridView do display RegionID and RegionDescription:

So as a summary be able to display RegionDescription in the GridView but
still be able to update Territories.
This kind of problem is very common so somebody must have a good solution
to how I solve this.

private void BindGridData()
{
using (SqlConnection conn = new
SqlConnection(WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
{
conn.Open();
using (SqlCommand comm = new SqlCommand("select TerritoryID,
TerritoryDescription, RegionID from Territories", conn))
{
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
}

%@ 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></title>
</head>
<body>
<form id="form1" runat="server">

<asp:GridView ID="GridView1" runat="server" GridLines="None"
AutoGenerateColumns="false"
AlternatingRowStyle-BackColor="#EEEEEE"
EditRowStyle-BorderColor="Red"
onrowcancelingedit="GridView1_RowCancelling"
onrowdeleting="GridView1_RowDeleting"
onrowediting="GridView1_RowEditing"
onrowupdating="GridView1_RowUpdating" DataKeyNames="TerritoryID">
<Columns>
<asp:TemplateField Visible="false" HeaderText="TerritoryID">
<ItemTemplate>
<asp:Label runat="server" ID="lblTerritoryID"
Text='<%#Eval("TerritoryID")%>' />
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="TerritoryDescription">
<ItemTemplate>
<asp:Label ID="lblTerritoryDescription" Text = '<%#
Eval("TerritoryDescription") %>' runat="server" />
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="RegionID">
<ItemTemplate>
<asp:Label ID="lblRegionID" Text = '<%# Eval("RegionID") %>'
runat="server" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</form>
</body>
</html>

//Tony
Click to expand...
 
Tony said:
I want to be able to list the RegionDescription in the database table
region in the GridView for corresponding RegionID but I still want to be
able to update database table Territories.
I can join the database tables Territories and Region to get
RegionDescription but if I do so I can't update Territories becuse
RegionDescription does not exist in Territories.
Click to expand...


You have decided to use datasets rather than custom objects (and DTOs,
etc.) - and that's okay.

There are some pros and cons to both approaches. Let's ignore that for
the moment.

In your case, many would choose to use a view to display the data, eg:

SELECT t.TerritoryID, t.TerritoryDescription, t.RegionID,
r.RegionDescription
FROM Region AS r INNER JOIN
Territories AS t ON r.RegionID = t.RegionID
ORDER BY t.TerritoryDescription

They may choose to hide the RegionID column (eg, make it zero width) in
the grid.

To edit, I'm guessing you select a row in your grid, then click Edit?

What happens next? Do you load a new Edit form? Or, do you display a
panel containing the data fields to be edited? Are you going to use a
Postback, or JavaScript (Ajax) to do the above?

Who cares, let's move on...

What happens when you click Save? Why can't you detect the fields that
have been edited and, knowing what was on the Edit form, make a decision
about how you're going to persist the changes?

What's the problem?
 
Back
Top