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();
}
 
Joined
Oct 25, 2006
Messages
6
Reaction score
0
'Deleting is not supported by data source

Only joined this group recently, so you've probably figured this out by now, but what you need to do is go back to where you Configure your data source, and when you select the Table you want using the Wizard, select "Advanced" and check off the box to create Insert, Update, and Delete statements. That'll solve the problem.

Carolyn
 
Joined
Dec 27, 2010
Messages
1
Reaction score
0
Of course this is not intended as a reply to original poster but just saying it here just in case someone is looking for it...you can use a template column let say with a delete image and make sure there is command name called "Delete" specified there. If "Delete" command name is specified then ASP.NET takes over the processing and your code behind will not even called.
 

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

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top