October 21, 2009 at 1:47 pm
Hi
I have 3 tables as follows
USE [SSWorkplan]
GO
CREATE TABLE [dbo].[Activities](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Activity] [varchar](20) NOT NULL,
CONSTRAINT [PK_Activities] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
GO
USE [SSWorkplan]
GO
CREATE TABLE [dbo].[Employees](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
GO
USE [SSWorkplan]
GO
CREATE TABLE [dbo].[Data](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [int] NOT NULL,
[Date] [datetime] NOT NULL,
[Slot1] [int] NOT NULL,
[Slot2] [int] NOT NULL,
[Slot3] [int] NOT NULL,
[Slot4] [int] NOT NULL,
CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
GO
I added the following data to tables
INSERT INTO [SSWorkplan].[dbo].[Activities]
([Activity])
VALUES
('Sick, Duty', 'Task, Training', '1st Line', 'Meeting', 'Leave',
'Ser Reqs', 'Q Check', '2nd Line', 'Other', 'OOO')
INSERT INTO [SSWorkplan].[dbo].[Employees]
([Name])
VALUES
('Andrew Woodward', 'Ash Bassett', 'Brian Horan', 'Dalbir Thandi',
'Ismail Patel', 'John Hickenbotham', 'Joby Vaughan', 'Kieron Meadows')
INSERT INTO [SSWorkplan].[dbo].[Data]
([Name]
,[Date]
,[Slot1]
,[Slot2]
,[Slot3]
,[Slot4])
VALUES
(1, '02/11/09', 5, 5, 5, 5,
2, '02/11/09', 9, 5, 3, 3,
3, '02/11/09', 10, 10, 10, 10,
4, '02/11/09', 7, 7, 7, 7,
5, '02/11/09', 2, 2, 5, 5,
6, '02/11/09', 10, 10, 10, 10,
7, '02/11/09', 10, 10, 10, 10,
8, '02/11/09', 5, 5, 2, 2)
What I need to do is display the data in a gridview as follows:
Name, Slot1, Slot2, Slot3, Slot4
To give the following (example 1 row)
Ash Bassett Q Check 1st Line Task Task
The query I have so far is:
SELECT Employees.Name, Activities.Activity AS Slot1
FROM Data INNER JOIN
Employees ON Data.Name = Employees.ID INNER JOIN
Activities ON Data.Slot1 = Activities.ID
How do I get the remaining 3 columns to display
Hope I have explained ok and someone can help
Andy
October 21, 2009 at 3:08 pm
You're almost there, you just have to keep joining to the same table.
Something like this should work:
SELECT
Employees.Name,
Activities1.Activity AS Slot1,
Activities2.Activity AS Slot2,
Activities3.Activity AS Slot3,
Activities4.Activity AS Slot4
FROM
#Data Data
INNER JOIN
#Employees Employees ON Data.Name = Employees.ID
INNER JOIN
#Activities Activities1 ON Data.Slot1 = Activities1.ID
INNER JOIN
#Activities Activities2 ON Data.Slot2 = Activities2.ID
INNER JOIN
#Activities Activities3 ON Data.Slot3 = Activities3.ID
INNER JOIN
#Activities Activities4 ON Data.Slot4 = Activities4.ID
- Jeff
October 24, 2009 at 1:59 am
Thanks SSC Veteran that worked a treat.
I now have my grid view displaying the correct data. I renamed the column headers and
i've added conditional formatting on the cells and now want to add the update feature.
My update command is
UPDATE Data SET Slot1 = @Slot1, Slot2 = @Slot2, Slot3 = @Slot3, Slot4 = @Slot4, Comments = @Comments WHERE (ID = @ID)
This works fine in the query builder but when I add the update command to datasource in Visual Studio I get the following error:
Server Error in '/' Application.
--------------------------------------------------------------------------------
Incorrect syntax near '.30'.
This is my final code:
<%@ Page Language="C#" MasterPageFile="~/MasterPages/MasterPage.master" AutoEventWireup="true" CodeFile="WPAdmin.aspx.cs" Inherits="Management_WPAdmin" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="WorkplanMainContent" Runat="Server">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1" onrowdatabound="GridView1_RowDataBound"
CssClass="DayGrid">
<Columns>
<asp:CommandField ButtonType="Button" ShowEditButton="True" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="08.30-10.00" HeaderText="08.30-10.00"
SortExpression="08.30-10.00" />
<asp:BoundField DataField="10.00-13.00" HeaderText="10.00-13.00"
SortExpression="10.00-13.00" />
<asp:BoundField DataField="13.00-16.00" HeaderText="13.00-16.00"
SortExpression="13.00-16.00" />
<asp:BoundField DataField="16.00-17.00" HeaderText="16.00-17.00"
SortExpression="16.00-17.00" />
<asp:BoundField DataField="Comments" HeaderText="Comments"
SortExpression="Comments" />
</Columns>
</asp:GridView>
<asp:Calendar ID="Calendar1" runat="server" BackColor="White"
BorderColor="#3366CC" BorderWidth="1px" CellPadding="1" CssClass="calendar"
DayNameFormat="Shortest" Font-Names="Verdana" Font-Size="8pt"
ForeColor="#003399" Height="200px" Width="220px">
<SelectedDayStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<SelectorStyle BackColor="#99CCCC" ForeColor="#336666" />
<WeekendDayStyle BackColor="#CCCCFF" />
<TodayDayStyle BackColor="#99CCCC" ForeColor="White" />
<OtherMonthDayStyle ForeColor="#999999" />
<NextPrevStyle Font-Size="8pt" ForeColor="#CCCCFF" />
<DayHeaderStyle BackColor="#99CCCC" ForeColor="#336666" Height="1px" />
<TitleStyle BackColor="#003399" BorderColor="#3366CC" BorderWidth="1px"
Font-Bold="True" Font-Size="10pt" ForeColor="#CCCCFF" Height="25px" />
</asp:Calendar>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:SSWorkPlanConnectionString %>"
SelectCommand="SELECT Employees.Name, Activities1.Activity AS '08.30-10.00',
Activities2.Activity AS '10.00-13.00', Activities3.Activity AS '13.00-16.00',
Activities4.Activity AS '16.00-17.00',
Comments FROM Data INNER JOIN Employees ON Data.Name = Employees.ID
INNER JOIN Activities AS Activities1 ON Data.Slot1 = Activities1.ID INNER JOIN Activities AS Activities2 ON Data.Slot2 = Activities2.ID INNER JOIN Activities AS Activities3 ON Data.Slot3 = Activities3.ID INNER JOIN Activities AS Activities4 ON Data.Slot4 = Activities4.ID
Where date = @WPDate"
UpdateCommand="UPDATE Data SET Slot1 = @Slot1, Slot2 = @Slot2, Slot3 = @Slot3, Slot4 = @Slot4, Comments = @Comments WHERE (ID = @ID)">
<SelectParameters>
<asp:ControlParameter ControlID="Calendar1" Name="WPDate"
PropertyName="SelectedDate" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="Slot1" />
<asp:Parameter Name="Slot2" />
<asp:Parameter Name="Slot3" />
<asp:Parameter Name="Slot4" />
<asp:Parameter Name="Comments" />
<asp:Parameter Name="ID" />
</UpdateParameters>
</asp:SqlDataSource>
</asp:Content>
Code behind is:
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class Management_WPAdmin : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
if (e.Row.Cells[2].Text == "1st Line")
{
e.Row.Cells[2].CssClass = "Firstline";
}
if (e.Row.Cells[2].Text == "Q Check")
{
e.Row.Cells[2].CssClass = "Qcheck";
}
if (e.Row.Cells[2].Text == "2nd Line")
{
e.Row.Cells[2].CssClass = "Secondline";
}
if (e.Row.Cells[2].Text == "Duty")
{
e.Row.Cells[2].CssClass = "Duty";
}
if (e.Row.Cells[2].Text == "Leave")
{
e.Row.Cells[2].CssClass = "Leave";
}
if (e.Row.Cells[2].Text == "Other")
{
e.Row.Cells[2].CssClass = "Other";
}
if (e.Row.Cells[2].Text == "OOO")
{
e.Row.Cells[2].CssClass = "OOO";
}
if (e.Row.Cells[2].Text == "Serv Reqs")
{
e.Row.Cells[2].CssClass = "Serreqs";
}
if (e.Row.Cells[2].Text == "Task")
{
e.Row.Cells[2].CssClass = "Task";
}
if (e.Row.Cells[2].Text == "Sick")
{
e.Row.Cells[2].CssClass = "Sick";
}
if (e.Row.Cells[2].Text == "Meeting")
{
e.Row.Cells[2].CssClass = "Meeting";
}
if (e.Row.Cells[2].Text == "Training")
{
e.Row.Cells[2].CssClass = "Training";
}
if (e.Row.Cells[3].Text == "1st Line")
{
e.Row.Cells[3].CssClass = "Firstline";
}
if (e.Row.Cells[3].Text == "Q Check")
{
e.Row.Cells[3].CssClass = "Qcheck";
}
if (e.Row.Cells[3].Text == "2nd Line")
{
e.Row.Cells[3].CssClass = "Secondline";
}
if (e.Row.Cells[3].Text == "Duty")
{
e.Row.Cells[3].CssClass = "Duty";
}
if (e.Row.Cells[3].Text == "Leave")
{
e.Row.Cells[3].CssClass = "Leave";
}
if (e.Row.Cells[3].Text == "Other")
{
e.Row.Cells[3].CssClass = "Other";
}
if (e.Row.Cells[3].Text == "OOO")
{
e.Row.Cells[3].CssClass = "OOO";
}
if (e.Row.Cells[3].Text == "Serv Reqs")
{
e.Row.Cells[3].CssClass = "Serreqs";
}
if (e.Row.Cells[3].Text == "Task")
{
e.Row.Cells[3].CssClass = "Task";
}
if (e.Row.Cells[3].Text == "Sick")
{
e.Row.Cells[3].CssClass = "Sick";
}
if (e.Row.Cells[3].Text == "Meeting")
{
e.Row.Cells[3].CssClass = "Meeting";
}
if (e.Row.Cells[3].Text == "Training")
{
e.Row.Cells[3].CssClass = "Training";
}
if (e.Row.Cells[4].Text == "1st Line")
{
e.Row.Cells[4].CssClass = "Firstline";
}
if (e.Row.Cells[4].Text == "Q Check")
{
e.Row.Cells[4].CssClass = "Qcheck";
}
if (e.Row.Cells[4].Text == "2nd Line")
{
e.Row.Cells[4].CssClass = "Secondline";
}
if (e.Row.Cells[4].Text == "Duty")
{
e.Row.Cells[4].CssClass = "Duty";
}
if (e.Row.Cells[4].Text == "Leave")
{
e.Row.Cells[4].CssClass = "Leave";
}
if (e.Row.Cells[4].Text == "Other")
{
e.Row.Cells[4].CssClass = "Other";
}
if (e.Row.Cells[4].Text == "OOO")
{
e.Row.Cells[4].CssClass = "OOO";
}
if (e.Row.Cells[4].Text == "Serv Reqs")
{
e.Row.Cells[4].CssClass = "Serreqs";
}
if (e.Row.Cells[4].Text == "Task")
{
e.Row.Cells[4].CssClass = "Task";
}
if (e.Row.Cells[4].Text == "Sick")
{
e.Row.Cells[4].CssClass = "Sick";
}
if (e.Row.Cells[4].Text == "Meeting")
{
e.Row.Cells[4].CssClass = "Meeting";
}
if (e.Row.Cells[4].Text == "Training")
{
e.Row.Cells[4].CssClass = "Training";
}
if (e.Row.Cells[5].Text == "1st Line")
{
e.Row.Cells[5].CssClass = "Firstline";
}
if (e.Row.Cells[5].Text == "Q Check")
{
e.Row.Cells[5].CssClass = "Qcheck";
}
if (e.Row.Cells[5].Text == "2nd Line")
{
e.Row.Cells[5].CssClass = "Secondline";
}
if (e.Row.Cells[5].Text == "Duty")
{
e.Row.Cells[5].CssClass = "Duty";
}
if (e.Row.Cells[5].Text == "Leave")
{
e.Row.Cells[5].CssClass = "Leave";
}
if (e.Row.Cells[5].Text == "Other")
{
e.Row.Cells[5].CssClass = "Other";
}
if (e.Row.Cells[5].Text == "OOO")
{
e.Row.Cells[5].CssClass = "OOO";
}
if (e.Row.Cells[5].Text == "Serv Reqs")
{
e.Row.Cells[5].CssClass = "Serreqs";
}
if (e.Row.Cells[5].Text == "Task")
{
e.Row.Cells[5].CssClass = "Task";
}
if (e.Row.Cells[5].Text == "Sick")
{
e.Row.Cells[5].CssClass = "Sick";
}
if (e.Row.Cells[5].Text == "Meeting")
{
e.Row.Cells[5].CssClass = "Meeting";
}
if (e.Row.Cells[5].Text == "Training")
{
e.Row.Cells[5].CssClass = "Training";
}
}
}
}
Hoping you can point me in the right direction
Andy
October 24, 2009 at 5:00 am
Not sure if I undestood your situation completely, but you are using this update command,
UPDATE Data
SET Slot1 = @Slot1,
Slot2 = @Slot2,
Slot3 = @Slot3,
Slot4 = @Slot4,
Comments = @Comments
WHERE (ID = @ID)
Do you have 'comments' column in your 'data' table?
---------------------------------------------------------------------------------
October 24, 2009 at 8:43 am
Yes, sorry I forgot to say I added an extra column called comments.
I have since had a play around and if I use a details view to update the records it works.
This means that I have to take the user away from the gridview to do an update instead of updating straight from the gridview.
The page I added is
<%@ Page Language="C#" MasterPageFile="~/MasterPages/MasterPage.master" AutoEventWireup="true" CodeFile="AddNewEntry.aspx.cs" Inherits="Management_AddNewEntry" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="WorkplanMainContent" Runat="Server">
<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"
DataKeyNames="ID" DataSourceID="AddNewEntrySrc" DefaultMode="Insert"
Height="50px" onitemupdated="DetailsView1_ItemUpdated"
onitemupdating="DetailsView1_ItemUpdating" Width="125px">
<Fields>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
ReadOnly="True" SortExpression="ID" />
<asp:TemplateField HeaderText="Name" SortExpression="Name">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList5" runat="server" DataSourceID="NameSrc"
DataTextField="Name" DataValueField="ID"
SelectedValue='<%# Bind("Name") %>' AppendDataBoundItems="True">
</asp:DropDownList>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList6" runat="server" DataSourceID="NameSrc"
DataTextField="Name" DataValueField="ID"
SelectedValue='<%# Bind("Name") %>' AppendDataBoundItems="True">
<asp:ListItem>Please make a selection</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("Name") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Slot1" SortExpression="Slot1">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="ActivitySrc"
DataTextField="Activity" DataValueField="ID"
SelectedValue='<%# Bind("Slot1") %>' AppendDataBoundItems="True">
<asp:ListItem>Please make a selection</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList7" runat="server" DataSourceID="ActivitySrc"
DataTextField="Activity" DataValueField="ID"
SelectedValue='<%# Bind("Slot1") %>' AppendDataBoundItems="True">
<asp:ListItem>Please make a selection</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("Slot1") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Slot2" SortExpression="Slot2">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="ActivitySrc"
DataTextField="Activity" DataValueField="ID"
SelectedValue='<%# Bind("Slot2") %>' AppendDataBoundItems="True">
<asp:ListItem>Please make a selection</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList8" runat="server" DataSourceID="ActivitySrc"
DataTextField="Activity" DataValueField="ID"
SelectedValue='<%# Bind("Slot2") %>' AppendDataBoundItems="True">
<asp:ListItem>Please make a selection</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("Slot2") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Slot3" SortExpression="Slot3">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList3" runat="server" DataSourceID="ActivitySrc"
DataTextField="Activity" DataValueField="ID"
SelectedValue='<%# Bind("Slot3") %>' AppendDataBoundItems="True">
<asp:ListItem>Please make a selection</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList9" runat="server" DataSourceID="ActivitySrc"
DataTextField="Activity" DataValueField="ID"
SelectedValue='<%# Bind("Slot3") %>' AppendDataBoundItems="True">
<asp:ListItem>Please make a selection</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("Slot3") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Slot4" SortExpression="Slot4">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList4" runat="server" DataSourceID="ActivitySrc"
DataTextField="Activity" DataValueField="ID"
SelectedValue='<%# Bind("Slot4") %>' AppendDataBoundItems="True">
<asp:ListItem>Please make a selection</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList10" runat="server" DataSourceID="ActivitySrc"
DataTextField="Activity" DataValueField="ID"
SelectedValue='<%# Bind("Slot4") %>' AppendDataBoundItems="True">
<asp:ListItem>Please make a selection</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("Slot4") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date" SortExpression="Date">
<EditItemTemplate>
<asp:Calendar ID="Calendar2" runat="server" BackColor="White"
BorderColor="#3366CC" BorderWidth="1px" CellPadding="1"
DayNameFormat="Shortest" Font-Names="Verdana" Font-Size="8pt"
ForeColor="#003399" Height="200px" SelectedDate='<%# Bind("Date") %>'
Width="220px">
<SelectedDayStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<SelectorStyle BackColor="#99CCCC" ForeColor="#336666" />
<WeekendDayStyle BackColor="#CCCCFF" />
<TodayDayStyle BackColor="#99CCCC" ForeColor="White" />
<OtherMonthDayStyle ForeColor="#999999" />
<NextPrevStyle Font-Size="8pt" ForeColor="#CCCCFF" />
<DayHeaderStyle BackColor="#99CCCC" ForeColor="#336666" Height="1px" />
<TitleStyle BackColor="#003399" BorderColor="#3366CC" BorderWidth="1px"
Font-Bold="True" Font-Size="10pt" ForeColor="#CCCCFF" Height="25px" />
</asp:Calendar>
</EditItemTemplate>
<InsertItemTemplate>
<asp:Calendar ID="Calendar1" runat="server" BackColor="White"
BorderColor="#3366CC" BorderWidth="1px" CellPadding="1"
DayNameFormat="Shortest" Font-Names="Verdana" Font-Size="8pt"
ForeColor="#003399" Height="200px" SelectedDate='<%# Bind("Date") %>'
Width="220px">
<SelectedDayStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<SelectorStyle BackColor="#99CCCC" ForeColor="#336666" />
<WeekendDayStyle BackColor="#CCCCFF" />
<TodayDayStyle BackColor="#99CCCC" ForeColor="White" />
<OtherMonthDayStyle ForeColor="#999999" />
<NextPrevStyle Font-Size="8pt" ForeColor="#CCCCFF" />
<DayHeaderStyle BackColor="#99CCCC" ForeColor="#336666" Height="1px" />
<TitleStyle BackColor="#003399" BorderColor="#3366CC" BorderWidth="1px"
Font-Bold="True" Font-Size="10pt" ForeColor="#CCCCFF" Height="25px" />
</asp:Calendar>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%# Bind("Date") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Comments" SortExpression="Comments">
<EditItemTemplate>
<asp:TextBox ID="TextBox7" runat="server" MaxLength="200"
Text='<%# Bind("Comments") %>' TextMode="MultiLine" Width="250px"></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBox ID="TextBox7" runat="server" MaxLength="200"
Text='<%# Bind("Comments") %>' TextMode="MultiLine" Width="250px"></asp:TextBox>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label7" runat="server" Text='<%# Bind("Comments") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ButtonType="Button" CancelText="Clear" ShowEditButton="True"
ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="ActivitySrc" runat="server"
ConnectionString="<%$ ConnectionStrings:SSWorkPlanConnectionString %>"
SelectCommand="SELECT [ID], [Activity] FROM [Activities] ORDER BY [Activity]">
</asp:SqlDataSource>
<asp:SqlDataSource ID="NameSrc" runat="server"
ConnectionString="<%$ ConnectionStrings:SSWorkPlanConnectionString %>"
SelectCommand="SELECT [ID], [Name] FROM [Employees] ORDER BY [Name]">
</asp:SqlDataSource>
<asp:SqlDataSource ID="AddNewEntrySrc" runat="server"
ConnectionString="<%$ ConnectionStrings:SSWorkPlanConnectionString %>"
InsertCommand="INSERT INTO Data(Date, Slot1, Slot2, Slot3, Slot4, Name, Comments) VALUES (@Date, @Slot1, @Slot2, @Slot3, @Slot4, @Name, @Comments)"
SelectCommand="SELECT ID, Name, Slot1, Slot2, Slot3, Slot4, Date, Comments
FROM Data
Where ID =@ID"
UpdateCommand="UPDATE Data SET Date = @Date, Slot1 = @Slot1, Slot2 = @Slot2, Slot3 = @Slot3, Slot4 = @Slot4, Name = @Name, Comments = @Comments WHERE (ID = @ID)">
<SelectParameters>
<asp:QueryStringParameter Name="ID" QueryStringField="ID" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="Date" />
<asp:Parameter Name="Slot1" />
<asp:Parameter Name="Slot2" />
<asp:Parameter Name="Slot3" />
<asp:Parameter Name="Slot4" />
<asp:Parameter Name="Name" />
<asp:Parameter Name="Comments" />
<asp:Parameter Name="ID" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="Date" />
<asp:Parameter Name="Slot1" />
<asp:Parameter Name="Slot2" />
<asp:Parameter Name="Slot3" />
<asp:Parameter Name="Slot4" />
<asp:Parameter Name="Name" />
<asp:Parameter Name="Comments" />
</InsertParameters>
</asp:SqlDataSource>
</asp:Content>
This enables me to use a seperate datasource and a different Select command.
So, Now when the first page loads with the original Select Command in Grid View the entries in the Name column are a hyperlink. When clicked on takes you to a new page with a details view where the record can be updated.
Hope this makes sense
Andy
October 24, 2009 at 11:19 am
sorry, to be honest, I am not sure of the .net part of your application(I dont know .net that much 🙁 ). I was trying to see if there is any problem with the SQL part of it. Lets see if someone has any advice to offer. (its less likely since this forum is for ss2k5)
---------------------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply