DataGrid - updating with SQL Server based DataSet issues

  • Thread starter Thread starter st
  • Start date Start date
S

st

Hi,


I'm new to ASP.NET in general and just getting to grips with C# and web

controls.


So far I've a simple form with a DataGrid that is populated by a
DataSet that is itself populated by a SQL Server table.


The table contents are rendered and so far, so good.


My next step was to create edit button columns and wire up edit events.

I then attempted to persist the DataSet and DataAdapter across
postbacks by assigning to Session, finally adding code to call Update
against the DataAdapter in the DataGrid update event.


Here's where I run into a problem, because, no matter how I rearrange
the code, the table fails to update and the unchanged table state is
given back at the next grid fill.


Checking the DataSet table contents at various stages shows that its
contents also never seem to relect the edit. I thought the DataSet,
being bound to the DataGrid, would change in line with edits? Do I have

to take the contents of the edited grid, change them at the appropriate

points in the DataSet and then call the update against the DataAdapter?



No compilation/run-time errors, by the way - just not able to update.


Thank you.


Simon


Here's my code:


public class Maint : System.Web.UI.Page
{
protected System.Data.SqlClient.SqlConne­ction
sqlConnection1;
protected System.Web.UI.WebControls.Butt­on Button1;
protected System.Web.UI.WebControls.Data­Grid
dgMasterGeneric;


protected String stringTable;
protected DataSet dsMaster;
//private SqlCommand sqlMaster;
protected SqlDataAdapter daMaster;


private void Page_Load(object sender, System.EventArgs
e)
// Put user code to initialize the page here
{
stringTable = Request.QueryString["table"];
if (!IsPostBack)
//DB read to grid
BindMasterGeneric(false);
else
{
daMaster =
(SqlDataAdapter)Session["da"];
dsMaster = (DataSet)Session["ds"];
};
}


private void Page_Unload(object sender, System.EventArgs e)
{
//Add the data store to the session
Session["da"]=daMaster;
Session["ds"]=dsMaster;
}


public void BindMasterGeneric(bool boolPersist)
{
//if (stringTable == string.Empty) return;
if (!boolPersist)
{
dsMaster = new DataSet();
daMaster = new SqlDataAdapter("SELECT * FROM "
+
stringTable,sqlConnection1);
SqlCommandBuilder commandBuilder = new
SqlCommandBuilder(daMaster);
}
daMaster.Fill(dsMaster,stringT­able);
dgMasterGeneric.DataSource = dsMaster;
dgMasterGeneric.DataBind();
}


public void Update_MasterGeneric(object source,
System.Web.UI.WebControls.Data­GridCommandEventArgs e)
{
//The clicked/edited row is derived from e
dgMasterGeneric.EditItemIndex =
e.Item.ItemIndex;
daMaster.Update(dsMaster,strin­gTable);
//Redisplay the data
BindMasterGeneric(true);
}
 
In ASP.NET (unlike Windows forms) you are responsible for moving the edits
from the bound control back into the DataTable--that is until ADO.NET 2.0
which can do this for you. I would pick up a copy of Rob MacDonald's book on
ASP programming (my daughter Fred edited it)--it has helped me a great deal.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


Hi,


I'm new to ASP.NET in general and just getting to grips with C# and web

controls.


So far I've a simple form with a DataGrid that is populated by a
DataSet that is itself populated by a SQL Server table.


The table contents are rendered and so far, so good.


My next step was to create edit button columns and wire up edit events.

I then attempted to persist the DataSet and DataAdapter across
postbacks by assigning to Session, finally adding code to call Update
against the DataAdapter in the DataGrid update event.


Here's where I run into a problem, because, no matter how I rearrange
the code, the table fails to update and the unchanged table state is
given back at the next grid fill.


Checking the DataSet table contents at various stages shows that its
contents also never seem to relect the edit. I thought the DataSet,
being bound to the DataGrid, would change in line with edits? Do I have

to take the contents of the edited grid, change them at the appropriate

points in the DataSet and then call the update against the DataAdapter?



No compilation/run-time errors, by the way - just not able to update.


Thank you.


Simon


Here's my code:


public class Maint : System.Web.UI.Page
{
protected System.Data.SqlClient.SqlConne­ction
sqlConnection1;
protected System.Web.UI.WebControls.Butt­on Button1;
protected System.Web.UI.WebControls.Data­Grid
dgMasterGeneric;


protected String stringTable;
protected DataSet dsMaster;
//private SqlCommand sqlMaster;
protected SqlDataAdapter daMaster;


private void Page_Load(object sender, System.EventArgs
e)
// Put user code to initialize the page here
{
stringTable = Request.QueryString["table"];
if (!IsPostBack)
//DB read to grid
BindMasterGeneric(false);
else
{
daMaster =
(SqlDataAdapter)Session["da"];
dsMaster = (DataSet)Session["ds"];
};
}


private void Page_Unload(object sender, System.EventArgs e)
{
//Add the data store to the session
Session["da"]=daMaster;
Session["ds"]=dsMaster;
}


public void BindMasterGeneric(bool boolPersist)
{
//if (stringTable == string.Empty) return;
if (!boolPersist)
{
dsMaster = new DataSet();
daMaster = new SqlDataAdapter("SELECT * FROM "
+
stringTable,sqlConnection1);
SqlCommandBuilder commandBuilder = new
SqlCommandBuilder(daMaster);
}
daMaster.Fill(dsMaster,stringT­able);
dgMasterGeneric.DataSource = dsMaster;
dgMasterGeneric.DataBind();
}


public void Update_MasterGeneric(object source,
System.Web.UI.WebControls.Data­GridCommandEventArgs e)
{
//The clicked/edited row is derived from e
dgMasterGeneric.EditItemIndex =
e.Item.ItemIndex;
daMaster.Update(dsMaster,strin­gTable);
//Redisplay the data
BindMasterGeneric(true);
}
 
St,
Check out this article...it might help also.

http://www.vb2themax.com/ExtLink.as...www.developer.com/net/asp/article.php/2210191

Regards,
Dave

st said:
Hi,


I'm new to ASP.NET in general and just getting to grips with C# and web

controls.


So far I've a simple form with a DataGrid that is populated by a
DataSet that is itself populated by a SQL Server table.


The table contents are rendered and so far, so good.


My next step was to create edit button columns and wire up edit events.

I then attempted to persist the DataSet and DataAdapter across
postbacks by assigning to Session, finally adding code to call Update
against the DataAdapter in the DataGrid update event.


Here's where I run into a problem, because, no matter how I rearrange
the code, the table fails to update and the unchanged table state is
given back at the next grid fill.


Checking the DataSet table contents at various stages shows that its
contents also never seem to relect the edit. I thought the DataSet,
being bound to the DataGrid, would change in line with edits? Do I have

to take the contents of the edited grid, change them at the appropriate

points in the DataSet and then call the update against the DataAdapter?



No compilation/run-time errors, by the way - just not able to update.


Thank you.


Simon


Here's my code:


public class Maint : System.Web.UI.Page
{
protected System.Data.SqlClient.SqlConne­ction
sqlConnection1;
protected System.Web.UI.WebControls.Butt­on Button1;
protected System.Web.UI.WebControls.Data­Grid
dgMasterGeneric;


protected String stringTable;
protected DataSet dsMaster;
//private SqlCommand sqlMaster;
protected SqlDataAdapter daMaster;


private void Page_Load(object sender, System.EventArgs
e)
// Put user code to initialize the page here
{
stringTable = Request.QueryString["table"];
if (!IsPostBack)
//DB read to grid
BindMasterGeneric(false);
else
{
daMaster =
(SqlDataAdapter)Session["da"];
dsMaster = (DataSet)Session["ds"];
};
}


private void Page_Unload(object sender, System.EventArgs e)
{
//Add the data store to the session
Session["da"]=daMaster;
Session["ds"]=dsMaster;
}


public void BindMasterGeneric(bool boolPersist)
{
//if (stringTable == string.Empty) return;
if (!boolPersist)
{
dsMaster = new DataSet();
daMaster = new SqlDataAdapter("SELECT * FROM "
+
stringTable,sqlConnection1);
SqlCommandBuilder commandBuilder = new
SqlCommandBuilder(daMaster);
}
daMaster.Fill(dsMaster,stringT­able);
dgMasterGeneric.DataSource = dsMaster;
dgMasterGeneric.DataBind();
}


public void Update_MasterGeneric(object source,
System.Web.UI.WebControls.Data­GridCommandEventArgs e)
{
//The clicked/edited row is derived from e
dgMasterGeneric.EditItemIndex =
e.Item.ItemIndex;
daMaster.Update(dsMaster,strin­gTable);
//Redisplay the data
BindMasterGeneric(true);
}
Click to expand...
 
Bill,

Thanks.

After reading your comment, I started looking at updating the DataSet
with DataGrid changes prior to writing back to the database when I came
across this article:
http://www.dotnet247.com/247reference/msgs/19/95499.aspx

It seems to contradict what you are saying. Actually, given what I've
read so far, I was very much under the impression that the bound
DataSet mirrors the DataGrid state - it just needs to pesist across
postbacks.

Sorry to be picky but are you sure manual DataSet updates are required?
The contents, field names and field count of my DataGrid's DataSet is
changeable so I'd prefer a simple update method!

Many thanks,

Simon
In ASP.NET (unlike Windows forms) you are responsible for moving the edits
from the bound control back into the DataTable--that is until ADO.NET 2.0
which can do this for you. I would pick up a copy of Rob MacDonald's bookon
ASP programming (my daughter Fred edited it)--it has helped me a great deal.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


Hi,


I'm new to ASP.NET in general and just getting to grips with C# and web

controls.


So far I've a simple form with a DataGrid that is populated by a
DataSet that is itself populated by a SQL Server table.


The table contents are rendered and so far, so good.


My next step was to create edit button columns and wire up edit events.

I then attempted to persist the DataSet and DataAdapter across
postbacks by assigning to Session, finally adding code to call Update
against the DataAdapter in the DataGrid update event.


Here's where I run into a problem, because, no matter how I rearrange
the code, the table fails to update and the unchanged table state is
given back at the next grid fill.


Checking the DataSet table contents at various stages shows that its
contents also never seem to relect the edit. I thought the DataSet,
being bound to the DataGrid, would change in line with edits? Do I have

to take the contents of the edited grid, change them at the appropriate

points in the DataSet and then call the update against the DataAdapter?



No compilation/run-time errors, by the way - just not able to update.


Thank you.


Simon


Here's my code:


public class Maint : System.Web.UI.Page
{
protected System.Data.SqlClient.SqlConne­ction
sqlConnection1;
protected System.Web.UI.WebControls.Butt­on Button1;
protected System.Web.UI.WebControls.Data­Grid
dgMasterGeneric;


protected String stringTable;
protected DataSet dsMaster;
//private SqlCommand sqlMaster;
protected SqlDataAdapter daMaster;


private void Page_Load(object sender, System.EventArgs
e)
// Put user code to initialize the page here
{
stringTable = Request.QueryString["table"];
if (!IsPostBack)
//DB read to grid
BindMasterGeneric(false);
else
{
daMaster =
(SqlDataAdapter)Session["da"];
dsMaster = (DataSet)Session["ds"];
};
}


private void Page_Unload(object sender, System.EventArgs e)
{
//Add the data store to the session
Session["da"]=daMaster;
Session["ds"]=dsMaster;
}


public void BindMasterGeneric(bool boolPersist)
{
//if (stringTable == string.Empty) return;
if (!boolPersist)
{
dsMaster = new DataSet();
daMaster = new SqlDataAdapter("SELECT * FROM "
+
stringTable,sqlConnection1);
SqlCommandBuilder commandBuilder = new
SqlCommandBuilder(daMaster);
}
daMaster.Fill(dsMaster,stringT­able);
dgMasterGeneric.DataSource = dsMaster;
dgMasterGeneric.DataBind();
}


public void Update_MasterGeneric(object source,
System.Web.UI.WebControls.Data­GridCommandEventArgs e)
{
//The clicked/edited row is derived from e
dgMasterGeneric.EditItemIndex =
e.Item.ItemIndex;
daMaster.Update(dsMaster,strin­gTable);
//Redisplay the data
BindMasterGeneric(true);
}
Click to expand...
 
I think if you look closely, you'll see that the article is about Windows
Forms (winforms)--not ASP. In Win forms, datagrid changes are posted
directly to the underling bound table.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


Bill,

Thanks.

After reading your comment, I started looking at updating the DataSet
with DataGrid changes prior to writing back to the database when I came
across this article:
http://www.dotnet247.com/247reference/msgs/19/95499.aspx

It seems to contradict what you are saying. Actually, given what I've
read so far, I was very much under the impression that the bound
DataSet mirrors the DataGrid state - it just needs to pesist across
postbacks.

Sorry to be picky but are you sure manual DataSet updates are required?
The contents, field names and field count of my DataGrid's DataSet is
changeable so I'd prefer a simple update method!

Many thanks,

Simon
In ASP.NET (unlike Windows forms) you are responsible for moving the edits
from the bound control back into the DataTable--that is until ADO.NET 2.0
which can do this for you. I would pick up a copy of Rob MacDonald's book
on
ASP programming (my daughter Fred edited it)--it has helped me a great
deal.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________


Hi,


I'm new to ASP.NET in general and just getting to grips with C# and web

controls.


So far I've a simple form with a DataGrid that is populated by a
DataSet that is itself populated by a SQL Server table.


The table contents are rendered and so far, so good.


My next step was to create edit button columns and wire up edit events.

I then attempted to persist the DataSet and DataAdapter across
postbacks by assigning to Session, finally adding code to call Update
against the DataAdapter in the DataGrid update event.


Here's where I run into a problem, because, no matter how I rearrange
the code, the table fails to update and the unchanged table state is
given back at the next grid fill.


Checking the DataSet table contents at various stages shows that its
contents also never seem to relect the edit. I thought the DataSet,
being bound to the DataGrid, would change in line with edits? Do I have

to take the contents of the edited grid, change them at the appropriate

points in the DataSet and then call the update against the DataAdapter?



No compilation/run-time errors, by the way - just not able to update.


Thank you.


Simon


Here's my code:


public class Maint : System.Web.UI.Page
{
protected System.Data.SqlClient.SqlConne­ction
sqlConnection1;
protected System.Web.UI.WebControls.Butt­on Button1;
protected System.Web.UI.WebControls.Data­Grid
dgMasterGeneric;


protected String stringTable;
protected DataSet dsMaster;
//private SqlCommand sqlMaster;
protected SqlDataAdapter daMaster;


private void Page_Load(object sender, System.EventArgs
e)
// Put user code to initialize the page here
{
stringTable = Request.QueryString["table"];
if (!IsPostBack)
//DB read to grid
BindMasterGeneric(false);
else
{
daMaster =
(SqlDataAdapter)Session["da"];
dsMaster = (DataSet)Session["ds"];
};
}


private void Page_Unload(object sender, System.EventArgs e)
{
//Add the data store to the session
Session["da"]=daMaster;
Session["ds"]=dsMaster;
}


public void BindMasterGeneric(bool boolPersist)
{
//if (stringTable == string.Empty) return;
if (!boolPersist)
{
dsMaster = new DataSet();
daMaster = new SqlDataAdapter("SELECT * FROM "
+
stringTable,sqlConnection1);
SqlCommandBuilder commandBuilder = new
SqlCommandBuilder(daMaster);
}
daMaster.Fill(dsMaster,stringT­able);
dgMasterGeneric.DataSource = dsMaster;
dgMasterGeneric.DataBind();
}


public void Update_MasterGeneric(object source,
System.Web.UI.WebControls.Data­GridCommandEventArgs e)
{
//The clicked/edited row is derived from e
dgMasterGeneric.EditItemIndex =
e.Item.ItemIndex;
daMaster.Update(dsMaster,strin­gTable);
//Redisplay the data
BindMasterGeneric(true);
}
Click to expand...
 
Back
Top