SqlDataSource and GridView

M

Mike P

When you use a SqlDataSource to hook up data to a GridView it seems to
be to be very inflexible. For example, I want to create my own Delete
button with my own code and I also want to create a popup warning for
the user before delete takes place. Whenever I do this with a
SqlDataSource, I get the error 'Deleting is not supported by data source
'SqlDataSource1' unless DeleteCommand is specified.' Which means I have
to specify a sproc or text to use as the Delete and I can't use my popup
etc. Can anybody tell me if there is a way around this? Here is my
code :

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$
ConnectionStrings:XeroxConnectionString %>"
SelectCommand="ViewForecast"
SelectCommandType="StoredProcedure">
</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server"
DataSourceID="SqlDataSource1"
SkinID="Grey" AutoGenerateColumns="false"
DataKeyNames="ForecastKey" AllowSorting="true"
OnRowDataBound="GridView1_RowDataBound"
OnRowCommand="GridView1_RowCommand">
<Columns>
<asp:TemplateField HeaderText="Company"
SortExpression="Name">
<ItemTemplate>
<asp:Label
ID="lblCompany" Text='<%# Eval("Name") %>' runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>

<asp:TemplateField
HeaderText="Forecast Type" SortExpression="ForecastDescription">
<ItemTemplate>
<asp:Label
ID="lblForecastType" Text='<%# Eval("ForecastDescription") %>'
runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>

<asp:TemplateField
HeaderText="Value (£)" SortExpression="MoneyValue">
<ItemTemplate>
<asp:Label
ID="lblMoneyValue" Text='<%# Eval("MoneyValue", "{0:#,###.00}") %>'
runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>

<asp:TemplateField
HeaderText="Probability (%)" SortExpression="Probability">
<ItemTemplate>
<asp:Label
ID="lblProbability" Text='<%# Eval("Probability") %>'
runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>

<asp:TemplateField
HeaderText="Weeks 1-3 (£) x (%)" SortExpression="ThreeWeekPercentage">
<ItemTemplate>
<asp:Label
ID="lblThreeWeekPercentage" runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>

<asp:TemplateField
HeaderText="Week 4 (£) x (%-25%)" SortExpression="FourthWeekPercentage">
<ItemTemplate>
<asp:Label
ID="lblFourthWeekPercentage" runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>

<asp:TemplateField
HeaderText="Due Date" SortExpression="InvoiceDate">
<ItemTemplate>
<asp:Label
ID="lblDueDate" Text='<%# Eval("InvoiceDate", "{0:dd/MM/yyyy}") %>'
runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>

<asp:TemplateField
HeaderText="Last Edit Date" SortExpression="CreateDate">
<ItemTemplate>
<asp:Label
ID="lblLastEditDate" Text='<%# Eval("CreateDate", "{0:dd/MM/yyyy}") %>'
runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>

<asp:CommandField
ShowEditButton="True" ButtonType="Link" ShowCancelButton="True"
UpdateText="Update"
EditText="Edit" CancelText="Cancel" />

<asp:ButtonField
ButtonType="Link" CommandName="Delete" Text="Delete" />
</Columns>
</asp:GridView>

protected void GridView1_RowDataBound(object sender,
GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label ForecastType =
(Label)e.Row.FindControl("lblForecastType");

switch (ForecastType.Text)
{
case "Analysis":
e.Row.Cells[1].BackColor =
System.Drawing.Color.Cyan;
break;

case "Call Centre":
e.Row.Cells[1].BackColor =
System.Drawing.Color.Lime;
break;

case "Data":
e.Row.Cells[1].BackColor =
System.Drawing.Color.Yellow;
break;

case "Data/Analysis":
e.Row.Cells[1].BackColor =
System.Drawing.Color.Magenta;
break;
}

Label Probability =
(Label)e.Row.FindControl("lblProbability");

switch (Probability.Text)
{
case "0.25":
e.Row.Cells[3].Text = "C (25%)";
e.Row.Cells[3].BackColor =
System.Drawing.Color.Coral;
break;

case "0.5":
e.Row.Cells[3].Text = "B (50%)";
e.Row.Cells[3].BackColor =
System.Drawing.Color.OrangeRed;
break;

case "0.75":
e.Row.Cells[3].Text = "A (75%)";
e.Row.Cells[3].BackColor = System.Drawing.Color.Red;
break;

case "0.751":
e.Row.Cells[3].Text = "Proforma (75%)";
e.Row.Cells[3].BackColor =
System.Drawing.Color.Maroon;
break;

case "1":
e.Row.Cells[3].Text = "In (100%)";
e.Row.Cells[3].BackColor =
System.Drawing.Color.White;
break;
}

LinkButton EditButton =
(LinkButton)e.Row.Cells[8].Controls[0];
EditButton.ForeColor = System.Drawing.Color.Blue;

LinkButton DeleteButton =
(LinkButton)e.Row.Cells[9].Controls[0];
DeleteButton.ForeColor = System.Drawing.Color.Blue;

DeleteButton.Attributes.Add("onclick", "javascript:return "
+
"confirm('Are you sure you want to delete forecast record "
+
DataBinder.Eval(e.Row.DataItem, "ForecastKey") + "?')");
}
}

protected void GridView1_RowCommand(object sender,
GridViewCommandEventArgs e)
{
if (e.CommandName == "Delete")
{
// 1)Convert the row index stored in the CommandArgument
property to an Integer
int index = Convert.ToInt32(e.CommandArgument);

// Retrieve the row that contains the button clicked by the
user from the Rows collection
GridViewRow row = GridView1.Rows[index];

//get datakeys
int id = (int)GridView1.DataKeys[row.DataItemIndex].Value;

//2) delete row
DataAccess da = new DataAccess();

// delete row etc



// GridView1.EditIndex = -1;

// //refresh gridview
// GridView1.DataBind();
}
 
B

Brock Allen

You can always use a TemplateColumn and put your own Button with a OnClientClick
property where you use a prompt(). When the event makes it back to the server,
to have the Button cause the GridView and thus the SqlDataSource to trigger
the DeleteCommand the CommandName on the Button must be the well-known value
"Delete".

-Brock
http://staff.develop.com/ballen

When you use a SqlDataSource to hook up data to a GridView it seems to
be to be very inflexible. For example, I want to create my own Delete
button with my own code and I also want to create a popup warning for
the user before delete takes place. Whenever I do this with a
SqlDataSource, I get the error 'Deleting is not supported by data
source 'SqlDataSource1' unless DeleteCommand is specified.' Which
means I have to specify a sproc or text to use as the Delete and I
can't use my popup etc. Can anybody tell me if there is a way around
this? Here is my code :

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$
ConnectionStrings:XeroxConnectionString %>"
SelectCommand="ViewForecast"
SelectCommandType="StoredProcedure">
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server"
DataSourceID="SqlDataSource1"
SkinID="Grey" AutoGenerateColumns="false"
DataKeyNames="ForecastKey" AllowSorting="true"
OnRowDataBound="GridView1_RowDataBound"
OnRowCommand="GridView1_RowCommand">
<Columns>
<asp:TemplateField HeaderText="Company"
SortExpression="Name">
<ItemTemplate>
<asp:Label
ID="lblCompany" Text='<%# Eval("Name") %>' runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>
<asp:TemplateField
HeaderText="Forecast Type" SortExpression="ForecastDescription">
<ItemTemplate>
<asp:Label
ID="lblForecastType" Text='<%# Eval("ForecastDescription") %>'
runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>
<asp:TemplateField
HeaderText="Value (#)" SortExpression="MoneyValue">
<ItemTemplate>
<asp:Label
ID="lblMoneyValue" Text='<%# Eval("MoneyValue", "{0:#,###.00}") %>'
runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>
<asp:TemplateField
HeaderText="Probability (%)" SortExpression="Probability">
<ItemTemplate>
<asp:Label
ID="lblProbability" Text='<%# Eval("Probability") %>'
runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>
<asp:TemplateField
HeaderText="Weeks 1-3 (#) x (%)" SortExpression="ThreeWeekPercentage">
<ItemTemplate>
<asp:Label
ID="lblThreeWeekPercentage" runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>
<asp:TemplateField
HeaderText="Week 4 (#) x (%-25%)"
SortExpression="FourthWeekPercentage">
<ItemTemplate>
<asp:Label
ID="lblFourthWeekPercentage" runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>
<asp:TemplateField
HeaderText="Due Date" SortExpression="InvoiceDate">
<ItemTemplate>
<asp:Label
ID="lblDueDate" Text='<%# Eval("InvoiceDate", "{0:dd/MM/yyyy}") %>'
runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>
<asp:TemplateField
HeaderText="Last Edit Date" SortExpression="CreateDate">
<ItemTemplate>
<asp:Label
ID="lblLastEditDate" Text='<%# Eval("CreateDate", "{0:dd/MM/yyyy}")
%>'
runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Height="24px"
Width="50px" />
</asp:TemplateField>
<asp:CommandField
ShowEditButton="True" ButtonType="Link" ShowCancelButton="True"
UpdateText="Update"
EditText="Edit" CancelText="Cancel" />
<asp:ButtonField
ButtonType="Link" CommandName="Delete" Text="Delete" />
</Columns>
</asp:GridView>
protected void GridView1_RowDataBound(object sender,
GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label ForecastType =
(Label)e.Row.FindControl("lblForecastType");
switch (ForecastType.Text)
{
case "Analysis":
e.Row.Cells[1].BackColor =
System.Drawing.Color.Cyan;
break;
case "Call Centre":
e.Row.Cells[1].BackColor =
System.Drawing.Color.Lime;
break;
case "Data":
e.Row.Cells[1].BackColor =
System.Drawing.Color.Yellow;
break;
case "Data/Analysis":
e.Row.Cells[1].BackColor =
System.Drawing.Color.Magenta;
break;
}
Label Probability =
(Label)e.Row.FindControl("lblProbability");
switch (Probability.Text)
{
case "0.25":
e.Row.Cells[3].Text = "C (25%)";
e.Row.Cells[3].BackColor =
System.Drawing.Color.Coral;
break;
case "0.5":
e.Row.Cells[3].Text = "B (50%)";
e.Row.Cells[3].BackColor =
System.Drawing.Color.OrangeRed;
break;
case "0.75":
e.Row.Cells[3].Text = "A (75%)";
e.Row.Cells[3].BackColor =
System.Drawing.Color.Red;
break;
case "0.751":
e.Row.Cells[3].Text = "Proforma (75%)";
e.Row.Cells[3].BackColor =
System.Drawing.Color.Maroon;
break;
case "1":
e.Row.Cells[3].Text = "In (100%)";
e.Row.Cells[3].BackColor =
System.Drawing.Color.White;
break;
}
LinkButton EditButton =
(LinkButton)e.Row.Cells[8].Controls[0];
EditButton.ForeColor = System.Drawing.Color.Blue;
LinkButton DeleteButton =
(LinkButton)e.Row.Cells[9].Controls[0];
DeleteButton.ForeColor = System.Drawing.Color.Blue;
DeleteButton.Attributes.Add("onclick", "javascript:return
"
+
"confirm('Are you sure you want to delete forecast record
"
+
DataBinder.Eval(e.Row.DataItem, "ForecastKey") + "?')");
}
}
protected void GridView1_RowCommand(object sender,
GridViewCommandEventArgs e)
{
if (e.CommandName == "Delete")
{
// 1)Convert the row index stored in the CommandArgument
property to an Integer
int index = Convert.ToInt32(e.CommandArgument);
// Retrieve the row that contains the button clicked by
the
user from the Rows collection
GridViewRow row = GridView1.Rows[index];
//get datakeys
int id = (int)GridView1.DataKeys[row.DataItemIndex].Value;
//2) delete row
DataAccess da = new DataAccess();
// delete row etc

// GridView1.EditIndex = -1;

// //refresh gridview
// GridView1.DataBind();
}
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

specifying update parameters in gridview 0
Modal popup with gridview row detail 1
GridView default column not enough for columns with alot of data 1
hyperlink in gridview 1
enter key causing postback and then errors 5
Show Date without time 1
Performing calcualtions on data in gridview 2
Button Click event not getting called 5

Top