Dataset to select/filter table rows

[Shailesh Patel]

Hi,
I have a dataset created from sql query. The sql query hsa sql function and
join to another table.
Now, I want to filter/select certain rows based on criteria and creating
html string. When it is over, I use the same dataset and user another
selection criteria and get different rows , process them and repeat the same
procedure with another criteria.

I am using:
oDs.Tables(0).Select("Cat IN ('A', 'B')")

but does not seem to be working. Let me know if I am missing anything.

Thank you in advance.

Shailesh

 

[Cor Ligthert[MVP]]

Shailesh,

I am not sure if your expression is right, that is always a pain (and a
reason why there is Linq now), be aware that it is not SQL script.

However your procedure gives a result as a collection of Datarow, I have the
idea that you want to use it in another way?

Cor

 

[W.G. Ryan]

Shailesh:

When you say it isn't working, what is happening? Is it returning the wrong
values, no values etc? I used the following code (you can take out the
PrimaryKey - i just did that for sorting) and it works fine. The syntax
is correct - but perhaps there's something with rowstate if the rows haven't
been added or whatever. Try this code and tell me where it's essentially
different from yours Also, just to be safe, throw in a
Debug.Assert(oDs.Tables(0).Rows.Count = WHATEVERYOUAREEXPECTING IT, "There
is a different number of rows here"); Also, like Cor mentions, you can use
LINQ to handle searches like this. I wrote an Extension method to handle
"IN" specifically b/c i believe it was dropped at the moment - if you can
use linq, let me know and I can send you the extension class I have - not
promising it's the best written, but it works

DataTable dt = new DataTable("MyTable");

DataColumn Category = new DataColumn("CAT", typeof(System.String));

DataColumn Name = new DataColumn("ProductName", typeof(System.String));


dt.Columns.Add(Category);

dt.Columns.Add(Name);

dt.PrimaryKey = new DataColumn[] { Name };

DataRow dro = dt.NewRow();

dro[0] = "A";

dro[1] = "Vista";

dt.Rows.Add(dro);

dro = dt.NewRow();

dro[0] = "A";

dro[1] = "XP";

dt.Rows.Add(dro);

dro = dt.NewRow();

dro[0] = "C";

dro[1] = "98";

dt.Rows.Add(dro);

dro = dt.NewRow();

dro[0] = "B";

dro[1] = "ME";

dt.Rows.Add(dro);

dro = dt.NewRow();

dro[0] = "B";

dro[1] = "2000";

dt.Rows.Add(dro);

dro = dt.NewRow();

dro[0] = "C";

dro[1] = "95";

dt.Rows.Add(dro);

dt.AcceptChanges();

DataRow[] Results = dt.Select("CAT IN ('A', 'B')");


foreach (DataRow oneRow in Results)

{

Debug.WriteLine( String.Format("Category: {0} - ProductName:
{1}",oneRow[0].ToString(), oneRow[1].ToString()));

}



Cordially,

W.G. Ryan, MVP

www.magenic.com | www.msmvps.com/WilliamRyan

 

[Cor Ligthert[MVP]]

Shailesh,

I have translated the code from Bill to VB for Net because you are obviously
using that.
It was also to see how it was in version VB 2008 (be aware that this is used
with the standard setting of Option Infer On)
In version before 2008 with Option Strict ON you have to set the used Types
(classes) like Bill did that in his C# sample)
For those interesting in this, be aware that this is strongly typed code
without late binding. (Beside the in the C# sample as well not completely
strongly typed created datatable).

\\\
Dim dt = New DataTable("MyTable")
Dim Category = New DataColumn("CAT")
Dim Name = New DataColumn("ProductName")
dt.Columns.Add(Category)
dt.Columns.Add(Name)
dt.PrimaryKey = New DataColumn() {Name}
Dim dro = dt.NewRow()
dro(0) = "A"
dro(1) = "Vista"
dt.Rows.Add(dro)
dro = dt.NewRow()
dro(0) = "A"
dro(1) = "XP"
dt.Rows.Add(dro)
dro = dt.NewRow()
dro(0) = "C"
dro(1) = "98"
dt.Rows.Add(dro)
dro = dt.NewRow()
dro(0) = "B"
dro(1) = "ME"
dt.Rows.Add(dro)
dro = dt.NewRow()
dro(0) = "B"
dro(1) = "2000"
dt.Rows.Add(dro)
dro = dt.NewRow()
dro(0) = "C"
dro(1) = "95"
dt.Rows.Add(dro)
dt.AcceptChanges() 'Not really needed for the sample, however to
keep it the same as the C# 3.0 version
Dim Results = dt.Select("CAT IN ('A', 'B')")
For Each oneRow In Results
Debug.WriteLine(String.Format("Category: {0} - ProductName:
{1}", oneRow(0), oneRow(1)))
Next
///

Cor