Bulk inserting data from Gridview -> MySQL database

Hi all

I'm currently creating a web application that will allow reading and editing of data from a MySQL database.  So far, I have written the code (with assistance!) that will read the data into a Gridview.  The gridview includes a number of radio buttons and a text box for each row.

I require assistance in writing the code that will write back any changed rows as new rows in the database.  

I've had a look online, and managed to find a Microsoft walkthrough, but I'm having trouble working out how to cater for the writing back of data from the radio buttons.

Thanks in advance

Kathryn

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
    protected void GridView1_SelectedIndexChanged1(object sender, EventArgs e)
    {

    }
    private bool tableCopied = false;
    private DataTable originalDataTable;

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
                   if (!tableCopied)
                {
                        originalDataTable = ((DataRowView)e.Row.DataItem).Row.Table.Copy();
                        ViewState["originalValuesDataTable"] = originalDataTable;
                        tableCopied = true;
                }
        }
protected void UpdateButton_Click(object sender, EventArgs e)
{
    originalDataTable = (DataTable)ViewState["originalValuesDataTable"];

    foreach (GridViewRow r in GridView1.Rows)
        if (IsRowModified(r)) { GridView1.UpdateRow(r.RowIndex, false); }

    // Rebind the Grid to repopulate the original values table.
    tableCopied = false;
    GridView1.DataBind();
}

///protected bool IsRowModified(GridViewRow r)
///{
    ///int currentID;
    ///string currentTitleOfCourtesy;
    ///string currentLastName;
    ///string currentFirstName;
    ///string currentTitle;
    ///string currentExtension;

    ///currentID = Convert.ToInt32(GridView1.DataKeys[0].Value);

    ///currentTitleOfCourtesy = ((TextBox)r.FindControl("TitleOfCourtesyTextBox")).Text;
    ///currentLastName = ((TextBox)r.FindControl("LastNameTextBox")).Text;
    ///currentFirstName = ((TextBox)r.FindControl("FirstNameTextBox")).Text;
    ///currentTitle = ((TextBox)r.FindControl("TitleTextBox")).Text;
    ///currentExtension = ((TextBox)r.FindControl("ExtensionTextBox")).Text;

    ///DataRow row =
       /// originalDataTable.Select(String.Format("EmployeeID = {0}", currentID))[0];

    ///if (!currentTitleOfCourtesy.Equals(row["TitleOfCourtesy"].ToString())) { return true; }
    ///if (!currentLastName.Equals(row["LastName"].ToString())) { return true; }
    ///if (!currentFirstName.Equals(row["FirstName"].ToString())) { return true; }
    ///if (!currentTitle.Equals(row["Title"].ToString())) { return true; }
    ///if (!currentExtension.Equals(row["Extension"].ToString())) { return true; }

    ///return false;
///}

Open in new window

html-code.txt
k1ss0ffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

techExtremeCommented:
Hi, follow this tutorial
http://www.codeproject.com/KB/aspnet/InsertingWithGridView.aspx
which shows how to easily display, update, edit and delete the items.

If you want to use objectdatasource only as in your present code, follow this blog post
http://csharpdotnetfreak.blogspot.com/2009/06/gridview-objectdatasource-insert-update.html
which uses the same principle.
Happy Coding!
0
k1ss0ffAuthor Commented:
Thanks for that, but neither of those tutorials deal with my question which specifically concerns using the Gridview to edit radio buttons, and save back the results as new rows in the table?
0
k1ss0ffAuthor Commented:
Having not had much luck with the answer to the above question, I've now simplified the example - I've updated the code following a Microsoft walkthrough, but am now still having problems updating the data.

When I click the Update button, I'm getting the following error message against this line of code:

 >>   currenttocid = Convert.ToInt32((TextBox)r.FindControl("TOCID"));

"Unable to cast object of type 'System.Web.UI.WebControls.TextBox' to type 'System.IConvertible'."

What I'm trying to do is update the table tocs, with any rows that have new TOCnames or the column trash has changed. tocid is the primarykey for the tocs table.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.Adapters;
using System.Web.UI.WebControls.WebParts;
using System.Data;

public partial class AddTOC : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void GridView3_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
    protected void GridView3(object sender, EventArgs e)
    {

    }
    private bool tableCopied = false;
    private DataTable originalDataTable;
    protected void GridView3_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
            if (!tableCopied)
            {
                originalDataTable =
                    ((DataRowView)e.Row.DataItem).Row.Table.Copy();
                ViewState["OriginalValuesDataTable"] = originalDataTable;
                tableCopied = true;
            }
    }
    protected void UpdateButton_Click(object sender, EventArgs e)
    {
        originalDataTable = (DataTable)ViewState["originalValuesDataTable"];

        foreach (GridViewRow r in Gridview3.Rows)
 if (IsRowModified(r)) 
        { Gridview3.UpdateRow(r.RowIndex, false); }
        //Rebind the Grid to repopulate the original values table
        tableCopied = false;
        Gridview3.DataBind();
    }
    protected bool IsRowModified(GridViewRow r)
{
    int currenttocid;
    string currenttocname;
    int currenttrash;

    currenttocid = Convert.ToInt32((TextBox)r.FindControl("TOCID"));
    currenttocname = ((TextBox)r.FindControl("TOCNAME")).Text;
    currenttrash = Convert.ToInt32((RadioButton)r.FindControl("Trash1"));
        
    DataRow row =
        originalDataTable.Select(String.Format("tocid = {0}",currenttocid))[0];

    if
        (!currenttocname.Equals(row["tocname"].ToString())) {return true;}
    if
        (!currenttrash.Equals(row["trash"].ToString())) {return true;}
    return false;
}
}

Open in new window

0
k1ss0ffAuthor Commented:
Have managed to work this out myself, solution was to use RadioButton FindControl and .checked as below:

cmd.Parameters.Add("?", OdbcType.Int).Value = (((RadioButton)GridView2.Rows[i].FindControl("edYes")).Checked)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.