Solved

DataTable - Checking for existence of column (by name)

Posted on 2003-11-12
12
2,873 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I will show you how to create a ASP.NET Captcha control without using any HTTP HANDELRS or what so ever. you can easily plug it into your web pages. For Example a = 2 + 3 (where 2 and 3 are 2 random numbers) Session("Answer") = 5 then we…
Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
The viewer will learn how to dynamically set the form action using jQuery.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now