I'm trying to use a DataSet to maintain some rows for a table, and when I've finished my changes, send all changes to the database using a SqlDataAdapter.
I find if there are no rows in the table in the database then I am getting a 'Object reference not set to an instance of an object' when I try to access the table in the Dataset.
Is there a way to work with a Dataset like this ie. I start off with an empty table and I wish to add rows, to access the structure of the table rows, build rows, then add them and do the update on the SQLDataAdapter.
Thanks,
Sinead
Here is my code:
protected SqlDataAdapter memberDA = new SqlDataAdapter();
protected DataSet memberDS
{
get
{
if (ViewState["memberDS"] != null)
return (DataSet)ViewState["memberDS"];
else
return new DataSet();
}
set
{
ViewState["memberDS"] = value;
}
}
protected SqlDataAdapter getDataAdapterForMembers()
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["SiteDBConn"].ConnectionString;
memberDA = new SqlDataAdapter("usp_GetMembers", conn);
memberDA.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder cb = new SqlCommandBuilder(memberDA);
return memberDA;
}
protected void onClick_AddMember(object sender, EventArgs e)
{
SqlDataAdapter sqlDA = getDataAdapterForMembers();
sqlDA.FillSchema(memberDS, SchemaType.Source, "Member");
sqlDA.Fill(memberDS, "Member");
DataTable localMemDT = new DataTable(); //use to create FromRow and bind to repeater
localMemDT = herdMemberDS.Tables["Member"].Copy();
localMemDT.Clear();
try
{
DataRow fromRow = localMemDT.NewRow();
DataRow toRow = memberDS.Tables["Member"].NewRow();
//
// Do work on from row
//
//copy from -> to
toRow.ItemArray = fromRow.ItemArray;
//add row and accept changes
localMemDT.Rows.Add(fromRow);
localMemDT.AcceptChanges();
//add row and do not accept changes
memberDS.Tables["Member"].Rows.Add(toRow);
//int i = localMemDT.Rows.Count;
}
catch (Exception ex)
{
}
repMembers.DataSource = localMemDT;
repMembers.DataBind();
}
Just a follow up to this post as I left it without any conclusion.
I ended up using table adaptors to facilitate wat I was tring to do. I was reluctant to give up my dataset/dataadaptor solution as I am fairly familiar with them and table adaptors were a learning curve.
Bottom line if you are trying to do maintain changes in memory and then commit to database - save yourself some time and energy and try table adaptors.
Marked as answer by Sinead on Sep 08, 2010 01:13 PM
Sinead
Member
1 Points
17 Posts
DataAdaptor/Dataset problems when no row present in database tables
Apr 21, 2010 12:03 PM|LINK
Hi All,
I'm trying to use a DataSet to maintain some rows for a table, and when I've finished my changes, send all changes to the database using a SqlDataAdapter.
I find if there are no rows in the table in the database then I am getting a 'Object reference not set to an instance of an object' when I try to access the table in the Dataset.
Is there a way to work with a Dataset like this ie. I start off with an empty table and I wish to add rows, to access the structure of the table rows, build rows, then add them and do the update on the SQLDataAdapter.
Thanks,
Sinead
Here is my code:
protected SqlDataAdapter memberDA = new SqlDataAdapter(); protected DataSet memberDS { get { if (ViewState["memberDS"] != null) return (DataSet)ViewState["memberDS"]; else return new DataSet(); } set { ViewState["memberDS"] = value; } } protected SqlDataAdapter getDataAdapterForMembers() { SqlConnection conn = new SqlConnection(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["SiteDBConn"].ConnectionString; memberDA = new SqlDataAdapter("usp_GetMembers", conn); memberDA.SelectCommand.CommandType = CommandType.StoredProcedure; SqlCommandBuilder cb = new SqlCommandBuilder(memberDA); return memberDA; } protected void onClick_AddMember(object sender, EventArgs e) { SqlDataAdapter sqlDA = getDataAdapterForMembers(); sqlDA.FillSchema(memberDS, SchemaType.Source, "Member"); sqlDA.Fill(memberDS, "Member"); DataTable localMemDT = new DataTable(); //use to create FromRow and bind to repeater localMemDT = herdMemberDS.Tables["Member"].Copy(); localMemDT.Clear(); try { DataRow fromRow = localMemDT.NewRow(); DataRow toRow = memberDS.Tables["Member"].NewRow(); // // Do work on from row // //copy from -> to toRow.ItemArray = fromRow.ItemArray; //add row and accept changes localMemDT.Rows.Add(fromRow); localMemDT.AcceptChanges(); //add row and do not accept changes memberDS.Tables["Member"].Rows.Add(toRow); //int i = localMemDT.Rows.Count; } catch (Exception ex) { } repMembers.DataSource = localMemDT; repMembers.DataBind(); }Sinead
Member
1 Points
17 Posts
Re: DataAdaptor/Dataset problems when no row present in database tables
Apr 21, 2010 01:55 PM|LINK
OK Apologies - this doesn't seem to have anything to do with no rows in the database. :o/
When I changed my declaration of my Dataset (memberDS )and removed it from the Viewstate I stopped getting the error.
Can anyone see anything wrong with how I am adding the Dataset to the viewstate in the original code?
And am I correct in thinking I need to put the DataSet in the Viewstate?
Thanks
Sinead
Member
1 Points
17 Posts
Re: DataAdaptor/Dataset problems when no row present in database tables
Sep 08, 2010 01:13 PM|LINK
Just a follow up to this post as I left it without any conclusion.
I ended up using table adaptors to facilitate wat I was tring to do. I was reluctant to give up my dataset/dataadaptor solution as I am fairly familiar with them and table adaptors were a learning curve.
Bottom line if you are trying to do maintain changes in memory and then commit to database - save yourself some time and energy and try table adaptors.