?
Solved

Bulk inserting data from Gridview -> MySQL database

Posted on 2010-01-09
4
Medium Priority
?
584 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:k1ss0ff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 12

Expert Comment

by:techExtreme
ID: 26273061
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
 

Author Comment

by:k1ss0ff
ID: 26273436
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
 

Author Comment

by:k1ss0ff
ID: 26278028
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
 

Accepted Solution

by:
k1ss0ff earned 0 total points
ID: 26454000
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question