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

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(',');
            GridView1.DataSource = dt;
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

Kirtan Patel replied to aman on 13-Nov-11 06:08 AM
SqlConnection con = new SqlConnection("your Connection Stirng");
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);
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
    //execute query
    //close connection

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

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);


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 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();




For alternate way u can refer the following website
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"  >


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

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

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



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");


 SqlCommand cmd = new SqlCommand(InsertQuery, cn);






Follow this link also-