Databinding editable GridView with SqlDataSource on button click

[moha...@gmail.com]

This seems like it should be simple to do but for some reason I have
been unable to make it work.

I would like to databind a SqlDataSource to a GridView during the
click event of a button. This sounds easy but the next requirement is
that the GridView is editable. So I have included the SelectCommand
and the UpdateCommand on the SqlDataSource to allow the GridView to be
editable. I have now been able to get the GridView to display data on
the button click using the following code. As you can see I've tried
three different ways of doing this and each causes a different
problem. Now I am stuck and could use some help.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class EditableGridView2 : System.Web.UI.Page
{
// 3. Displays data but the row edit button causes the data to
dissapear.
protected SqlDataSource dataSource = new SqlDataSource();

protected void Page_Load(object sender, EventArgs e)
{
this.gridView.RowEditing += new
GridViewEditEventHandler(gridView_RowEditing);
this.gridView.RowUpdating += new
GridViewUpdateEventHandler(gridView_RowUpdating);
this.gridView.RowUpdated += new
GridViewUpdatedEventHandler(gridView_RowUpdated);
this.getData.Click += new EventHandler(getData_Click);

// 3. Displays data but the row edit button causes the data to
dissapear.
dataSource.ID = "SqlDataSource1";
dataSource.ConnectionString =
ConfigurationManager.ConnectionStrings["MembershipConnectionString"].ToString();
this.Controls.Add(dataSource);
this.gridView.DataSourceID = dataSource.ID;
}

void getData_Click(object sender, EventArgs e)
{
// 1. and 2.
//SqlDataSource dataSource = new SqlDataSource();
//dataSource.ID = "SqlDataSource1";
//dataSource.ConnectionString =
ConfigurationManager.ConnectionStrings["MembershipConnectionString"].ToString();

dataSource.SelectCommand = "SELECT [appID], [status], [clientName]
FROM [Applications] WHERE ([status] = @status)";
dataSource.UpdateCommand = "UPDATE [MemberApplications] SET [status]
= @status, [clientName] = @clientName WHERE [appID] = @appID";
dataSource.SelectParameters.Add("status", "Pending");
dataSource.UpdateParameters.Add(new Parameter("status"));
dataSource.UpdateParameters.Add(new Parameter("clientName"));
dataSource.UpdateParameters.Add(new Parameter("appID"));

// 1. Displays data but clicking the row edit causes the exception
"The DataSourceID of 'gridView' must be the ID of a control of type
IDataSource. A control with ID 'SqlDataSource1' could not be found."
//this.Controls.Add(dataSource);
//this.gridView.DataSourceID = dataSource.ID;

// 2. Displays data but the row edit button does not work.
//this.gridView.DataSource = dataSource;
//this.gridView.DataBind();
}

void gridView_RowUpdated(object sender, GridViewUpdatedEventArgs e)
{
this.message.Text = e.AffectedRows.ToString();
}

void gridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
//
}

void gridView_RowEditing(object sender, GridViewEditEventArgs e)
{
//
}
}

[Latish Sehgal]

Shouldn't the tablename be same in both select and update commands?

[moha...@gmail.com]

Yes, the table name should be the same in both commands. Sorry about
the bad pasting job. The code really is the same in both places. So,
just assume that the table name is the same. The code still doesn't
work.

Thanks for the heads up.

> > ConfigurationManager.ConnectionStrings["MembershipConnectionString"].ToStri-ng();

> > this.Controls.Add(dataSource);
> > this.gridView.DataSourceID = dataSource.ID;
> > }
>
> > void getData_Click(object sender, EventArgs e)
> > {
> > // 1. and 2.
> > //SqlDataSource dataSource = new SqlDataSource();
> > //dataSource.ID = "SqlDataSource1";
> > //dataSource.ConnectionString =

> > ConfigurationManager.ConnectionStrings["MembershipConnectionString"].ToStri-ng();

> = @status, [clientName] = @clientName WHERE [appID] = @appID";- Hide quoted text -
>
> - Show quoted text -

[Latish Sehgal]

I do not know how you are able to see the edit button on the datagrid,
i am not able to replicate your scenario with the code you provided. I
can see the data, but can't get the edit buttons to show.
I am pasting below markup code that works fine for me, but none of my
logic here is in code behind. Let me know if this is any help, or
perhaps you can mail me your code so that i can check it out.

<asp:sqldatasource ID="Sqldatasource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
SelectCommand="SELECT [CustomerID], [CompanyName], [Phone]
FROM [Customers]"
UpdateCommand="UPDATE [Customers] SET [CompanyName] =
@CompanyName, [Phone] = @Phone WHERE [CustomerID] = @CustomerID" >
<UpdateParameters>
<asp:Parameter Name="CompanyName" Type="String" />
<asp:Parameter Name="Phone" Type="String" />
<asp:Parameter Name="CustomerID" Type="String" />
</UpdateParameters>
</asp:sqldatasource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True"
DataSourceID="Sqldatasource1" BackColor="White"
BorderColor="#DEDFDE" BorderStyle="None"
BorderWidth="1px" CellPadding="4" ForeColor="Black"
GridLines="Vertical" >
<FooterStyle BackColor="#CCCC99" />
<RowStyle BackColor="#F7F7DE" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black"
HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True"
ForeColor="White" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True"
ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<EditRowStyle BackColor="Wheat" />
<Columns>
<asp:CommandField ShowSelectButton="True"
ShowEditButton="True" />
</Columns>
</asp:GridView>

[moha...@gmail.com]

Latish,

Your binding the GridView on page load. Change your code so that the
binding occurs during the button click. This type of binding would
need to be done if you were going to provide a form input value as
criteria for your select query. The button click is going to change
the results of the query. I'm also doing this because in my case the
column list of the select statement is going to change during the
button click as well as the criteria.

[Latish Sehgal]

Ok. I am able to get the Edit thing to work, the issue here is that
when you click Edit, the page reloads and since you have defined your
select and update commands in the button handler (which does not
execute on page reload), the datasource has incomplete information for
the edit to work. Debug and watch the datasource to check this out.
Also, since you do not want to include this information in the page
load event, i have added this to the Row_Edit event. It looks
something like

void gridView_RowEditing(object sender, GridViewEditEventArgs
e)
{
//

dataSource.SelectCommand = "SELECT [CustomerID],
[CompanyName], [Phone] FROM [Customers]";
dataSource.UpdateCommand = "UPDATE [Customers] SET

[CompanyName] = @CompanyName, [Phone] = @Phone WHERE [CustomerID] =

@CustomerID";

dataSource.UpdateParameters.Add(new
Parameter("CompanyName"));
dataSource.UpdateParameters.Add(new Parameter("Phone"));
dataSource.UpdateParameters.Add(new
Parameter("CustomerID"));

gridView.DataSource = dataSource;

gridView.EditIndex = e.NewEditIndex;
gridView.DataBind();

}

I think you might need to define some implementation in your other
events also, but hopefully this should give you a start.

-Latish Sehgal
http://www.dotnetsurfers.com/

[moha...@gmail.com]

Hello Latish,

I'm back at it again. Trying to solve this problem and implement what
you have given me. What I have now is the following code which almost
works but doesn't. In the end this worked up to the point where the
update is being made. The code excutes as far as the
gridView_RowUpdating event but then the gridView_RowUpdated event is
never called. So the update is never done. Can you or anyone else who
wants to look at this and see what I might be able to change to make
this work.

<code>

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class EditableGridView : System.Web.UI.Page
{

void gridView_RowEditing(object sender, GridViewEditEventArgs e)
{

// Re-create the SqlDataSource here for row edits.

// The SqlDataSource already exists so there isn't a reason to set
the following values again.
//this.dataSource.ID = "SqlDataSource1";
//this.dataSource.ConnectionString =
ConfigurationManager.ConnectionStrings["MembershipConnectionString"].ToString();
//this.Controls.Add(this.dataSource);
this.gridView.DataSource = this.dataSource;

this.dataSource.SelectCommand = "SELECT [appID], [status],
[clientName] FROM [MemberApplications] WHERE ([status] = @status) AND
dssgDelete = 'no'";
this.dataSource.UpdateCommand = "UPDATE [MemberApplications] SET

[status] = @status, [clientName] = @clientName WHERE [appID] =
@appID";

// Adding this parameter here causes a "Parameter already added"
exception.
//this.dataSource.SelectParameters.Add("status", "Pending");
this.dataSource.UpdateParameters.Add(new Parameter("status"));
this.dataSource.UpdateParameters.Add(new Parameter("clientName"));
this.dataSource.UpdateParameters.Add(new Parameter("appID"));

this.gridView.EditIndex = e.NewEditIndex;
this.gridView.DataBind();
}

void gridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
{

int rowIndex = e.RowIndex;
}

void gridView_RowUpdated(object sender, GridViewUpdatedEventArgs e)
{
this.message.Text = e.AffectedRows.ToString();
}

protected SqlDataSource dataSource = new SqlDataSource();

protected void Page_Load(object sender, EventArgs e)
{
this.gridView.RowEditing += new
GridViewEditEventHandler(gridView_RowEditing);
this.gridView.RowUpdating += new
GridViewUpdateEventHandler(gridView_RowUpdating);
this.gridView.RowUpdated += new
GridViewUpdatedEventHandler(gridView_RowUpdated);
this.getData.Click += new EventHandler(getData_Click);

this.dataSource.ID = "SqlDataSource1";
this.dataSource.ConnectionString =
ConfigurationManager.ConnectionStrings["MembershipConnectionString"].ToString();
this.Controls.Add(this.dataSource);
}

void getData_Click(object sender, EventArgs e)
{

this.dataSource.SelectCommand = "SELECT [appID], [status],
[clientName] FROM [MemberApplications] WHERE ([status] = @status) AND
dssgDelete = 'no'";
this.dataSource.UpdateCommand = "UPDATE [MemberApplications] SET

[status] = @status, [clientName] = @clientName WHERE [appID] =
@appID";

this.dataSource.SelectParameters.Add("status", "Pending");
this.dataSource.UpdateParameters.Add(new Parameter("status"));
this.dataSource.UpdateParameters.Add(new Parameter("clientName"));
this.dataSource.UpdateParameters.Add(new Parameter("appID"));

this.gridView.DataSource = this.dataSource;
this.gridView.DataBind();
}
}
</code>

Regards,

Aaron

On Dec 19 2007, 10:47 am, Latish Sehgal <latish.seh...@gmail.com>
wrote:

On Dec 19 2007, 10:47 am, Latish Sehgal <latish.seh...@gmail.com>
wrote: