Import Data from Excel to a SQL Server 2000 use c#

Question

hai .... My name is ngurah please help about Import Data from Excel to a SQL Server 2000 use c#?? thanks

Satyapriya Nayak · Answer

Hi Ngurah 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(NameRollNumber)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

ngurah arta · Answer

private void button1_Click(object sender EventArgs e) { try { System.Data.OleDb.OleDbConnection MyConnection; System.Data.DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; MyConnection = new System.Data.OleDb.OleDbConnection(@provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + button2.Text + ';Extended Properties=Excel 8.0;); MyCommand = new System.Data.OleDb.OleDbDataAdapter(select * from [Sheet1$]  MyConnection); MyCommand.TableMappings.Add(Table TestTable); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dataGridView1.DataSource =DtSet.Tables[0]; MyConnection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } private void button2_Click(object sender EventArgs e) {/* OpenFileDialog dlg = new OpenFileDialog(); // Show all files dlg.Filter = string.Empty; dlg.ShowDialog(); */ /* all file dak terfokus OpenFileDialog dlg = new OpenFileDialog(); // Filter by Word Documents OR Excel Worksheets OR PowerPoint Presentations // OR Office Files // OR All Files *dlg.Filter = Word Documents|*.doc|Excel Worksheets|*.xls|PowerPoint Presentations|*.ppt + |Office Files|*.doc;*.xls;*.ppt + |All Files|*.*; dlg.ShowDialog(); */ OpenFileDialog dlg = new OpenFileDialog(); // Filter by Excel Worksheets dlg.Filter = Excel Worksheets|*.xls; dlg.ShowDialog(); if (dlg.ShowDialog() == DialogResult.OK) { button2.Text = Convert.ToString(dlg.FileName); //pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage; //pictureBox1.Image = Image.FromFile(openFile.FileName); } }

ngurah arta · Answer

Thanks Satyapriya Nayak but how to use windows form? I have project and show the data like this I hope to Import Data from Excel to a SQL Server 2000 use c#??