Solved

Error : Column 'Employee_Key' does not belong to table Employee.

Posted on 2010-11-08
13
787 Views
Last Modified: 2012-05-10
Hi,

I am trying to popluate a datarow (please the attached code) with values from Active Directory and i get the error 'Column 'Employee_Key' does not belong to table Employee' on line  dr["Employee_Key"] = dey.Properties["title"].Value ?? string.Empty;

All i am trying to do is load a schema of the Employee table from the database and apply it to dt (datatable) and then loop through all the rows of Active Directory and built a table with that data.

however when i create a new row and try to assign values i get the above error

Please, could you help me figure out why i am getting this error... I searched in google but of no avail :'( I'm on a dead end :((









public DataTable GetData(Entities.Config.EtlConfig config)
        {
            var s = new SqlLoader();
            var dt = s.GetSchemaTable(config.SqlTableToLoad);
            string setdate = DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss");
            dt.TableName = "Employee";

            try
            {
                DirectoryEntry de = GetDirectoryEntry();
                DirectorySearcher ds = new DirectorySearcher(de);
                SearchResultCollection results = ds.FindAll();
                foreach (SearchResult result in results)
                {
                    DirectoryEntry dey = GetDirectoryEntry();

                    DataRow dr = dt.NewRow();

                    dr["Employee_Key"] = dey.Properties["title"].Value ?? string.Empty; 
                    dr["Employee_Name"] = dey.Properties["cn"].Value;
                    dr["Employee_Full_Name"] = dey.Properties["distinguishedName"].Value;
                    dr["Employee_Phone_Number"] = dey.Properties["telephoneNumber"];
                    dr["Email_Address"] = dey.Properties["mail"];
                    dr["Business_Unit"] = dey.Properties["department"];
                    dr["Supervisor_Full_Name"] = dey.Properties["manager"];
                    dr["Row_Update_Date"] = setdate;
                    dr["Source"] = config.Source;
                    dt.Rows.Add(dr);
                    dey.Close();
                }
                de.Close();

                return dt;
            }
            catch(Exception e)
            {
                _log.Error(e.Message);
                throw new CustomException(e,ErrorType.DataImporter,e.Message);
            }
        }

        public DataTable GetSchemaTable(string sqlTable)
        {   
            DataTable dt;
            try
            {
                _dbManager.Open();
               SqlDataReader rd;
               using (rd= (SqlDataReader)_dbManager.ExecuteReader(CommandType.Text, "Select top 1 * from " + sqlTable + " NOLOCK"))
               {
                   dt = rd.GetSchemaTable();
               }
            }
            catch (Exception ex)
            {
                _log.Error("Error getting schema for table : " + sqlTable + " " + ex.Message);
                throw new CustomException(ex,ErrorType.SQLImport,"Error getting schema for table : " + sqlTable + " " +  ex.Message);
               
            }
            finally
            {
                  _dbManager.Dispose();
            }

            return dt;
        }

Open in new window

0
Comment
Question by:2ooth
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 34085810
>>>i get the error 'Column 'Employee_Key' does not belong to table Employee'

This might sound obvious but does the table Employee have a column (field) called Employee_Key ?  (Check the spelling)
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 34085834
I think that it is not seeing that as a column name.  Before you try accessing the column try this:

foreach(DataColumn column in dt.Columns.)
{
      Response.Write(column.ColumnName);
}

Then see what you get...
0
 
LVL 8

Expert Comment

by:InternalStatic
ID: 34085907
This may or may not have any relevance to your error (though I'm thinking it probably does), but where is variable _dbmanager initialized, if it is at all? If it's not done properly, stuff like this can happen. May also want to check out your SqlLoader definition (unless I'm just not seeing it in the MSDN for some reason).
Definitely try mrichmon's code, and poste the results (if any) unless they lead you to a direct conclusion and you no longer need our assistance :P
0
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:2ooth
ID: 34086070
Hi,

Thank you for your response.. here is what i get when i do foreach loop throught dt.columns

 string ColumnValues = "";
            foreach(DataColumn column in dt.Columns)
            {
                ColumnValues += column.ColumnName + "\n";
            }

ColumnName
ColumnOrdinal
ColumnSize
NumericPrecision
NumericScale
IsUnique
IsKey
BaseServerName
BaseCatalogName
BaseColumnName
BaseSchemaName
BaseTableName
DataType
AllowDBNull
ProviderType
IsAliased
IsExpression
IsIdentity
IsAutoIncrement
IsRowVersion
IsHidden
IsLong
IsReadOnly
ProviderSpecificDataType
DataTypeName
XmlSchemaCollectionDatabase
XmlSchemaCollectionOwningSchema
XmlSchemaCollectionName
UdtAssemblyQualifiedName
NonVersionedProviderType
IsColumnSet

So its obvious that column names are not being returned :((

What is going on here? Why is it not returning the column name?
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 34086110
It sounds like you are getting generic information - not your actual table...
0
 

Author Comment

by:2ooth
ID: 34086155
How can i get information from the actual table?
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 34086195
Looking at GetSchemaTable in the help files then that is what should be returned.


0
 

Author Comment

by:2ooth
ID: 34086221
Ok.. Now i am truly baffled.....


This is what happens when i loop through the datarows instead of the dataColumns

           string rowValues = "";
            foreach (DataRow dr in dt.Rows)
            {
                rowValues += dr["ColumnName"].ToString() + "\n";
            }


Employee_Key
Address_City
Address_Line_1
Address_Line_2
Address_State
Birth_Date
Business_Unit
Country
Email_Address
Employee_Job_Cd
Employee_Job_Desc
Employee_Name
Employment_Status_Cd
Gender
Hire_Date
Holding_Period_Days
Is_Terminated
Marital_Status_Cd
Employee_Phone_Number
SSN
Supervisor_Name
Supervisor_Phone_Number
Terminate_Date
Zip_Code
Employee_Full_Name
Supervisor_Full_Name
Supervisor_Id
Primary_Region_Cd
Manual_Select_Approval_Level
Row_Update_Date
Source
Supervisor_Email_Address
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 34086243
The schema is how the table (or rather fields in it) are defined, that is what you are working on here.
0
 

Author Comment

by:2ooth
ID: 34086324
how can i resolve this issue?

I am really reaching a dead end here?

:((
0
 

Author Comment

by:2ooth
ID: 34086377
Someone???

Anyone??

0
 

Accepted Solution

by:
2ooth earned 0 total points
ID: 34086797
OK.. I found the solution through a work around...

0
 

Author Closing Comment

by:2ooth
ID: 34134635
Resolved by using

                SqlConnection conn = new SqlConnection(ConfigManager.DbConnection());
                SqlCommand cmd = new SqlCommand("Select top 0 * from " + sqlTable, conn);
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(table);
                conn.Close();
 
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question