Copy data from excel to ms sql
 

Hi, I am trying to copy som data from an excel document that I have on the server into a table in my ms sql server.
I think the problem now is that I am having trouble connecting to the sql server.

I'm using sql server 2005.

You can see the error message here:
http://www.excel.web.surftown.dk/default.aspx
and then click on the button.

This is all the code I have written:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web.Security;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.Common;

using System.Data.OleDb;

 







public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {



    }





    protected void Button1_Click(object sender, EventArgs e)

    {

        string excelConnectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", Server.MapPath("database.xls"));







        using (OleDbConnection connection =

             new OleDbConnection(excelConnectionString))

        {



            OleDbCommand command = new OleDbCommand

            ("Select ID,Data FROM [Sheet1$]", connection);



            connection.Open();



            using (DbDataReader dr = command.ExecuteReader())

            {

                string sqlConnectionString = "Data Source=;Initial Catalog=Test;Integrated Security=True";



                using (SqlBulkCopy bulkCopy =

                   new SqlBulkCopy(sqlConnectionString))

                {

                    bulkCopy.DestinationTableName = "ExcelData";

                    bulkCopy.WriteToServer(dr);



                    

                }

            }

        }

    }

}

I hope someone can help me with the problem?

Kischi

Re: Copy data from excel to ms sql
 

I actually got it to work now.
I just had to change the datasource.

Thanks anyway. :-D

Kischi