Help Regarding import data from ms-excel

Question

Hi,

I want import data from Ms-Excel.In my Excel have two columns like RollNumber and Name.If I click the Import button means ,it will upload in my sql server. what can I do.

Thanks & Regards,

Gokilavasan.M

Satyapriya Nayak · Answer

Hi Gokilavasan, Try this... Untitled Page Insert Data         using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.IO; using System.Data.OleDb; using System.Data.SqlClient; using System.Text; namespace Excel_data_gridview_db1 { public partial class _Default : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; protected void insertdata_Click(object sender, EventArgs e) { OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("test.xls") + ";Extended Properties=Excel 8.0"); OleDbCommand com = new OleDbCommand("select * from [Sheet1$]", conn); conn.Open(); OleDbDataReader reader = com.ExecuteReader(); string Name = ""; string RollNumber = ""; while (reader.Read()) { Name = valid(reader, 0); RollNumber = valid(reader, 1); insertdataintosql(Name, RollNumber); } conn.Close(); l1.Text = "Records Inserted successfully"; } protected string valid(OleDbDataReader reader, int str1) { object val = reader[str1]; if (val != DBNull.Value) return val.ToString(); else return Convert.ToString(0); } public void insertdataintosql(string Name, string RollNumber) { SqlConnection conn = new SqlConnection(connStr); SqlCommand com = new SqlCommand(); com.Connection = conn; com.CommandText = "insert into student(Name,RollNumber)values(@Name,@RollNumber)"; com.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name; com.Parameters.Add("@RollNumber", SqlDbType.VarChar).Value = RollNumber; com.CommandType = CommandType.Text; conn.Open(); com.ExecuteNonQuery(); conn.Close(); } } } Thanks If this post helps you mark it as answer