读取Excel表并将其导入SQL Server数据库 [英] Read and Import Excel Sheet into SQL Server Database

问题描述

嗨


我正在开发一个应用程序，它将从用户那里获取Excel文件并将其保存在文件夹中，然后在按钮上单击它想要读取excel文件数据和将它存储在数据库中。



它接受文件并将其保存在文件夹中。但它会抛出以下错误

Hi
I'm Developing an application which will get the Excel file from the user and save it in folder after that on button click it want to read the excel file data and store it in database.

It was accepting the file and save it in folder.But it throws following Error near

myConnection.Open();

错误：



没有可用的错误消息，结果代码：E_FAIL（0x80004005）。







这是我的插入代码

Error:

No error message available, result code: E_FAIL(0x80004005).



Here is my Inserting Code

void insertDB()
        {
            string FileName = lblFileName.Text;
            string Extension = Path.GetExtension(FileName);
            string FolderPath =Server.MapPath(ConfigurationManager.AppSettings["FolderPath"]);
            string conStr = "";
            switch (Extension)
            {
                case ".xls": //Excel 97-03
                conStr =ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
                case ".xlsx": //Excel 07
                conStr =ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                    break;
            }

            using (var context = new LQTransAgentSeaFreightRateDataContext())
            {
                string sql = string.Format("Select * FROM [{0}]", "Sheet1$");
                using (var myConnection = new OleDbConnection(conStr))
                using (var myCommand = new OleDbCommand(sql, myConnection))
                {
                    myConnection.Open();
                    var myReader = myCommand.ExecuteReader();
                    while (myReader.Read())
                    {
                        context.TB_TransAgentSeaFreightRates.InsertOnSubmit(new TB_TransAgentSeaFreightRate()
                        {
                            tASF_VCPOD = myReader.GetString(0),
                            tASF_VCPOL = myReader.GetString(1),
                            tASF_VCForwarder= myReader.GetString(2),
                            tASF_VCForwarderReference= myReader.GetString(3),
                            tASF_VCShippingLine= myReader.GetString(4),
                            tASF_VCContainerType= myReader.GetString(5),
                            tASF_VCContainerSize= myReader.GetString(6),
                            tASF_DTEValidFrom = Convert.ToDateTime( myReader.GetString(7)),
                            tASF_DTEValidTo = Convert.ToDateTime(myReader.GetString(8)),
                            tASF_NUBasicRate= mobjGenlib.ConvertLong( myReader.GetString(9)),
                            tASF_NUPAF = mobjGenlib.ConvertLong(myReader.GetString(10)),
                            tASF_NUCAF = mobjGenlib.ConvertLong(myReader.GetString(11)),
                            tASF_NUPSS = mobjGenlib.ConvertLong(myReader.GetString(12)),
                            tASF_NUTotalAmount =mobjGenlib.ConvertLong(myReader.GetString(13)),
                            tASF_NUFreeDays = mobjGenlib.ConvertLong(myReader.GetString(14)),
                            tASF_VCCreditDays = myReader.GetString(15),
                            tASF_VCNITDeposit = myReader.GetString(16),
                            tASF_NUIsActive=1,
                            tASF_mCMP_NUUniqueId=mobjGenlib.ConvertLong(TXTCompanyID.Text)
                        });
                    }
                }

                context.SubmitChanges();
            }
        }

任何人都可以帮我解决这个问题。



先谢谢

Can any one Please Help me to resolve this.

Thanks in Advance

推荐答案

）;
使用（ var myConnection = new OleDbConnection（conStr））
使用（ var myCommand = new OleDbCommand（sql，myConnection） ））
{
myConnection.Open（）;
var myReader = myCommand.ExecuteReader（）;
while （myReader.Read（））
{
context.TB_TransAgentSeaFreightRates.InsertOnSubmit（ new TB_TransAgentSeaFreightRate（）
{
tASF_VCPOD = myReader.GetString（ 0 ），
tASF_VCPOL = myReader.GetString（ 1 ），
tASF_VCForwarder = myReader.GetString（ 2 ），
tASF_VCForwarderReference = myReader.GetString（ 3 ），
tASF_VCShippingLine = myReader.GetString（ 4 ），
tASF_VCContainerType = myReader.GetString（ 5 ），
tASF_VCContainerSize = myReader.GetString（ 6 ），
tASF_DTEValidFrom = Convert.ToDateTime（myReader.GetString（ 7 ）），
tASF_DTEValidTo = Convert.ToDateTime（myReader.GetStrin） g（ 8 ）），
tASF_NUBasicRate = mobjGenlib.ConvertLong（myReader.GetString（ 9 ） ），
tASF_NUPAF = mobjGenlib.ConvertLong（myReader.GetString（ 10 ）），
tASF_NUCAF = mobjGenlib.ConvertLong（myReader.GetString（ 11 ）），
tASF_NUPSS = mobjGenlib.ConvertLong（myReader.GetString（ 12 ）），
tASF_NUTotalAmount = mobjGenlib.ConvertLong（myReader.GetString（ 13 ）），
tASF_NUFreeDays = mobjGenlib.ConvertLong（myReader.GetString（ 14 ）），
tASF_VCCreditDays = myReader.GetString（ 15 ），
tASF_VCNITDeposit = myReader.GetString（<跨度lass =code-digit> 16 ），
tASF_NUIsActive = 1 ，
tASF_mCMP_NUUniqueId = mobjGenlib.ConvertLong（TXTCompanyID.Text ）
}）;
}
}

context.SubmitChanges（）;
}
}

"); using (var myConnection = new OleDbConnection(conStr)) using (var myCommand = new OleDbCommand(sql, myConnection)) { myConnection.Open(); var myReader = myCommand.ExecuteReader(); while (myReader.Read()) { context.TB_TransAgentSeaFreightRates.InsertOnSubmit(new TB_TransAgentSeaFreightRate() { tASF_VCPOD = myReader.GetString(0), tASF_VCPOL = myReader.GetString(1), tASF_VCForwarder= myReader.GetString(2), tASF_VCForwarderReference= myReader.GetString(3), tASF_VCShippingLine= myReader.GetString(4), tASF_VCContainerType= myReader.GetString(5), tASF_VCContainerSize= myReader.GetString(6), tASF_DTEValidFrom = Convert.ToDateTime( myReader.GetString(7)), tASF_DTEValidTo = Convert.ToDateTime(myReader.GetString(8)), tASF_NUBasicRate= mobjGenlib.ConvertLong( myReader.GetString(9)), tASF_NUPAF = mobjGenlib.ConvertLong(myReader.GetString(10)), tASF_NUCAF = mobjGenlib.ConvertLong(myReader.GetString(11)), tASF_NUPSS = mobjGenlib.ConvertLong(myReader.GetString(12)), tASF_NUTotalAmount =mobjGenlib.ConvertLong(myReader.GetString(13)), tASF_NUFreeDays = mobjGenlib.ConvertLong(myReader.GetString(14)), tASF_VCCreditDays = myReader.GetString(15), tASF_VCNITDeposit = myReader.GetString(16), tASF_NUIsActive=1, tASF_mCMP_NUUniqueId=mobjGenlib.ConvertLong(TXTCompanyID.Text) }); } } context.SubmitChanges(); } }

任何人都可以帮我解决这个问题。



先谢谢

Can any one Please Help me to resolve this.

Thanks in Advance

嘿伙计，



这些文章一定能帮到你：< br $> b $ b

http://www.c-sharpcorner.com/uploadfile/vivek4u_swamy/import-data-from-excel-to-a-sql-server-database/ [ ^ ]



http://stackoverflow.com/questions/21670983/import-data-from-an-excel-sheet-into-a-sql-server-database [ ^ ]



http://www.c-sharpcorner.com/UploadFile/99bb20/import-excel-data-to -sql-server-in-Asp-Net / [ ^ ]

Hey Buddy,

These Articles will definitely help you:

http://www.c-sharpcorner.com/uploadfile/vivek4u_swamy/import-data-from-excel-to-a-sql-server-database/[^]

http://stackoverflow.com/questions/21670983/import-data-from-an-excel-sheet-into-a-sql-server-database[^]

http://www.c-sharpcorner.com/UploadFile/99bb20/import-excel-data-to-sql-server-in-Asp-Net/[^]

这篇关于读取Excel表并将其导入SQL Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！