Import the Excel 2002 data into sql server 2000 using ado.net1.1

[jayfeb29]

Hi ,

Any one can guide me in Import the Excel 2002 data into sql server 2000 using ado.net1.1. I have searched all the forums most of the solutions end with a line of suggestion not with any code .

here is my code . by using OLEDB JET I got the data from Excel to data set , the data is in the data table

OleDbConnection strConn = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;data source= c:\\1005.xls;Extended Properties=\"Excel 8.0;HDR=YES\"");
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [sheet1$]",strConn);
DataSet dataSet = new DataSet();
myCommand.Fill(dataSet);
DataTable mytable;
mytable= dataSet.Tables[0];

I can view this data on the datagrid .

now I want to transfer this data in to SQL server . I tryed as below but I was end up with nothing

if(mytable.Rows.Count>0)
{
try
{
// connect to sql andinsert the data
OleDbConnection conn = new OleDbConnection ("Provider=SQLOLEDB;Data Source=;Initial Catalog=;");


I was end up here just by making connection to SQL server . Can some one assist me further . I am a beginner.I tryed loop like this


foreach (DataRow dr in mytable.Rows)
{
foreach (DataColumn dc in mytable.Columns)
{
string val = dr[dc].ToString();
Response.Write(val);
}




I can see the string values but no ides how to transfer the data in the table "mytable ' in to sql server

[kenobewan]

You need a dataadapter:

DataAdapter.Update(dataset);

Update will have to be configured for the dataadapter. If you are using VS this will help alot with the setup.

Hope that this helps.

[jayfeb29]

Thanks for the reply .

I used
DataAdapter.Update (dataset, "source table );

It is not working

[kenobewan]

Did you set up the update for the dataadapter? Are you using VS? Did you get an error?

[jayfeb29]

Ys I am using VS
I tryed these methods,

DataAdapter.Updata(dataset,"sourceTable");

my code

OleDbDataAdapter excelda = new OleDbDataAdapter("SELECT * FROM [sheet1$]",excelConn);
DataSet excelds = new DataSet(); excelda.Fill(excelds,"sheet1$");
DataTable excelTable = excelds.Tables["sheet1$"];
//creae new sql adapter that is used to build a dataset
SqlDataAdapter sqlda = new SqlDataAdapter ("select *from tempdata",sqlConn)
SqlCommandBuilder x = new SqlCommandBuilder (sqlda);
sqlda.Update (excelds,"tempdata");
Response.Write (" data is loaded");

Tempdata table in sql is empty .