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

Discussion in 'ASP .Net Datagrid Control' started by steroche, Aug 25, 2005.

  1. steroche

    steroche Guest

    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, Aug 25, 2005
    #1
    1. Advertising

  2. 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


    "steroche" wrote:

    > 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
    >
    >
    Phillip Williams, Aug 25, 2005
    #2
    1. Advertising

  3. steroche

    steroche Guest

    steroche, Aug 25, 2005
    #3
  4. steroche

    steroche Guest

    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?
    steroche, Aug 25, 2005
    #4
  5. 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


    "steroche" wrote:

    > 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?
    >
    >
    Phillip Williams, Aug 25, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. steroche
    Replies:
    4
    Views:
    608
    =?Utf-8?B?UGhpbGxpcCBXaWxsaWFtcw==?=
    Aug 25, 2005
  2. Shaguf
    Replies:
    0
    Views:
    351
    Shaguf
    Dec 24, 2008
  3. Shaguf
    Replies:
    0
    Views:
    451
    Shaguf
    Dec 26, 2008
  4. steroche

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

    steroche, Aug 25, 2005, in forum: ASP .Net Web Controls
    Replies:
    4
    Views:
    111
    Phillip Williams
    Aug 25, 2005
  5. Steven Burn

    Warning: MVP.org is not a Microsoft MVP site

    Steven Burn, Jan 10, 2004, in forum: ASP General
    Replies:
    7
    Views:
    168
    Jeff Cochran
    Jan 12, 2004
Loading...

Share This Page