Why am I getting a DuplicateNameException?

Question

I am currently making a ASP.NET MVC application for users to upload other users to a database from csv and excel. And while working on the csv-uploader method I have come across a problem where the TextFieldParser - csvReader only reads the first column name and then move on to try and add it to every column in the DataTable and thus it gives the "DuplicateNameException in System.Data.dll". And I have tried to find a counter that helps against this and i have also changed the delimiter type resulting in every column staying in the first DataTable column instead, meaning that the delimiters is not the problem. And there is a breakpoint so I could see that the colFields counter manages to find all columns in the csv-file though it only tries to add the first one to the DataTable.

When searching for the problem the only thing I come accross is when there is deliberate code to try and implement the same column over and over, but I do not think that I have anything like that.

The method just stop trying to add the next column and tries to add the same first one instead. And as a result there is the "DuplicateNameException", anyone can see why that is?

Here is my code and it is placed in a controller:

 [HttpPost]
        public ActionResult Import(HttpPostedFileBase file)
        {

            DataTable csvData = new DataTable();
            if (file != null && file.ContentLength > 0)
            {

                try
                {
                    using (TextFieldParser csvReader = new TextFieldParser(file.InputStream))
                    {

                        //TODO:Skapa en lista eller liknande för delimiter val om möjligt.
                        csvReader.SetDelimiters(new string[] { ";" });
                        csvReader.HasFieldsEnclosedInQuotes = false;
                        string[] colFields = csvReader.ReadFields();
                        foreach (string column in colFields)
                        {
                            //TODO:Nuvarande error: DuplicateNameException, den lägger Author title på två olika kolumner och ger exception när detta upptäcks.

                            DataColumn Titel = new DataColumn(column);
                            Titel.AllowDBNull = true;
                            csvData.Columns.Add(Titel);

                            DataColumn FirstName = new DataColumn(column);
                            FirstName.AllowDBNull = true;
                            csvData.Columns.Add(FirstName);

                            DataColumn LastName = new DataColumn(column);
                            LastName.AllowDBNull = true;
                            csvData.Columns.Add(LastName);

                            DataColumn AbstrNum = new DataColumn(column);
                            AbstrNum.AllowDBNull = true;
                            csvData.Columns.Add(AbstrNum);

                            DataColumn PosterTitel = new DataColumn(column);
                            PosterTitel.AllowDBNull = true;
                            csvData.Columns.Add(PosterTitel);

                            DataColumn Workshop = new DataColumn(column);
                            Workshop.AllowDBNull = true;
                            csvData.Columns.Add(Workshop);

                            DataColumn Keywords = new DataColumn(column);
                            Keywords.AllowDBNull = true;
                            csvData.Columns.Add(Keywords);

                            DataColumn Institution = new DataColumn(column);
                            Institution.AllowDBNull = true;
                            csvData.Columns.Add(Institution);

                            DataColumn CollabEmail = new DataColumn(column);
                            CollabEmail.AllowDBNull = true;
                            csvData.Columns.Add(CollabEmail);
                        }
                        while (!csvReader.EndOfData)
                        {
                            string[] fieldData = csvReader.ReadFields();
                            //Making empty value as null
                            for (int i = 0; i < fieldData.Length; i++)
                            {
                                if (fieldData[i] == "")
                                {
                                    fieldData[i] = null;
                                }
                            }
                            csvData.Rows.Add(fieldData);
                        }
                    }
                    //Fortfarande i try...

                    SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder();
                    cb.DataSource="HELMER/SQLEXPRESS";
                    cb.InitialCatalog="TestDB";
                    cb.IntegratedSecurity=true;
                    SqlConnection cnn = new SqlConnection(cb.ConnectionString);

                    SqlCommand cmd = new SqlCommand("SELECT * FROM csvData", cnn);
                    cnn.Open();
                    SqlDataReader rdr=cmd.ExecuteReader();

                    SqlBulkCopy sbc= new SqlBulkCopy("server=.;database=TestDB;" + "Integrated Security=SSPI");
                    sbc.DestinationTableName = "Users";
                    sbc.WriteToServer(rdr);

                    sbc.Close();
                    rdr.Close();
                    cnn.Close();
                }
                catch (Exception ex)
                {

                }
            }
            return RedirectToAction("Index", "Home", new { Message = "The Import was a success" });
        }

I have taken some source material from this site:

http://www.morgantechspace.com/2013/10/import-csv-file-into-sql-server-using.html

And also I am still pretty new to this site, though it has been a week right about if there is anything I am doing wrong.

Answer 1

Try this :

foreach (string column in colFields)
                {
                    DataColumn datecolumn = new DataColumn(column);
                    datecolumn.AllowDBNull = true;
                    csvData.Columns.Add(datecolumn);
                }

Once you created the columns from colFields and you can add rows to the created columns.

while (!csvReader.EndOfData)
                    {
                        string[] fieldData = csvReader.ReadFields();
                        //Making empty value as null
                        for (int i = 0; i < fieldData.Length; i++)
                        {
                            if (fieldData[i] == "")
                            {
                                fieldData[i] = null;
                            }
                        }
                       csvData.Rows.Add(fieldData); // Here rows are added to the created columns
                    }
                }