Big DataGrid Update Problem-MVP Help Please????

[steroche]

I would REALLY appreciate help please please please! Im sure it is
probably blindingly obvious to most of you but I am totally in the dark
here!I am lost - i thought i had finally figured out this dataSet
updating lark when i realised that i think i am right back at square
1!!!
Here's my scenario - i have a SQLDB and i retrieve all my data from
that into a dataset and display this to a datagrid(WebForm). I have got
this grid sorted and paged and all but i cannot update records. When i
try I always get the error "There is no row at position" x. Do i need
to create a DataTable when i create the dataset? I thought i could
handle everything through the dataset?? If so how do i go about doing
this???Do i have to somewhow cast all the rows from the dataset or
what?

Can somebody please help?? Code to follow:



public void fillperson()
{
mySQLConnection sqlConn = new mySQLConnection();
sqlComm_person = new SqlCommand();
sqlComm_person.Connection = sqlConn.GetConnection();
sqlComm_person.CommandText = "proc_getpersonDetailswithName";
sqlComm_person.CommandType = CommandType.StoredProcedure;


SqlDataAdapter daperson = new SqlDataAdapter(sqlComm_person);
sqlConn.GetConnection().Open();
daperson.Fill(dsperson, "person");


dsperson.Tables[0].TableName = "person";

Cache["personData"] = dsperson;
Session["personData"] = dsperson;
sqlConn.GetConnection().Close();

}


private void BindData(DataGrid dg, string name)
{

// Bind the dsperson source
dg.DataSource = (DataSet) Cache[name];
dg.DataSource = (DataSet) Session[name];

// Bind the data
dg.DataBind();

}
private void dgperson_UpdateCommand(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
string personid, newOrderID,newLname,newCompanyID,newnotes,newArea =
null;
string Order_ID,Company_ID,notes,Current_Area,person_ID = null;
TextBox tb;


tb =
(TextBox)(dgperson.Items[dgperson.EditItemIndex].FindControl("txtEditperson_ID"));
personid = tb.Text;
tb =
(TextBox)(dgperson.Items[dgperson.EditItemIndex].FindControl("txtEditOrder"));
newOrderID = tb.Text;
tb =
(TextBox)(dgperson.Items[dgperson.EditItemIndex].FindControl("txtEditCompany"));
newCompanyID = tb.Text;
tb =
(TextBox)(dgperson.Items[dgperson.EditItemIndex].FindControl("txtEditnotes"));
newnotes = tb.Text;
tb =
(TextBox)(dgperson.Items[dgperson.EditItemIndex].FindControl("txtEditArea"));
newArea = tb.Text;

mySQLConnection sqlConn = new mySQLConnection();
sqlComm_Updateperson = new SqlCommand();

sqlComm_Updateperson.Connection = sqlConn.GetConnection();
sqlComm_Updateperson.CommandText =
"proc_personUpdateCommand_restrict";
sqlComm_Updateperson.CommandType = CommandType.StoredProcedure;


//all stored procedure go in here - these are all ok i think
//

SqlDataAdapter daUpdateperson = new
SqlDataAdapter(sqlComm_Updateperson);

daUpdateperson.Fill(dsperson, "person");
int count = 0;
count = dsperson.Tables[0].Rows.Count;
Cache["personData"] = dsperson;
Session["personData"] = dsperson;

this.dsperson.Tables["person"].Rows[e.Item.ItemIndex][2] =
newOrderID;
this.dsperson.Tables["person"].Rows[e.Item.ItemIndex][3] =
newCompanyID;
this.dsperson.Tables["person"].Rows[e.Item.ItemIndex][4] = newnotes;
this.dsperson.Tables["person"].Rows[e.Item.ItemIndex][5] = newArea;

this.daperson.Update(dsperson, "person");

dgperson.EditItemIndex = -1;

// Refreshes the grid
BindData(this.dgperson, "personData");
}



Can somebody please tell me where i am going wrong???
If theres some syntactical errors they are probably a mistake when
posting as i had to change my column names for privacy.
Thanks
Steve

 

[Guest]

Hi Steve,

The error "There is no row at position x" that you get is probably caused by
your usage of the datagrid row item index as a way to find the changed row in
the table. If all your table data is displayed on one page on the grid, this
might work but if your grid has more than one page then the DataTable row
index will be different than that of the grid Item.ItemIndex.

You are better off creating a primary key on your table that you use to
match the updated row on the grid as I showed you in another example.
http://www.societopia.com/samples/datagrid_3.aspx

There are also a few things in your code that you should notice:

1- When you use the entire dataset as the datasource you are actually using
the only DataTable in it. (It just happend that you have one DataTable in
this case, but the dataset can hold several datatables) Your code would look
clearer if you were to write:

DataTable dt;
DataSet ds = (DataSet) Session[name];
if (ds !=null)
{
dt = ds.Tables["personData"];
dg.DataSource = dt;
}

2- You are persisting your dataset by saving it in both the cache and the
session objects, you only need to save to one of them. The session refers to
a particular user session whereas the cache is to share data between all
users sessions within the application.

HTH,
http://www.societopia.net
http://www.webswapp.com

I would REALLY appreciate help please please please! Im sure it is
probably blindingly obvious to most of you but I am totally in the dark
here!I am lost - i thought i had finally figured out this dataSet
updating lark when i realised that i think i am right back at square
1!!!
Here's my scenario - i have a SQLDB and i retrieve all my data from
that into a dataset and display this to a datagrid(WebForm). I have got
this grid sorted and paged and all but i cannot update records. When i
try I always get the error "There is no row at position" x. Do i need
to create a DataTable when i create the dataset? I thought i could
handle everything through the dataset?? If so how do i go about doing
this???Do i have to somewhow cast all the rows from the dataset or
what?

Can somebody please help?? Code to follow:



public void fillperson()
{
mySQLConnection sqlConn = new mySQLConnection();
sqlComm_person = new SqlCommand();
sqlComm_person.Connection = sqlConn.GetConnection();
sqlComm_person.CommandText = "proc_getpersonDetailswithName";
sqlComm_person.CommandType = CommandType.StoredProcedure;


SqlDataAdapter daperson = new SqlDataAdapter(sqlComm_person);
sqlConn.GetConnection().Open();
daperson.Fill(dsperson, "person");


dsperson.Tables[0].TableName = "person";

Cache["personData"] = dsperson;
Session["personData"] = dsperson;
sqlConn.GetConnection().Close();

}


private void BindData(DataGrid dg, string name)
{

// Bind the dsperson source
dg.DataSource = (DataSet) Cache[name];
dg.DataSource = (DataSet) Session[name];

// Bind the data
dg.DataBind();

}
private void dgperson_UpdateCommand(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
string personid, newOrderID,newLname,newCompanyID,newnotes,newArea =
null;
string Order_ID,Company_ID,notes,Current_Area,person_ID = null;
TextBox tb;


tb =
(TextBox)(dgperson.Items[dgperson.EditItemIndex].FindControl("txtEditperson_ID"));
personid = tb.Text;
tb =
(TextBox)(dgperson.Items[dgperson.EditItemIndex].FindControl("txtEditOrder"));
newOrderID = tb.Text;
tb =
(TextBox)(dgperson.Items[dgperson.EditItemIndex].FindControl("txtEditCompany"));
newCompanyID = tb.Text;
tb =
(TextBox)(dgperson.Items[dgperson.EditItemIndex].FindControl("txtEditnotes"));
newnotes = tb.Text;
tb =
(TextBox)(dgperson.Items[dgperson.EditItemIndex].FindControl("txtEditArea"));
newArea = tb.Text;

mySQLConnection sqlConn = new mySQLConnection();
sqlComm_Updateperson = new SqlCommand();

sqlComm_Updateperson.Connection = sqlConn.GetConnection();
sqlComm_Updateperson.CommandText =
"proc_personUpdateCommand_restrict";
sqlComm_Updateperson.CommandType = CommandType.StoredProcedure;


//all stored procedure go in here - these are all ok i think
//

SqlDataAdapter daUpdateperson = new
SqlDataAdapter(sqlComm_Updateperson);

daUpdateperson.Fill(dsperson, "person");
int count = 0;
count = dsperson.Tables[0].Rows.Count;
Cache["personData"] = dsperson;
Session["personData"] = dsperson;

this.dsperson.Tables["person"].Rows[e.Item.ItemIndex][2] =
newOrderID;
this.dsperson.Tables["person"].Rows[e.Item.ItemIndex][3] =
newCompanyID;
this.dsperson.Tables["person"].Rows[e.Item.ItemIndex][4] = newnotes;
this.dsperson.Tables["person"].Rows[e.Item.ItemIndex][5] = newArea;

this.daperson.Update(dsperson, "person");

dgperson.EditItemIndex = -1;

// Refreshes the grid
BindData(this.dgperson, "personData");
}



Can somebody please tell me where i am going wrong???
If theres some syntactical errors they are probably a mistake when
posting as i had to change my column names for privacy.
Thanks
Steve

 

[steroche]

Hi Philip,
Thanks alot for your reply. I will clean up my code now and give your
example @
http://www.webswapp.com/viewsource.aspx?file=\demos\datagrid_3.aspx a
shot.

Thanks for you help,
Steve

 

[steroche]

Hi Philip,
I'm trying to modify my code as you suggested:
private void BindData(DataGrid dg, string name)
{
DataTable dt;
DataRow dr;
DataSet ds = (DataSet) Session[name];
if(ds != null)
{
dt = ds.Tables[name];
dg.DataSource = dt;
dg.DataBind();
}

dg.DataBind();

}
However I don't see my datagrid any more. I don't think the datatable
dt is getting populated at dt = ds.Tables[name]????

Also i have been looking at your VB.net code at
http://www.societopia.com/samples/datagrid_3.aspx. Im not too familiar
with VB.net. How do i go about creating these primary keys in c#?All of
my tables have a primary key field so can i just use this?

 

[Guest]

Hi Steve,

You should replace the variable "name" with your actual table name, e.g. if
your tablename was "person" then you should write:
dt = ds.Tables["person"];

I put a simpler demo for you in C# to demonstrate the use a of primary key
on the Datatable http://www.societopia.net/samples/datagrid_1c.aspx

HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com