Solved

DataTable - Checking for existence of column (by name)

Posted on 2003-11-12
12
2,894 Views
Last Modified: 2013-11-19
How do I check for the existence of a particular column in a datatable (by name)?

My problem is that I fill a dataset by using an XML file with  optional elements. This means that certain rows do not contain those columns (missing elements). This is especially a problem with nested elements, since they will cause mutiple datatables in my dataset, but that is another story. Anyway, I need to do something like:

if (ColumnExists("ColumnName") {
  string myvalue = AccessColumnValue();
  // do other stuff with the column
}

How do I accomplish this? I have tried using IsNull and other things, but what I really need to know is if a column of a specified name exists in a given datatable for a given datarow. Any help suggestions are appreciated. I'm at wits end on this one.
0
Comment
Question by:brian_dallas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 28

Expert Comment

by:iboutchkine
ID: 9732973
You can read schema. her eis an example how to do it in Vb.NET

Dim cn As New OleDbConnection()
Dim cmd As New OleDbCommand()
Dim schemaTable As DataTable
Dim myReader As OleDbDataReader
Dim myField As DataRow
Dim myProperty As DataColumn

'Open a connection to the SQL Server Northwind database.
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=login;" & _
                      "Password=password;Initial Catalog=Northwind"
cn.Open()

'Retrieve records from the Employees table into a DataReader.
cmd.Connection = cn
cmd.CommandText = "SELECT * FROM Employees"
myReader = cmd.ExecuteReader()

'Retrieve column schema into a DataTable.
schemaTable = myReader.GetSchemaTable()

'For each field in the table...
For Each myField In schemaTable.Rows
    'For each property of the field...
     For Each myProperty In schemaTable.Columns
        'Display the field name and value.
         Console.WriteLine(myProperty.ColumnName & " = " & myField(myProperty).ToString())

'here check if field exists

     Next
     Console.WriteLine()

     'Pause.
     Console.ReadLine()
Next

'Always close the DataReader and Connection objects.
myReader.Close()
cn.Close()
0
 

Author Comment

by:brian_dallas
ID: 9733282
I'm sure that I'm missing something here, but my data is already in the form of an XMLDataDocument and has a structure similar to this:

<element1>
  <element2>
     <nested-element1/>
     <nested-element2/>
  </element2>
  <element3/>
</element1>
<second-element1>
  <element2>
     <nested-element1/>
  </element2>
  <element3/>
</second-element1>

Note how the <nested-element2/> tag does not appear in the second set of data, since it is optional in the schema. This causes a problem for me when the datatable gets built because when I loop through the rows, all of a sudden I have nothing called <nested-element2> in the second row. You see what I mean? How do I check as I move through the rows to make sure that the column is there?

Thanks.
0
 

Author Comment

by:brian_dallas
ID: 9733446
"<second-element1>" is only for distinction from the first "<element1>" -- in the real schema/xml, they have the same name.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:brian_dallas
ID: 9733463
AND, this is the .NET exception that I am trying to avoid:

"Column 'MISSINGCOLUMN' does not belong to table MYTABLE."

Thanks!
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 9733521
I was talking about reading Db schema to check if col exist, not XML schema.
You might try another approach, to execute SQL with the col name and see if it will create an error

sSQL = "select count(YourCol) from Table"

then execute scalar and see if error is generated
0
 

Author Comment

by:brian_dallas
ID: 9734552
I don't think I'm explaining this correctly or clearly enough. Let me try again. I build my own datatable with the columns the following way:

Add any columns outside the dataset using:

DataColumn dc = new DataColumn("FirstColumn")
dt.Columns.Add(dc)

Then, I add a bunch of columns from a dataset using:

for (System.Int32 iCol = 0; iCol < m_dsDataSource.Tables[0].Columns.Count; iCol ++ )
{
   //...do setup of column name and such
   dtDataTable.Columns.Add(dcColumn);
}

Then I add row information (this is where the problem occurs):

for (i = 0; i < m_dsDataSource.Tables[0].Rows.Count; i++)
{
    DataRow drDataRow = dtDataTable.NewRow();
    drDataRow["MyColumn"] = m_dsDataSource.Tables[0].Rows[i]["IAMHERE"];

    // PROBLEM AREA !!!
    drDataRow["MyPossiblyMissingColumn"] = m_dsDataSource.Tables[1].Rows[i]["MAYBEMISSING"];

    // ...etc...adding additional row information to the table created
}

How do I check to make sure that MAYBEMISSING is there before trying to access it and assign it to the drDataRow["MyPossiblyMissingColumn"]. If it is not there in the row information that is where I will get the exception.

Thanks, and I hope this is more clear...
0
 
LVL 6

Expert Comment

by:thomasdodds
ID: 9734716
did you try

if not isNothing(m_dsDataSource.Tables[1].Rows[i]["MAYBEMISSING"]) then
  ...
end if


not sure of the C# syntax (i'm a vb coder)

if (! IsNothing(m_dsDataSource.Tables[1].Rows[i]["MAYBEMISSING"])) {
...
}
0
 
LVL 4

Expert Comment

by:timmyt851
ID: 9735496
How about:

C#:       If (! IsNothing(m_dsDataSource.Tables[1].Columns["MAYBEMISSING"])) Then...
VB:        If Not IsNothing(m_dsDataSource.Tables(1).Columns("MAYBEMISSING")) Then...

?
0
 

Author Comment

by:brian_dallas
ID: 9735752
Nope, neither of the above solutions work -- I still get the exception when trying to access the column...even to compare it to null (or Nothing in the VB.NET world).
0
 
LVL 4

Accepted Solution

by:
timmyt851 earned 500 total points
ID: 9735886
VB: If ds.tables("mytable").columns.contains("mycolumn") then...

Make sure you are referencing the correct table, use tables(0) if all else fails.

TIM
0
 
LVL 6

Expert Comment

by:thomasdodds
ID: 9739072
just noticed that your table index is different in these lines of code ... is that a typo or on purpose?

   DataRow drDataRow = dtDataTable.NewRow();
    drDataRow["MyColumn"] = m_dsDataSource.Tables[0].Rows[i]["IAMHERE"]; // index = 0

    // PROBLEM AREA !!!
    drDataRow["MyPossiblyMissingColumn"] = m_dsDataSource.Tables[1].Rows[i]["MAYBEMISSING"]; // index = 1
0
 

Author Comment

by:brian_dallas
ID: 9743701
Yes, sorry...that was a typo in the example code only; I actually have several tables in my dataset, so I just forgot to change the index for one of the tables.

The answer was to use the .columns.contains(...)....I knew it had to be something simple.

Thanks!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Most of the sites are being standardized with W3C Web Standards. W3C provides lot of web standard services to the web. They have the web specification, process and documentation for all the web standards. You can apply HTML, CSS and Accessibility st…
Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

739 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