Hi
I am trying to add edit and update function to my gridview.
I am using Visual Studio 2008.
I have 3 tables in my database and have a SELECT query which works fine.
I now want to add the edit button to update the database records.
I also have conditional formatting on the results in the gridview.
here are my tables
<br />
USE [SSWorkplan]<br />
GO<br />
<br />
CREATE TABLE [dbo].[Activities](<br />
[ID] [int] IDENTITY(1,1) NOT NULL,<br />
[Activity] [varchar](20) NOT NULL,<br />
CONSTRAINT [PK_Activities] PRIMARY KEY CLUSTERED <br />
(<br />
[ID] ASC<br />
)<br />
<br />
GO<br />
<br />
USE [SSWorkplan]<br />
GO<br />
<br />
CREATE TABLE [dbo].[Employees](<br />
[ID] [int] IDENTITY(1,1) NOT NULL,<br />
[Name] [varchar](50) NOT NULL,<br />
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED <br />
(<br />
[ID] ASC<br />
)<br />
GO<br />
<br />
USE [SSWorkplan]<br />
GO<br />
CREATE TABLE [dbo].[Data](<br />
[ID] [int] IDENTITY(1,1) NOT NULL,<br />
[Name] [int] NOT NULL,<br />
[Date] [datetime] NOT NULL,<br />
[Slot1] [int] NOT NULL,<br />
[Slot2] [int] NOT NULL,<br />
[Slot3] [int] NOT NULL,<br />
[Slot4] [int] NOT NULL,<br />
CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED <br />
(<br />
[ID] ASC<br />
)<br />
GO
I added the following test data to the tables
<br />
INSERT INTO [SSWorkplan].[dbo].[Activities]<br />
([Activity])<br />
VALUES<br />
('Sick, Duty', 'Task, Training', '1st Line', 'Meeting', 'Leave',<br />
'Ser Reqs', 'Q Check', '2nd Line', 'Other', 'OOO')<br />
<br />
INSERT INTO [SSWorkplan].[dbo].[Employees]<br />
([Name])<br />
VALUES<br />
('Andrew Woodward', 'Ash Bassett', 'Brian Horan', 'Dalbir Thandi',<br />
'Ismail Patel', 'John Hickenbotham', 'Joby Vaughan', 'Kieron Meadows')<br />
<br />
INSERT INTO [SSWorkplan].[dbo].[Data]<br />
([Name]<br />
,[Date]<br />
,[Slot1]<br />
,[Slot2]<br />
,[Slot3]<br />
,[Slot4])<br />
VALUES<br />
(1, '02/11/09', 5, 5, 5, 5,<br />
2, '02/11/09', 9, 5, 3, 3,<br />
3, '02/11/09', 10, 10, 10, 10,<br />
4, '02/11/09', 7, 7, 7, 7,<br />
5, '02/11/09', 2, 2, 5, 5,<br />
6, '02/11/09', 10, 10, 10, 10,<br />
7, '02/11/09', 10, 10, 10, 10,<br />
8, '02/11/09', 5, 5, 2, 2)
The select statement is
<br />
SELECT Employees.Name, Activities1.Activity AS Slot1, Activities2.Activity AS Slot2, Activities3.Activity AS Slot3, Activities4.Activity AS Slot4, Comments <br />
FROM Data <br />
INNER JOIN Employees ON Data.Name = Employees.ID <br />
INNER JOIN Activities AS Activities1 ON Data.Slot1 = Activities1.ID <br />
INNER JOIN Activities AS Activities2 ON Data.Slot2 = Activities2.ID <br />
INNER JOIN Activities AS Activities3 ON Data.Slot3 = Activities3.ID <br />
INNER JOIN Activities AS Activities4 ON Data.Slot4 = Activities4.ID<br />
Where date = @WPDate
This all works fine upto here.
When I add an Update statement to the datasource and enable editing in the gridview task pane I get the following error.
Server Error in '/' Application.
--------------------------------------------------------------------------------
Incorrect syntax near '.30'.
If I execute the update command whilst in query designer it works fine and updates the database.
Also when the enable editing is set the conditional formatting on the forth row doesn't work.
What am I missing?
This is my final code.
ASPX page
<br />
<%@ Page Language="C#" MasterPageFile="~/MasterPages/MasterPage.master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" Title="Service Support Workplan Plan" %><br />
<br />
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server"><br />
</asp:Content><br />
<asp:Content ID="Content2" ContentPlaceHolderID="WorkplanMainContent" Runat="Server"><br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" <br />
DataSourceID="SqlDataSource1" onrowdatabound="GridView1_RowDataBound" <br />
CssClass="DayGrid"><br />
<Columns><br />
<asp:CommandField ShowEditButton="True" /><br />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" /><br />
<asp:BoundField DataField="08.30-10.00" HeaderText="08.30-10.00" <br />
SortExpression="08.30-10.00" /><br />
<asp:BoundField DataField="10.00-13.00" HeaderText="10.00-13.00" <br />
SortExpression="10.00-13.00" /><br />
<asp:BoundField DataField="13.00-16.00" HeaderText="13.00-16.00" <br />
SortExpression="13.00-16.00" /><br />
<asp:BoundField DataField="16.00-17.00" HeaderText="16.00-17.00" <br />
SortExpression="16.00-17.00" /><br />
<asp:BoundField DataField="Comments" HeaderText="Comments" <br />
SortExpression="Comments" /><br />
</Columns><br />
</asp:GridView><br />
<asp:Calendar ID="Calendar1" runat="server" BackColor="White" <br />
BorderColor="#3366CC" BorderWidth="1px" CellPadding="1" CssClass="calendar" <br />
DayNameFormat="Shortest" Font-Names="Verdana" Font-Size="8pt" <br />
ForeColor="#003399" Height="200px" Width="220px"><br />
<SelectedDayStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" /><br />
<SelectorStyle BackColor="#99CCCC" ForeColor="#336666" /><br />
<WeekendDayStyle BackColor="#CCCCFF" /><br />
<TodayDayStyle BackColor="#99CCCC" ForeColor="White" /><br />
<OtherMonthDayStyle ForeColor="#999999" /><br />
<NextPrevStyle Font-Size="8pt" ForeColor="#CCCCFF" /><br />
<DayHeaderStyle BackColor="#99CCCC" ForeColor="#336666" Height="1px" /><br />
<TitleStyle BackColor="#003399" BorderColor="#3366CC" BorderWidth="1px" <br />
Font-Bold="True" Font-Size="10pt" ForeColor="#CCCCFF" Height="25px" /><br />
</asp:Calendar><br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" <br />
ConnectionString="<%$ ConnectionStrings:SSWorkplanConnectionString2 %>" <br />
<br />
<br />
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<br />
Where date = @WPDate" <br />
UpdateCommand="UPDATE Data SET Slot1 = @SLOT1, Slot2 = @Slot2, Slot3 = @Slot3, Slot4 = @Slot4, Comments = @Comments WHERE (ID = @ID)"><br />
<SelectParameters><br />
<asp:ControlParameter ControlID="Calendar1" Name="WPDate" <br />
PropertyName="SelectedDate" /><br />
</SelectParameters><br />
<UpdateParameters><br />
<asp:Parameter Name="SLOT1" /><br />
<asp:Parameter Name="Slot2" /><br />
<asp:Parameter Name="Slot3" /><br />
<asp:Parameter Name="Slot4" /><br />
<asp:Parameter Name="Comments" /><br />
<asp:Parameter Name="ID" /><br />
</UpdateParameters><br />
</asp:SqlDataSource><br />
</asp:Content><br />
<br />
Code behind in c#
<br />
using System;<br />
using System.Collections;<br />
using System.Configuration;<br />
using System.Data;<br />
using System.Linq;<br />
using System.Web;<br />
using System.Web.Security;<br />
using System.Web.UI;<br />
using System.Web.UI.HtmlControls;<br />
using System.Web.UI.WebControls;<br />
using System.Web.UI.WebControls.WebParts;<br />
using System.Xml.Linq;<br />
<br />
public partial class _Default : System.Web.UI.Page<br />
{<br />
protected void Page_Load(object sender, EventArgs e)<br />
{<br />
<br />
}<br />
<br />
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)<br />
{<br />
<br />
if (e.Row.RowType == DataControlRowType.DataRow)<br />
{<br />
if (e.Row.Cells[1].Text == "1st Line")<br />
{<br />
e.Row.Cells[1].CssClass = "Firstline";<br />
}<br />
<br />
if (e.Row.Cells[1].Text == "Q Check")<br />
{<br />
e.Row.Cells[1].CssClass = "Qcheck";<br />
}<br />
<br />
if (e.Row.Cells[1].Text == "2nd Line")<br />
{<br />
e.Row.Cells[1].CssClass = "Secondline";<br />
}<br />
<br />
if (e.Row.Cells[1].Text == "Duty")<br />
{<br />
e.Row.Cells[1].CssClass = "Duty";<br />
}<br />
<br />
if (e.Row.Cells[1].Text == "Leave")<br />
{<br />
e.Row.Cells[1].CssClass = "Leave";<br />
}<br />
<br />
if (e.Row.Cells[1].Text == "Other")<br />
{<br />
e.Row.Cells[1].CssClass = "Other";<br />
}<br />
<br />
if (e.Row.Cells[1].Text == "OOO")<br />
{<br />
e.Row.Cells[1].CssClass = "OOO";<br />
}<br />
<br />
if (e.Row.Cells[1].Text == "Serv Reqs")<br />
{<br />
e.Row.Cells[1].CssClass = "Serreqs";<br />
}<br />
<br />
if (e.Row.Cells[1].Text == "Task")<br />
{<br />
e.Row.Cells[1].CssClass = "Task";<br />
}<br />
<br />
if (e.Row.Cells[1].Text == "Sick")<br />
{<br />
e.Row.Cells[1].CssClass = "Sick";<br />
}<br />
<br />
if (e.Row.Cells[1].Text == "Meeting")<br />
{<br />
e.Row.Cells[1].CssClass = "Meeting";<br />
}<br />
<br />
if (e.Row.Cells[1].Text == "Training")<br />
{<br />
e.Row.Cells[1].CssClass = "Training";<br />
}<br />
<br />
if (e.Row.Cells[2].Text == "1st Line")<br />
{<br />
e.Row.Cells[2].CssClass = "Firstline";<br />
}<br />
<br />
if (e.Row.Cells[2].Text == "Q Check")<br />
{<br />
e.Row.Cells[2].CssClass = "Qcheck";<br />
}<br />
<br />
if (e.Row.Cells[2].Text == "2nd Line")<br />
{<br />
e.Row.Cells[2].CssClass = "Secondline";<br />
}<br />
<br />
if (e.Row.Cells[2].Text == "Duty")<br />
{<br />
e.Row.Cells[2].CssClass = "Duty";<br />
}<br />
<br />
if (e.Row.Cells[2].Text == "Leave")<br />
{<br />
e.Row.Cells[2].CssClass = "Leave";<br />
}<br />
<br />
if (e.Row.Cells[2].Text == "Other")<br />
{<br />
e.Row.Cells[2].CssClass = "Other";<br />
}<br />
<br />
if (e.Row.Cells[2].Text == "OOO")<br />
{<br />
e.Row.Cells[2].CssClass = "OOO";<br />
}<br />
<br />
if (e.Row.Cells[2].Text == "Serv Reqs")<br />
{<br />
e.Row.Cells[2].CssClass = "Serreqs";<br />
}<br />
<br />
if (e.Row.Cells[2].Text == "Task")<br />
{<br />
e.Row.Cells[2].CssClass = "Task";<br />
}<br />
<br />
if (e.Row.Cells[2].Text == "Sick")<br />
{<br />
e.Row.Cells[2].CssClass = "Sick";<br />
}<br />
<br />
if (e.Row.Cells[2].Text == "Meeting")<br />
{<br />
e.Row.Cells[2].CssClass = "Meeting";<br />
}<br />
<br />
if (e.Row.Cells[2].Text == "Training")<br />
{<br />
e.Row.Cells[2].CssClass = "Training";<br />
}<br />
<br />
if (e.Row.Cells[3].Text == "1st Line")<br />
{<br />
e.Row.Cells[3].CssClass = "Firstline";<br />
}<br />
<br />
if (e.Row.Cells[3].Text == "Q Check")<br />
{<br />
e.Row.Cells[3].CssClass = "Qcheck";<br />
}<br />
<br />
if (e.Row.Cells[3].Text == "2nd Line")<br />
{<br />
e.Row.Cells[3].CssClass = "Secondline";<br />
}<br />
<br />
if (e.Row.Cells[3].Text == "Duty")<br />
{<br />
e.Row.Cells[3].CssClass = "Duty";<br />
}<br />
<br />
if (e.Row.Cells[3].Text == "Leave")<br />
{<br />
e.Row.Cells[3].CssClass = "Leave";<br />
}<br />
<br />
if (e.Row.Cells[3].Text == "Other")<br />
{<br />
e.Row.Cells[3].CssClass = "Other";<br />
}<br />
<br />
if (e.Row.Cells[3].Text == "OOO")<br />
{<br />
e.Row.Cells[3].CssClass = "OOO";<br />
}<br />
<br />
if (e.Row.Cells[3].Text == "Serv Reqs")<br />
{<br />
e.Row.Cells[3].CssClass = "Serreqs";<br />
}<br />
<br />
if (e.Row.Cells[3].Text == "Task")<br />
{<br />
e.Row.Cells[3].CssClass = "Task";<br />
}<br />
<br />
if (e.Row.Cells[3].Text == "Sick")<br />
{<br />
e.Row.Cells[3].CssClass = "Sick";<br />
}<br />
<br />
if (e.Row.Cells[3].Text == "Meeting")<br />
{<br />
e.Row.Cells[3].CssClass = "Meeting";<br />
}<br />
<br />
if (e.Row.Cells[3].Text == "Training")<br />
{<br />
e.Row.Cells[3].CssClass = "Training";<br />
}<br />
<br />
if (e.Row.Cells[4].Text == "1st Line")<br />
{<br />
e.Row.Cells[4].CssClass = "Firstline";<br />
}<br />
<br />
if (e.Row.Cells[4].Text == "Q Check")<br />
{<br />
e.Row.Cells[4].CssClass = "Qcheck";<br />
}<br />
<br />
if (e.Row.Cells[4].Text == "2nd Line")<br />
{<br />
e.Row.Cells[4].CssClass = "Secondline";<br />
}<br />
<br />
if (e.Row.Cells[4].Text == "Duty")<br />
{<br />
e.Row.Cells[4].CssClass = "Duty";<br />
}<br />
<br />
if (e.Row.Cells[4].Text == "Leave")<br />
{<br />
e.Row.Cells[4].CssClass = "Leave";<br />
}<br />
<br />
if (e.Row.Cells[4].Text == "Other")<br />
{<br />
e.Row.Cells[4].CssClass = "Other";<br />
}<br />
<br />
if (e.Row.Cells[4].Text == "OOO")<br />
{<br />
e.Row.Cells[4].CssClass = "OOO";<br />
}<br />
<br />
if (e.Row.Cells[4].Text == "Serv Reqs")<br />
{<br />
e.Row.Cells[4].CssClass = "Serreqs";<br />
}<br />
<br />
if (e.Row.Cells[4].Text == "Task")<br />
{<br />
e.Row.Cells[4].CssClass = "Task";<br />
}<br />
<br />
if (e.Row.Cells[4].Text == "Sick")<br />
{<br />
e.Row.Cells[4].CssClass = "Sick";<br />
}<br />
<br />
if (e.Row.Cells[4].Text == "Meeting")<br />
{<br />
e.Row.Cells[4].CssClass = "Meeting";<br />
}<br />
<br />
if (e.Row.Cells[4].Text == "Training")<br />
{<br />
e.Row.Cells[4].CssClass = "Training";<br />
}<br />
}<br />
}<br />
}<br />
Hope you can help
Andy
|