ASP.NET - how to insert the data to sql server from the gridview control

Asked By aman on 13-Nov-11 05:49 AM
hi all,

i want to insert the data to sql server from the gridview control in asp.net

below is how i have uploaded the gridview with the data.

        protected void Button1_Click(object sender, EventArgs e)
        {
            string filename= Path.GetFileName(FileUpload1.PostedFile.FileName);
            string[] str = File.ReadAllLines(Server.MapPath("") + filename);
            DataTable dt = new DataTable();
            string[] temp = str[0].Split(',');
            foreach (string t in temp)
            {
                dt.Columns.Add(t, typeof(string));
            }
            for (int i = 0; i < str.Length; i++)
            {
                string[] t = str[i].Split(',');
                dt.Rows.Add(t);
            }
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
data  is loaded successfully to the gridview control using the above control now i want to upload the loaded gridview data to the sql server table.
please help me with some goo example

thanks,
Kirtan Patel replied to aman on 13-Nov-11 06:08 AM
SqlConnection con = new SqlConnection("your Connection Stirng");
con.Open();
foreach (GridViewRow r in GridView1.Rows)
{
  string CellValue = r.Cells[0].Text;
  SqlCommand comm = new SqlCommand("INSERT INTO tablename(Colname) VALUES(@ColValue)", con);
  comm.Parameters.AddWithValue("@ColValue", CellValue);
  comm.ExecuteNonQuery();
}
con.Close();
 
dipa ahuja replied to aman on 13-Nov-11 06:09 AM
Try this way:

First Loop through all the rows of gridview and inside loop fire the insert Query:

void exportToSQL()
{
  SqlConnection conn = new SqlConnection("Connstring");
  SqlCommand comm;
  string q = "INSERT INTO Table1 name,gender,contact values(@name,@gender,@contact)";
  foreach (GridViewRow row in GridView1.Rows)
  {
    string name = ((Label)row.FindControl("label1")).Text.ToString();
    string gender = ((DropDownList)row.FindControl("dropdown1")).SelectedValue.ToString();
    string contact = ((Label)row.FindControl("lblcontact")).Text.ToString();
 
    comm = new SqlCommand(q, conn);
    comm.Parameters.AddWithValue("name", name);
    comm.Parameters.AddWithValue("gender", gender);
    comm.Parameters.AddWithValue("contact", contact);
 
    //open connection
    conn.Open();
 
    //execute query
    comm.ExecuteNonQuery();
 
    //close connection
    conn.Close();
  }
}

Devil Scorpio replied to aman on 13-Nov-11 06:31 AM
Hi,

Refer this code to insert the data to sql server from the gridview control

private void button1_Click(object sender, EventArgs e)

{

int i;

string conStr = Properties.Settings.Default.connStr;

SqlConnection con = new SqlConnection(conStr);

con.Open();

for (i = 0; i <= dataGridView1.Rows.Count - 1; i++)

{

//Here in this for loop you can access all the values of

//gridview and can store it in db by using ado.net like

string query = "insert into Table values(" + dataGridView1.Rows[i].Cells[0].Value + "," + dataGridView1.Rows[i].Cells[1].Value + ")";

SqlCommand cmd = new SqlCommand(query, con);

retval = cmd.ExecuteNonQuery();

}

con.Close();

}


For alternate way u can refer the following website
http://www.eggheadcafe.com/community/vb/14/10054280/how-to-insert-multiple-rows-from-gridview-in-databse.aspx
Jitendra Faye replied to aman on 13-Nov-11 11:47 PM

For that first you have to use foreach loop for getting all values of GridViews,

follow these steps-

Suppose this is your GridView-


<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="false"  >

<Columns>

<asp:BoundField DataField ="empid" HeaderText ="empid"/>

<asp:BoundField DataField ="empname" HeaderText ="empname"/>

<asp:BoundField DataField ="dept" HeaderText ="deptname"/>

</Columns>

</asp:GridView>

Then Write this code-


protected void ExportToDataBase_Click(object sender, EventArgs e)

{

foreach (DataRow r in GridView1.Rows)

{

 string strEmpId = r[0].ToString (); //FOR GETTING ENPID

 string strEmpName = r[1].ToString(); //FOR GETTING ENPNAME

 string strDept = r[2].ToString(); //FOR GETTING ENPDEPT


 string InsertQuery = "insert into emptable emp(empis,name,dept) values(' " + strEmpId + " ',' " + strEmpName + " ',' " + strDept + " ') ";

 SqlConnection cn = new SqlConnection("CONNECTION STRING");

 cn.Open();

 SqlCommand cmd = new SqlCommand(InsertQuery, cn);

 cmd.ExecuteNonQuery();

 cn.Close();

}

}

USE THIS CODE AND LET ME KNOW.



Follow this link also-

http://www.eggheadcafe.com/community/asp-net/17/10342524/how-to-store-large-data-from-gridview-to-database-in-single-click.aspx