Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DataTable - Checking for existence of column (by name)

Posted on 2003-11-12
12
Medium Priority
?
2,902 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
Suggested Courses

926 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