Click here to Skip to main content
14,735,180 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi, I am using the below code to write data from a comma separated file into a SQL Server table and run into the following Error: Column 'Id' does not belong to table.

private string writetotbl(IList<string> records)
{
    string connString = ConfigurationManager.ConnectionStrings["myDBConnString"].ConnectionString;

    try
    {
        var lkup = from record in records
                         let rec = records.Split(',')
                         select new Lookup
                         {
                             Id = rec[0],
                             Code = rec[1],
                             Description = rec[2]
                         };

        DataTable dt = new DataTable();
        dt.Columns.Add(new DataColumn("@Id", typeof(int)));
        dt.Columns.Add(new DataColumn("@Code", typeof(string)));
        dt.Columns.Add(new DataColumn("@Description", typeof(string)));
        DataRow dr = dt.NewRow();

        foreach (var i in lkup)
        {
            dr = dt.NewRow();
            dr["Id"] = i.Id.Replace("\"", "");
            dr["Code"] = i.Code.Replace("\"", "");
            dr["Description"] = i.Description.Replace("\"", "");
            dt.Rows.Add(dr);
        }

        using (var conn = new SqlConnection(connString))
        {
            conn.Open();
            using (SqlBulkCopy s = new SqlBulkCopy(conn))
            {
                s.DestinationTableName = "Lookup";
                s.BatchSize = dt.Rows.Count;
                s.BulkCopyTimeout = 0;
                s.ColumnMappings.Add("Id", "Id");
                s.ColumnMappings.Add("Code", "Code");
                s.ColumnMappings.Add("Description", "Description");
                s.WriteToServer(dt);
                s.Close();
            }
            conn.Close();
        }
        return (null);
    }
    catch (Exception ex)
    {
        errmsg = ex.Message;
        return (errmsg);
    }
}


Not sure what might be causing this.

Thank you in advance..

What I have tried:

Could you please help me with ideas. I have all the columns in the file, datatable match the column name in the database table and with appropriate datatype. Not sure how this error pops up.
Posted
Updated 15-Mar-18 10:03am
v2

1 solution

Simplify your code, and try again with that:
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("@Id", typeof(int)));
DataRow dr = dt.NewRow();
dr["Id"] = 666;
You will get the same error.
And the error is right: "Column 'Id' does not belong to table"
Column '@Id' does though:
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("@Id", typeof(int)));
DataRow dr = dt.NewRow();
dr["@Id"] = 666;
Will work fine.

So match your columns up, either by changing the initial names:
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("Id", typeof(int)));
dt.Columns.Add(new DataColumn("Code", typeof(string)));
dt.Columns.Add(new DataColumn("Description", typeof(string)));
DataRow dr = dt.NewRow();

foreach (var i in lkup)
{
    dr = dt.NewRow();
    dr["Id"] = i.Id.Replace("\"", "");
    dr["Code"] = i.Code.Replace("\"", "");
    dr["Description"] = i.Description.Replace("\"", "");
    dt.Rows.Add(dr);
}
Or changing the usage:
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("@Id", typeof(int)));
dt.Columns.Add(new DataColumn("@Code", typeof(string)));
dt.Columns.Add(new DataColumn("@Description", typeof(string)));
DataRow dr = dt.NewRow();

foreach (var i in lkup)
{
    dr = dt.NewRow();
    dr["@Id"] = i.Id.Replace("\"", "");
    dr["@Code"] = i.Code.Replace("\"", "");
    dr["@Description"] = i.Description.Replace("\"", "");
    dt.Rows.Add(dr);
}
   
Comments
Member 12586110 15-Mar-18 15:50pm
   
Thanks very much...I apologize for being so stupid. The '@' symbol did not catch my attention.
Maciej Los 15-Mar-18 16:10pm
   
Please, never say like that about yourself.
OriginalGriff 15-Mar-18 16:30pm
   
No need to apologise, and it's not stupid - we all do it!
I often find I read what I meant to write ... :blush:

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



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