By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,972 Members | 883 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,972 IT Pros & Developers. It's quick & easy.

SqlDataSource and GridView

P: n/a
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();
}


*** Sent via Developersdex http://www.developersdex.com ***
May 31 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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();
}
*** Sent via Developersdex http://www.developersdex.com ***

Jun 1 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.