Datatable select

[JackDurden]

How would I find a row with a column value? Say Im looking for people with the name "Lance" then once ive found Lance display the row of info for lance. Ive tried using select but cant seem to get it to work. Here is my code.

using System;
using System.Collections;
using System.Data;

namespace Table
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable table = new DataTable("records");
            DataColumn Col;
            DataRow Row;

            Col = new DataColumn();
            Col.DataType = System.Type.GetType("System.Int32");
            Col.ColumnName = "ID";
            table.Columns.Add(Col);


            Col = new DataColumn();
            Col.DataType = System.Type.GetType("System.String");
            Col.ColumnName = "FIRST";
            table.Columns.Add(Col);


            Col = new DataColumn();
            Col.DataType = System.Type.GetType("System.Int32");
            Col.ColumnName = "AGE";
            table.Columns.Add(Col);

            DataColumn[] PrimKey = new DataColumn[1];
            PrimKey[0] = table.Columns["ID"];
            table.PrimaryKey = PrimKey;
 
            Row = table.NewRow();
            Row["ID"] = 1;
            Row["FIRST"] = "Steve";
            Row["AGE"] = 34;
            table.Rows.Add(Row);

            Row = table.NewRow();
            Row["ID"] = 2;
            Row["FIRST"] = "Peter";
            Row["AGE"] = 54;
            table.Rows.Add(Row);

            Row = table.NewRow();
            Row["ID"] = 3;
            Row["FIRST"] = "Lance";
            Row["AGE"] = 94;
            table.Rows.Add(Row);

            // Display the data
            foreach (DataRow m in table.Rows)
            {
                Console.Write("{0} ", m["ID"]);
                Console.Write("{0} ", m["FIRST"]);
                Console.Write("{0} ", m["AGE"]);
                Console.WriteLine();
            }
        }
    }
}

[Ramy Mahrous]

foreach(DataRow row in table.Rows)
{
if(row["columnName"] == value)
//display the row, otherwise escape.
}

[JerryShaw]

To use the select, try this:

DataRow[] rows = table.Select("[First]='Lance'");
if(rows.Length > 0)
{
      Console.Write("{0} ", rows[0]["ID"]);
      Console.Write("{0} ", rows[0]["FIRST"]);
      Console.Write("{0} ", row[0]["AGE"]);
      Console.WriteLine();
}

// Jerry

[JackDurden]

Yes JerryShaw thats exactly what i was looking for, thanks! Just curious what if I wanted to find more then one row that had the name "Lance" in it?

[JerryShaw]

The rows array contains all rows where column [First] = 'Lance' so all you need to do is iterate through the array of rows.

DataRow[] rows = table.Select("[First]='Lance'");
foreach(DataRow row in rows)
{
      Console.Write("{0} ", row["ID"]);
      Console.Write("{0} ", row["FIRST"]);
      Console.Write("{0} ", row["AGE"]);
      Console.WriteLine();
}

BTW, there is a second parameter for the select method that allows you to set the order the rows are returned in.

// Jerry

[JackDurden]

I tried that new bit of code and it just prints out the same line twice, which is row 3 when it should print out rows 3 and 4. Here is the code im working with..I added another row, so there are two rows with the name Lance.

Can this line hold more then one row at a time?
DataRow[] rows = table.Select("[First]='Lance'");

Thanks for the help!

using System;
using System.Collections;
using System.Data;

namespace Table
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable table = new DataTable("records");
            DataColumn Col;
            DataRow Row;

            Col = new DataColumn();
            Col.DataType = System.Type.GetType("System.Int32");
            Col.ColumnName = "ID";
            table.Columns.Add(Col);


            Col = new DataColumn();
            Col.DataType = System.Type.GetType("System.String");
            Col.ColumnName = "FIRST";
            table.Columns.Add(Col);


            Col = new DataColumn();
            Col.DataType = System.Type.GetType("System.Int32");
            Col.ColumnName = "AGE";
            table.Columns.Add(Col);

            DataColumn[] PrimKey = new DataColumn[1];
            PrimKey[0] = table.Columns["ID"];
            table.PrimaryKey = PrimKey;
 
            Row = table.NewRow();
            Row["ID"] = 1;
            Row["FIRST"] = "Steve";
            Row["AGE"] = 34;
            table.Rows.Add(Row);

            Row = table.NewRow();
            Row["ID"] = 2;
            Row["FIRST"] = "Peter";
            Row["AGE"] = 54;
            table.Rows.Add(Row);

            Row = table.NewRow();
            Row["ID"] = 3;
            Row["FIRST"] = "Lance";
            Row["AGE"] = 94;
            table.Rows.Add(Row);

            Row = table.NewRow();
            Row["ID"] = 4;
            Row["FIRST"] = "Lance";
            Row["AGE"] = 61;
            table.Rows.Add(Row);

            // Display the data
            foreach (DataRow m in table.Rows)
            {
                Console.Write("{0} ", m["ID"]);
                Console.Write("{0} ", m["FIRST"]);
                Console.Write("{0} ", m["AGE"]);
                Console.WriteLine();
            }

            Console.WriteLine("\n");
                DataRow[] rows = table.Select("[First]='Lance'");
                foreach (DataRow row in rows)
                {
                    Console.Write("{0} ", rows[0]["ID"]);
                    Console.Write("{0} ", rows[0]["FIRST"]);
                    Console.Write("{0} ", rows[0]["AGE"]);
                    Console.WriteLine();
                }
          
        }
    }
}

[JerryShaw]

:)
You need to use the row not rows[0]

foreach (DataRow row in rows)
                {
                    Console.Write("{0} ",  row["ID"]; // rows[0]["ID"]);
                    Console.Write("{0} ", row["FIRST"]; //rows[0]["FIRST"]);
                    Console.Write("{0} ", row["AGE"]; //rows[0]["AGE"]);
                    Console.WriteLine();
                }

[flavioweb1]

Staff, Why don't you do create DataTable to store the data separately, then to create the DataTable is just using a "for"

Then you have something like this:
''-----------
dim dataArray as ArrayList = GetData();
for integer i = 0 to dataArray.count - 1
{
Row = table.NewRow();
Row["ID"] = dataArray(0)(i);
Row["FIRST"] = dataArray(1)(i);
Row["AGE"] = dataArray(2)(i);
table.Rows.Add(Row);
}
next
''-----------
With this you create evething rows your datatable, sure you need store data in ArrayList, and you can do for columns too.
Sorry my mistake english, I'm Brazilian, my english is basic.

:-)