Hi
Try this Code.
OleDbConnection con;
OleDbCommand com;
SqlCommand Cmmd;
SqlConnection Sqlcon;
string str;
protected void Page_Load(object sender, EventArgs e)
{
}
private DataTable dtBind()
{
DataTable dt = new DataTable();
com = new OleDbCommand();
con = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='d:\\test.xls';Extended Properties=Excel 8.0;");
con.Open();
com.Connection = con;
str = "Select * from [sheet1$]";
OleDbDataAdapter oleda = new OleDbDataAdapter(str, con);
oleda.Fill(dt);
return dt;
}
protected void Bt_Submit_Click(object sender, EventArgs e)
{
try
{
//DataTable dt1 = new DataTable();
//dt1 = dtBind();
//if (Convert.ToInt32(dt1.DefaultView[0][0]) == 1)
//{
DataTable dt=new DataTable ();
dt=dtBind();
if(dt.Rows.Count>0)
{
Cmmd = new SqlCommand();
Sqlcon = new SqlConnection("Your ConnectionString");
for (int i = 0; i <= dt.Rows.Count-1; i++)
{
Sqlcon.Open();
Cmmd.Connection = Sqlcon;
str = "Insert into [Table1] (A1,B2,C3,D4,E5,F6,G7,H8,I9,J10) values('" + dt.DefaultView[0][0] + "','" + dt.DefaultView[0][1] + "','" + dt.DefaultView[0][2] + "','" + dt.DefaultView[0][3] + "','" + dt.DefaultView[0][4] + "','" + dt.DefaultView[0][5] + "','" + dt.DefaultView[0][6] + "','" + dt.DefaultView[0][7] + "','" + dt.DefaultView[0][8] + "','" + dt.DefaultView[0][9] + "')";
Cmmd.CommandText = str;
Cmmd.ExecuteNonQuery();
Sqlcon.Close();
}
for (int j = 0; j <= dt.Rows.Count - 1; j++)
{
Sqlcon.Open();
Cmmd.Connection = Sqlcon;
str = "Insert into [Table2] (K11,L12,M13,N14,O15,P16,Q17,R18,S19,T20) values('" + dt.DefaultView[0][10] + "','" + dt.DefaultView[0][11] + "','" + dt.DefaultView[0][12] + "','" + dt.DefaultView[0][13] + "','" + dt.DefaultView[0][14] + "','" + dt.DefaultView[0][15] + "','" + dt.DefaultView[0][16] + "','" + dt.DefaultView[0][17] + "','" + dt.DefaultView[0][18] + "','" + dt.DefaultView[0][19] + "')";
Cmmd.CommandText = str;
Cmmd.ExecuteNonQuery();
Sqlcon.Close();
}
}
//}
}
catch (Exception ex)
{
}
}
}
Mark as Answer if its helpful to you
Kumaraspcode2009@gmail.com
Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator