Solved

DataTable - Checking for existence of column (by name)

Posted on 2003-11-12
12
2,884 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

773 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