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

<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