Click here to Skip to main content
15,301,291 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
"A column named 'Client Meeting ID' already belongs to this DataTable."}

error when I am trying to Download Data from Database into Excel Format.

protected void btnmeeting_Click(object sender, EventArgs e)
        {
            ResultsData1.Clear();
            SqlConnection con211 = new SqlConnection(connstring);
            SqlCommand cmd21 = new SqlCommand("USP_Report", con211);
            cmd21.CommandType = CommandType.StoredProcedure;
            cmd21.Parameters.AddWithValue("@mode", 99);            
            cmd21.Parameters.AddWithValue("@userid", Session["UserId"].ToString());           
            SqlDataAdapter da = new SqlDataAdapter(cmd21);            
            DataTable dt = new DataTable();
            dt.Clear();
            da.Fill(dt);
            DataTableReader reader = new DataTableReader(dt);
           int c = 0;
            bool firstTime = true;

            DataTable dtSchema = new DataTable();
            dtSchema.Clear();
            dtSchema = reader.GetSchemaTable();
            var listCols = new List<DataColumn>();
            if (dtSchema != null)
            {
                try
                {
                    foreach (DataRow drow in dtSchema.Rows)
                    {
                        string columnName = Convert.ToString(drow["ColumnName"]);
                        var column = new DataColumn(columnName, (Type)(drow["DataType"]));
                        column.Unique = (bool)drow["IsUnique"];
                        column.AllowDBNull = (bool)drow["AllowDBNull"];
                        column.AutoIncrement = (bool)drow["IsAutoIncrement"];
                        listCols.Add(column);
                        ResultsData1.Columns.Add(column);

                    }
                }
                catch (Exception ex)
                {
                    Response.Write(ex);
                }
            }


It Giving Error at
<pre>ResultsData1.Columns.Add(column);


What I have tried:

I have Created Two Global Variables as
static private int rowsPerSheet = 200;
    static private DataTable ResultsData1 = new DataTable();


Now on Button Click Event I have Following Code

protected void btnmeeting_Click(object sender, EventArgs e)
       {
           ResultsData1.Clear();
           SqlConnection con211 = new SqlConnection(connstring);
           SqlCommand cmd21 = new SqlCommand("USP_Report", con211);
           cmd21.CommandType = CommandType.StoredProcedure;
           cmd21.Parameters.AddWithValue("@mode", 99);
           cmd21.Parameters.AddWithValue("@userid", Session["UserId"].ToString());
           SqlDataAdapter da = new SqlDataAdapter(cmd21);
           DataTable dt = new DataTable();
           dt.Clear();
           da.Fill(dt);
           DataTableReader reader = new DataTableReader(dt);
          int c = 0;
           bool firstTime = true;

           DataTable dtSchema = new DataTable();
           dtSchema.Clear();
           dtSchema = reader.GetSchemaTable();
           var listCols = new List<DataColumn>();
           if (dtSchema != null)
           {
               try
               {
                   foreach (DataRow drow in dtSchema.Rows)
                   {
                       string columnName = Convert.ToString(drow["ColumnName"]);
                       var column = new DataColumn(columnName, (Type)(drow["DataType"]));
                       column.Unique = (bool)drow["IsUnique"];
                       column.AllowDBNull = (bool)drow["AllowDBNull"];
                       column.AutoIncrement = (bool)drow["IsAutoIncrement"];
                       listCols.Add(column);
                       ResultsData1.Columns.Add(column);

                   }
               }
               catch (Exception ex)
               {
                   Response.Write(ex);
               }
           }

           // Call Read before accessing data.
           while (reader.Read())
           {
               DataRow dataRow = ResultsData1.NewRow();
               for (int i = 0; i < listCols.Count; i++)
               {
                   dataRow[(listCols[i])] = reader[i];
               }
               ResultsData1.Rows.Add(dataRow);
               c++;
               if (c == rowsPerSheet)
               {
                   c = 0;
                   ExportToOxml(firstTime);
                   ResultsData1.Clear();
                   firstTime = false;
               }
           }
           if (ResultsData1.Rows.Count > 0)
           {
               ExportToOxml(firstTime);
               ResultsData1.Clear();
           }
           // Call Close when done reading.
           reader.Close();

       }

I have also Created function to download file as
This function download data 200 row on each sheet

private static void ExportToOxml(bool firstTime)
        {
            const string fileName = @"E:\MyExcel1.xlsx";

            //Delete the file if it exists. 
            if (firstTime && File.Exists(fileName))
            {
                File.Delete(fileName);
            }

            uint sheetId = 1; //Start at the first sheet in the Excel workbook.

            if (firstTime)
            {
                //This is the first time of creating the excel file and the first sheet.
                // Create a spreadsheet document by supplying the filepath.
                // By default, AutoSave = true, Editable = true, and Type = xlsx.
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook);

                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();

                // Add a WorksheetPart to the WorkbookPart.
                var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                worksheetPart.Worksheet = new Worksheet(sheetData);


                var bold1 = new Bold();
                CellFormat cf = new CellFormat();


                // Add Sheets to the Workbook.
                Sheets sheets;
                sheets = spreadsheetDocument.WorkbookPart.Workbook.
                    AppendChild<Sheets>(new Sheets());

                // Append a new worksheet and associate it with the workbook.
                var sheet = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.
                        GetIdOfPart(worksheetPart),
                    SheetId = sheetId,
                    Name = "Sheet" + sheetId
                };
                sheets.Append(sheet);

                //Add Header Row.
                var headerRow = new Row();
                foreach (DataColumn column in ResultsData1.Columns)
                {
                    var cell = new Cell
                    {
                        DataType = CellValues.String,
                        CellValue = new CellValue(column.ColumnName)
                    };
                    headerRow.AppendChild(cell);
                }
                sheetData.AppendChild(headerRow);

                foreach (DataRow row in ResultsData1.Rows)
                {
                    var newRow = new Row();
                    foreach (DataColumn col in ResultsData1.Columns)
                    {
                        var cell = new Cell
                        {
                            DataType = CellValues.String,
                            CellValue = new CellValue(row[col].ToString())
                        };
                        newRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(newRow);
                }

                ResultsData1.Clear();
                workbookpart.Workbook.Save();

                spreadsheetDocument.Close();
            }
            else
            {
                // Open the Excel file that we created before, and start to add sheets to it.
                var spreadsheetDocument = SpreadsheetDocument.Open(fileName, true);

                var workbookpart = spreadsheetDocument.WorkbookPart;
                if (workbookpart.Workbook == null)
                    workbookpart.Workbook = new Workbook();

                var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                worksheetPart.Worksheet = new Worksheet(sheetData);
                var sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;

                if (sheets.Elements<Sheet>().Any())
                {
                    //Set the new sheet id
                    sheetId = sheets.Elements<Sheet>().Max(s => s.SheetId.Value) + 1;
                }
                else
                {
                    sheetId = 1;
                }

                // Append a new worksheet and associate it with the workbook.
                var sheet = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.
                        GetIdOfPart(worksheetPart),
                    SheetId = sheetId,
                    Name = "Sheet" + sheetId
                };
                sheets.Append(sheet);

                //Add the header row here.
                var headerRow = new Row();

                foreach (DataColumn column in ResultsData1.Columns)
                {
                    var cell = new Cell
                    {
                        DataType = CellValues.String,
                        CellValue = new CellValue(column.ColumnName)
                    };
                    headerRow.AppendChild(cell);
                }
                sheetData.AppendChild(headerRow);

                foreach (DataRow row in ResultsData1.Rows)
                {
                    var newRow = new Row();

                    foreach (DataColumn col in ResultsData1.Columns)
                    {
                        var cell = new Cell
                        {
                            DataType = CellValues.String,
                            CellValue = new CellValue(row[col].ToString())
                        };
                        newRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(newRow);
                }

                workbookpart.Workbook.Save();

                // Close the document.
                spreadsheetDocument.Close();
                ResultsData1.Clear();
            }
        }
Posted
Updated 21-Aug-17 4:37am
Comments
Richard MacCutchan 21-Aug-17 4:11am
   
What part of that error message do you not understand?
Suvendu Shekhar Giri 21-Aug-17 5:51am
   
wondering the same :P

1 solution

Your stored procedure, USP_Report, is returning two columns called "Client Meeting ID".

A DataTable cannot contain two columns with the same name.

Fix your stored procedure so that the returned column names are unique.

Or fix your code to check for duplicate columns and rename them if necessary:
C#
foreach (DataRow drow in dtSchema.Rows)
{
    string rawColumnName = Convert.ToString(drow["ColumnName"]);
    
    int duplicateCount = 1;
    string columnName = rawColumnName;
    while (ResultsData1.Columns.Contains(columnName))
    {
        columnName = rawColumnName + " (" + duplicateCount + ")";
        duplicateCount++;
    }
    
    ...

NB: You're also going to run into problems if multiple users try to export the data at the same time, since all requests will be trying to write to the same file. You could end up with the file disappearing, or data from different users' requests getting mixed up in the same file.

You should see if there's an option to create the file in memory, without saving it to disk. If there isn't, then you should use a random file for each user, and be sure to delete it after using TransmitFile / WriteFile to send it to the user.


EDIT: The problem is that the DataTable is being stored in a static field. That means the every request from every user is going to be manipulating the same DataTable instance. With multiple users accessing the site at the same time, you're likely to get much more obscure and confusing errors, as users' changes overwrite each other.

Remove the static field, and use a local variable instead. You'll need to pass it into the ExportToOxml method as a parameter.
private static void ExportToOxml(bool firstTime, DataTable ResultsData1)
{
    ...
}

protected void btnmeeting_Click(object sender, EventArgs e)
{
    DataTable ResultsData1 = new DataTable();
    ...
}

(The comment about the file name conflicts still stands.)
   
v2
Comments
paul_vin 21-Aug-17 12:44pm
   
Actually when user is login then fist time, it download file with no problem.
But when second time user click on download button then It giving above error Even after i have cleared data table. so for that what should I do. also I want to deploy
that solution on Live project at that time it should be download by any client on client machine in download folder so. so what path i should have to give to download on client Machine
Richard Deeming 21-Aug-17 12:52pm
   
OK, I see the problem: you're storing the DataTable in a static field.

That means every request from every user will be using the same DataTable instance.

I can only assume you're still testing this code in Visual Studio. If you had deployed it, I would expect to see far more obscure errors, since multiple requests would be trying to manipulate the same object at the same time.

Remove the static field, and use a local variable instead. You'll need to pass it into the ExportToOxml method as a parameter.
paul_vin 22-Aug-17 2:35am
   
First thanks for above solution
How I should restrict to download single user at a time.
would you provide code to me and where I should integrate.
also I have used static path here but i want dynamic path so what changes I
should Do and where
Richard Deeming 22-Aug-17 13:36pm
   
Don't restrict it to a single user. Remove the static field and use a local variable. That way, there won't be any problem with multiple users downloading the data at the same time.

To use a dynamic path, just create a random file name:
string folder = System.IO.Path.GetTempPath();
string fileName = System.IO.Path.Combine(folder, Guid.NewGuid().ToString("N") + ".xlsx");

When you use TransmitFile or WriteFile to send the file to the user, you can use the Content-Disposition header to set the file name:
response.AddHeader("Content-Disposition", "attachment; filename=Report.xlsx");
response.TransmitFile(fileName);

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