需要将数据表存储到数据库。 [英] need to store datatable to database.

问题描述

嗨


我需要将存储在excel中的值存储到database.to这样做我已经在datatable中获得了那个想要将数据表值存储到sqlserver 2008中的excel值。



尝试时我收到错误，因为列'POL'不属于桌子。



这是我的代码：

Hi
I need to store the values that stored in excel to database.to do so i have get that excel values in datatable after that want to store datatable value into sqlserver 2008.

When trying that I'm getting an error as "Column 'POL' does not belong to table ."

Here is my code:

 private void Import_To_Grid(string FilePath, string Extension, string isHDR)
       {
           String strConnString = ConfigurationManager.ConnectionStrings["CARGONETConnectionString"].ConnectionString;
           //file upload path
           string FolderPath = Server.MapPath(ConfigurationManager.AppSettings["FolderPath"]);
           //file name
           string FileName = lblFileName.Text;
           //Create connection string to Excel work book
           
           string conStr = "";
           switch (Extension)
           {
               case ".xls": //Excel 97-03
                   conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FolderPath + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                   break;
               case ".xlsx": //Excel 07
                   conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FolderPath + FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
                   break;
           }

            conStr = String.Format(conStr, FilePath, isHDR);
            OleDbConnection connExcel = new OleDbConnection(conStr);
            OleDbCommand cmdExcel = new OleDbCommand();
            OleDbDataAdapter oda = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            cmdExcel.Connection = connExcel;
            
           //Get the name of First Sheet
            connExcel.Open();
            DataTable dtExcelSchema;
            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string SheetName = ddlSheets.SelectedValue.ToString();
            connExcel.Close();
            
           //Read Data from First Sheet
            connExcel.Open();
            cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
            oda.SelectCommand = cmdExcel;
            oda.Fill(dt);
            connExcel.Close();

           //Bind to Database
            DataRow newRow = dt.NewRow();

//Using SQL
            string sql = "INSERT INTO TB_TransAgenSeaFreightRate(POL,POD,FORWARDER,FORWARDER REFERENCE,SHIPPING LINE,CONTAINER TYPE,CONTAINER SIZE,VALIDITY FROM,VALIDITY TO,BASIC RATE,PAF,CAF,PSS,TOTAL AMOUNT,REE DAYS,CREDIT DAYS,NIT DEPOSIT,ISACTIVE,COMPANYID) " +
                         " VALUES ( @POL,  @POD, @FORWARDER, @FORWARDER REFERENCE, @SHIPPING LINE, @CONTAINER TYPE, @CONTAINER SIZE, @VALIDITY FROM, @VALITITY TO, @BASIC RATE, @PAF, @CAF, @PSS, @TOTAL AMOUNT, @FREE DAYS, @CREDIT DAYS, @NIT DEPOSIT, @ISACTIVE,@COMPANYID)";
            using (SqlConnection conn = new SqlConnection(strConnString))
            {
                conn.Open();
                foreach (DataRow r in dt.Rows)
                {

                    
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;
                    cmd.Parameters.AddWithValue("@POL", r["POL"]);
                    cmd.Parameters.AddWithValue("@POD", r["POD"]);
                    cmd.Parameters.AddWithValue("@FORWARDER", r["FORWARDER"]);
                    cmd.Parameters.AddWithValue("@FORWARDER REFERENCE", r["FORWARDER REFERENCE"]);
                    cmd.Parameters.AddWithValue("@SHIPPING LINE", r["SHIPPING LINE"]);
                    cmd.Parameters.AddWithValue("@CONTAINER TYPE", r["CONTAINER TYPE"]);
                    cmd.Parameters.AddWithValue("@CONTAINER SIZ", r["CONTAINER SIZE"]);
                    cmd.Parameters.AddWithValue("@VALIDITY FROM", r["VALIDITY FROM"]);
                    cmd.Parameters.AddWithValue("@VALIDITY TO", r["VALIDITY TO"]);
                    cmd.Parameters.AddWithValue("@BASIC RATE", r["BASIC RATE"]);
                    cmd.Parameters.AddWithValue("@PAF", r["PAF"]);
                    cmd.Parameters.AddWithValue("@CAF", r["CAF"]);
                    cmd.Parameters.AddWithValue("@PSS", r["PSS"]);
                    cmd.Parameters.AddWithValue("@TOTAL AMOUNT", r["TOTAL AMOUNT"]);
                    cmd.Parameters.AddWithValue("@FREE DAYS", r["FREE DAYS"]);
                    cmd.Parameters.AddWithValue("@CREDIT DAYS", r["CREDIT DAYS"]);
                    cmd.Parameters.AddWithValue("@NIT DEPOSIT", r["NIT DEPOSIT"]);
                    cmd.Parameters.AddWithValue("@ISACTIVE", 1);
                    cmd.Parameters.AddWithValue("@COMPANYID", TXTCompanyID.Text);
                   

                    cmd.ExecuteNonQuery();   
                }
            }

//Using LINQ

            //using (LQTransAgentSeaFreightRateDataContext DB = new LQTransAgentSeaFreightRateDataContext())
            //{
            //    foreach (DataRow r in dt.Rows)
            //    {
            //        var newSFR = new TB_TransAgentSeaFreightRate
            //        {
            //            tASF_VCPOD = r["POL"].ToString(),
            //            tASF_VCPOL = r["POD"].ToString(),
            //            tASF_VCForwarder = r["FORWARDER"].ToString(),
            //            tASF_VCForwarderReference = r["FORWARDER REFERENCE"].ToString(),
            //            tASF_VCShippingLine = r["SHIPPING LINE"].ToString(),
            //            tASF_VCContainerType = r["CONTAINER TYPE"].ToString(),
            //            tASF_VCContainerSize = r["CONTAINER SIZE"].ToString(),
            //            tASF_DTEValidFrom = Convert.ToDateTime(r["VALIDITY FROM"].ToString()),
            //            tASF_DTEValidTo = Convert.ToDateTime(r["VALITITY TO"].ToString()),
            //            tASF_NUBasicRate = Convert.ToDecimal(r["BASIC RATE"]),
            //            tASF_NUPAF = Convert.ToDecimal(r["PAF "]),
            //            tASF_NUCAF = Convert.ToDecimal(r["CAF"]),
            //            tASF_NUPSS = Convert.ToDecimal(r["PSS"]),
            //            tASF_NUTotalAmount = Convert.ToDecimal(r["TOTAL AMOUNT"]),
            //            tASF_NUFreeDays = Convert.ToDecimal(r["FREE DAYS"]),
            //            tASF_VCCreditDays = r["CREDIT DAYS"].ToString(),
            //            tASF_VCNITDeposit = r["NIT DEPOSIT"].ToString(),
            //            tASF_NUIsActive = 1,
            //            tASF_mCMP_NUUniqueId = mobjGenlib.ConvertLong(TXTCompanyID.Text)

            //        };
            //        DB.TB_TransAgentSeaFreightRates.InsertOnSubmit(newSFR);
            //        DB.SubmitChanges();
            //    }
            //}

            //Bind Data to GridView
            dg_AgentSFR.Caption = Path.GetFileName(FilePath);
            dg_AgentSFR.DataSource = dt;
            dg_AgentSFR.DataBind();

       }

请帮我解决我错的地方......



提前致谢。

Please help me where i'm going wrong...

Thanks in advance.

推荐答案

按照< b> BulletVictim< / b>



好​​像我说的（据我所知）excel文件需要与目标sql表相同的列标题。因此，为了添加额外的值，我建议离开，直到添加excel然后再添加它们，或者你可以改变excel文档并在将excel添加到sql之前将这些额外的值添加到它。我从我的一个asp网站中提取了一些批量复制代码供你查看，也许它可以帮助你找到你在批量复制尝试中遗漏的东西。



那里一旦我纠正它，它就是excel中的错配，它工作正常。

并且感谢

As per <b>BulletVictim </b>

Ok like I said(as far as my knowledge goes) the excel file needs the same column headers as the target sql table. So for adding the extra values I would suggest leaving does out until the excel has been added and then adding them in afterwards, or alternatively you could alter the excel document and add these extra values to it before adding the excel to sql. I extracted some bulk copy code from one of my asp sites for you to look at and maybe it helps you spot something that you missed in your bulk copy attempt.

There is a mismatch calumn in excel once i correct it , it works fine.
And thanks

BulletVictim - yesterday

这篇关于需要将数据表存储到数据库。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！