"A column named 'client meeting ID' already belongs to this datatable." error in ASP .NET whlie downloading data into excel format

Question

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 20-Aug-17 21:02pm

paul_vin

Updated 21-Aug-17 4:37am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2017-08-21T04:37:00

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.)