Click here to Skip to main content
15,905,967 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have successfully import excel file records to sql table.
but the orders of the records is changed while saving in table.
i wanna add the files as it is in excel file .
Why i am getting this issue?

please help!
thanks in advance

What I have tried:

private void btnELChooseFile_Click(object sender, EventArgs e)
{
    DataSet ds = new DataSet();
    if (FileUpload1.ShowDialog() == DialogResult.OK)
    {
        string FileName = Path.GetFileName(FileUpload1.FileName);
        HFExcel.Text = FileName.Trim();
        string Extension = Path.GetExtension(FileUpload1.FileName);
        string FilePath = System.Windows.Forms.Application.StartupPath + "\\App_Data\\" + FileName;
        System.IO.File.Copy(FileUpload1.FileName, FilePath);
        string conStr = string.Empty;
        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                break;
            case ".xlsx": //Excel 07
                conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                break;
        }
        OleDbConnection connExcel = new OleDbConnection(conStr);
        // OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        //cmdExcel.Connection = connExcel;
        try
        {
            connExcel.Open();
            DataTable dtExcelSchema;
            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            var SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
            OleDbCommand cmdExcel = new OleDbCommand("SELECT * From [" + SheetName + "]", connExcel);
            cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
            oda.SelectCommand = cmdExcel;
            //oda.Fill(dt);



            oda.Fill(ds);
            DataTable exceldt = ds.Tables[0];
            //ViewState["dt"] = dt;
            // MessageBox.Show("try");
            string Result = string.Empty;
            int TotalCount = 0;
            int ResultCount = 0;
            int FailedCount = 0;
            int AlreadyCount = 0;
            //DataTable dt = new DataTable();
            //dt = ViewState["dt"] as DataTable;

            //TotalCount = dt.Rows.Count;
            List<string> ListLinkedIds = new List<string>();

            if (exceldt.Rows.Count > 0)
            {
                // MessageBox.Show("Enter");
                for (int i = 0; i < exceldt.Rows.Count; i++)
                {
                    objBO.QueryType = "Insert";
                    objBO.ProductID = exceldt.Rows[i]["Group"].ToString().Trim();
                    objBO.ProductGroupID = exceldt.Rows[i]["LocationId"].ToString().Trim();
                    objBO.ProductName = exceldt.Rows[i]["EquipmentName"].ToString().Trim();
                    objBO.BarcodeNo = exceldt.Rows[i]["EquipmentId"].ToString().Trim();
                    objBO.ProductModel = exceldt.Rows[i]["EquipmentModel"].ToString().Trim();
                    objBO.ProductSerial = exceldt.Rows[i]["EquipmentSerial"].ToString().Trim();
                    objBO.ProductMake = exceldt.Rows[i]["EquipmentMake"].ToString().Trim();
                    objBO.CalibrationFrequency = exceldt.Rows[i]["CalibrationFrequency"].ToString().Trim();
                    objBO.DateofPuechase = ValidateClass.ConvDMYTOYMD(exceldt.Rows[i]["DateofPuechase"].ToString()).Trim();
                    objBO.ASSETNo = exceldt.Rows[i]["ASSETNo"].ToString().Trim();
                    objBO.Calibrated = ValidateClass.ConvDMYTOYMD(exceldt.Rows[i]["CalibrationDate"].ToString()).Trim();
                    objBO.CalibrateDue = ValidateClass.ConvDMYTOYMD(exceldt.Rows[i]["CalibrateDue"].ToString()).Trim();
                    objBO.WarrantyDate = ValidateClass.ConvDMYTOYMD(exceldt.Rows[i]["WarrantyDate"].ToString()).Trim();
                    objBO.ProductRemark = exceldt.Rows[i]["EquipmentRemark"].ToString().Trim();
                    objBO.EmailBefore = exceldt.Rows[i]["EmailBefore"].ToString().Trim();
                    objBO.CalibratCertificate = null;
                    objBO.Location = "1";
                    objBO.Status = "Y";
                    objBO.CreatedBy = userName;
                    objBO.TransactionFlag = "N";
                    Result = objBAL.ProductMaster(objBO);

                    if (Result == "Success")
                    {
                        ResultCount = ResultCount + 1;
                    }
                    else if (Result == "Already")
                    {
                        AlreadyCount = AlreadyCount + 1;
                    }
                    else
                    {
                        FailedCount = FailedCount + 1;
                    }
                }
                if (TotalCount > 0)
                {
                    string TotalRecord = string.Empty;
                    string RecordSuccessfully = string.Empty;
                    string AlreadyExisting = string.Empty;
                    string RecordFailed = string.Empty;
                    if (TotalCount > 0)
                    {
                        TotalRecord = "Total Record" + ' ' + TotalCount.ToString();
                    }
                    else
                    {
                        TotalRecord = string.Empty;
                    }
                    if (ResultCount > 0)
                    {
                        RecordSuccessfully = ResultCount.ToString() + ' ' + "Record(s) are Inserted Successfully, ";
                    }
                    else
                    {
                        RecordSuccessfully = string.Empty;
                    }
                    if (AlreadyCount > 0)
                    {
                        AlreadyExisting = AlreadyCount.ToString() + ' ' + "Asset No. is Already Existing, ";
                    }
                    else
                    {
                        AlreadyExisting = string.Empty;
                    }
                    if (FailedCount > 0)
                    {
                        RecordFailed = FailedCount.ToString() + ' ' + "Record(s) are Failed, ";
                    }
                    else
                    {
                        RecordFailed = string.Empty;
                    }
                    BindProduct();
                    MessageBox.Show("Product Master", " " + TotalRecord + "( " + RecordSuccessfully + " " + AlreadyExisting + " " + RecordFailed + ")");
                }

                else
                {
                    MessageBox.Show("no rows");
                }
            }

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);

            //MessageBox.Show("catch");
        }

    }
    else
    {
        MessageBox.Show("no rows");
    }

}
Posted
Updated 1-May-22 23:59pm

1 solution

That's complicated: it depends on a lot of factors, such as the control you are displaying treh DB version from and it's settings, to the query you are using to extract the data from the DB.

Let's start with the basics: Excel is not a DB, it;s a spreadsheet - you know that, but you may not realize what it means. Excel works with numbered (or "named-in-sequence" if you prefer to think of it that way) rows of numbered (or "named-in-sequence") columns.
So when you pull data from Excel (unless you specify otherwise) you get the data in that row x column order.

But SQL is a DB: it works with rows of named columns, but it doesn't automatically order the rows, and it is at liberty to stored them in any order it sees fit - because the row itself contains information which may be used to order the rows when they are retrieved via a SELECT, but SQL doesn't know what column that data is in, or what order might be requested. That's why it has Primary key and Column Indexes - the Primary key is always indexed for easy and quick searching / sorting (as well as containing a unique value) and Indexes allow you to say that you will often search or sort by this column as well.

WHen you SELECT rows from a DB, there is no guaranteed order in which they will be presented unless the SELECT command includes an ORDER BY clause - generally it's the order in which they were inserted or at least stored so it doesn't change, but it can change "on the fly" if SQL decides to compact the DB.

So unless your SELECT to fill your control contains an ORDER BY clause, you don't; really have any control over the ordering that your user will see. And the control itself can have it's own ordering: often by the first column value which may not be what the Excel sheet was organised by!

So start with you DB and see exactly what is in there: use SSMS to SELECT the rows without any ORDER BY, then look at what you get. Then compare that with the data you see, and it should be obvious which data is providing a sort order. Then you can look at your code to work out what part of it is applying the sort!

Sorry, but we can't do any of that for yoU!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900