How to fix getschema error within my project?

Question

0.00/5 (No votes)

See more:

Hello. I have a web program that can upload a excel file to the SQL server database. So far the only error I get when I run the program is:

C#

System.ArgumentException: 'Column 'TABLE_NAME' does not belong to table MetaDataCollections.'

Which happens on this line of code:

C#

string sheet1 = excel_con.GetSchema().Rows[0]["TABLE_NAME"].ToString();

This only happens when I click the upload button. So I am stomped at this moment. The Excel file I am using does have small data in it. The database does not have any Tables in it.

Here is my Full Code of the HTML:

ASP.NET

<pre><%@ Page Language="C#" AutoEventWireup="true" CodeFile="CS.aspx.cs" Inherits="CS" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button Text="Upload" OnClick = "Upload" runat="server" />
        <br />
        <br />
        <br />
        <br />
    </form>
</body>
</html>

Here is the Code Behind:

C#

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


public partial class CS : System.Web.UI.Page
{
    public object SqlSchemaGuid { get; private set; }

    protected void Upload(object sender, EventArgs e)
{
    //Upload and save the file
    string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(excelPath);


        string conString = string.Empty;
        string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        switch (extension)
        {
            case ".xls": //Excel 97-03
                conString = ConfigurationManager.ConnectionStrings["AkinTestConnectionString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07 or higher
                conString = ConfigurationManager.ConnectionStrings["AkinTestConnectionString"].ConnectionString;
                break;
            case ".csv": //CSV Files
                conString = ConfigurationManager.ConnectionStrings["AkinTestConnectionString"].ConnectionString;
                break;
                //SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[""].ConnectionString);
                //con.Open();

        }
    conString = string.Format(conString, excelPath);
    using (SqlConnection excel_con = new SqlConnection(conString))
    {
        excel_con.Open();
        string sheet1 = excel_con.GetSchema().Rows[0]["TABLE_NAME"].ToString();
        DataTable dtExcelData = new DataTable();

        //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
        dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                new DataColumn("Name", typeof(string)),
                new DataColumn("Salary",typeof(decimal)) });

        using (SqlDataAdapter reader = new SqlDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
        {
            reader.Fill(dtExcelData);
        }
        excel_con.Close();

        string consString = ConfigurationManager.ConnectionStrings["AkinTestConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                //Set the database table name
                sqlBulkCopy.DestinationTableName = "dbo.tblPersons";

                //[OPTIONAL]: Map the Excel columns with that of the database table
                sqlBulkCopy.ColumnMappings.Add("Id", "PersonId");
                sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                sqlBulkCopy.ColumnMappings.Add("Salary", "Salary");
                con.Open();
                sqlBulkCopy.WriteToServer(dtExcelData);
                con.Close();
            }
        }
    }
}
}

What I have tried:

I have tried to change the TABLE_NAME and COMMENT the line of code out.

Posted 14-Nov-19 8:55am

Computer Wiz99

Add a Solution

Comments

ZurdoDev 14-Nov-19 15:58pm

TABLE_NAME is not a name of a table in your file. Just debug it and see what is in excel_con.GetSchema().Rows[0]

Computer Wiz99 14-Nov-19 16:45pm

Thanks. I have been looking at this code for to long. It is the simple things.

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)