Here's a brain-teaser for you...DataTable.Select 1

[andegre]

Here is my output FROM THE IMMEDIATE WINDOW:

dt.Select("PWDPermitStatusID = 1")
{System.Data.DataRow[1]}
    [0]: {System.Data.DataRow}
dt.Select("PWDPermitStatusID = 2")
{System.Data.DataRow[1]}
    [0]: {System.Data.DataRow}
dt.Select("PWDPermitStatusID = 3")
{System.Data.DataRow[1]}
    [0]: {System.Data.DataRow}
dt.Select("PWDPermitStatusID = 4")
{System.Data.DataRow[1]}
    [0]: {System.Data.DataRow}
dt.Select("PWDPermitStatusID = 5")
{System.Data.DataRow[1]}
    [0]: {System.Data.DataRow}
dt.Select("PWDPermitStatusID = 6")
{System.Data.DataRow[1]}
    [0]: {System.Data.DataRow}
dt.Select("PWDPermitStatusID = 7")
{System.Data.DataRow[1]}
    [0]: {System.Data.DataRow}
dt.Select("PWDPermitStatusID = 8")
{System.Data.DataRow[1]}
    [0]: {System.Data.DataRow}
dt.Select("PWDPermitStatusID = 9")
{System.Data.DataRow[1]}
    [0]: {System.Data.DataRow}
dt.Select("PWDPermitStatusID = 10")
{System.Data.DataRow[0]}
dt.Select("PWDPermitStatusID = 97")
{System.Data.DataRow[1]}
    [0]: {System.Data.DataRow}
dt.Select("PWDPermitStatusID = 98")
{System.Data.DataRow[1]}
    [0]: {System.Data.DataRow}
dt.Select("PWDPermitStatusID = 99")
{System.Data.DataRow[1]}
    [0]: {System.Data.DataRow}

And here is the query executed in Management Studio (and is the same query/stored procedure that put the data in the dt variable):

1	Lost	Lost
2	Stolen	Stolen
3	Destroyed	Destroyed
4	Damaged	Damaged
5	Revoked	Revoked
6	Deceased	Deceased
7	Expired	Expired
8	Corrected	Corrected
9	Voided	Voided
10	Valid	Valid
97	RtrndUnDel	Returned Undeliverable
98	PrgmReIss	Program Re-Issue
99	Other	Other

Why is dt.Select("PWDPermitStatusID = 10") not working?

 

[kss444]

At first glance all the output from the immediate window under dt.Select("PWDPermitStatusID = 10") it is shows
{System.Data.DataRow[0]} and for all the rest it shows
{System.Data.DataRow[1]}.

your data row 0 or 1.

Ordinary Programmer

 

[andegre]

I'm doing a foreach statement like this:

foreach (DataRow row in dt.Select("PWDPermitStatusID = " + textBox.Text)
{
    valid = true;
)

This works for all of the values except for 10.

In the immediate window, I believe that was returning the row count for the different values I was entering in the filter statement.

I still don't understand why the statement is not working...

 

[PGO01]

So you are saying this:

1. Running the query from .NET does not return a record with a PWDPermitStatus of 10.
2. Running the query directly from the database does return a record with a PWDPermitStatus of 10.

If yes, then I would suggest double checking that you are using the correct (same) database for both connections.

If you are then it's probably something in your code. You could do a SQL Trace from Tools >> SQL Server Profiler >> Select the connection >> click on the top-left icon (New Trace) >> select the TSQL template >> start the trace >> execute your .NET code >> stop the trace.

Then go through the trace results and see what .NET was actually submitting to the database.

 

[andegre]

DId profiler and all it caught was:

exec PWDGetLookupValues

In my code, I stepped into it right before it does the foreach statement and I did these queries in the Immediate Window

dsLookupValues.Tables["PWDPermitStatus"].Rows[9][1]
"Valid"
dsLookupValues.Tables["PWDPermitStatus"].Rows[9][0]
"10"
dsLookupValues.Tables["PWDPermitStatus"].Columns[1].ColumnName
"LookupValue"
dsLookupValues.Tables["PWDPermitStatus"].Columns[0].ColumnName
"PWDPermitStatusID"
dsLookupValues.Tables["PWDPermitStatus"].Select("PWDPermitStatusID = 10")
{System.Data.DataRow[0]}
dsLookupValues.Tables["PWDPermitStatus"].Select("PWDPermitStatusID = 9")
{System.Data.DataRow[1]}
    [0]: {System.Data.DataRow}

This verifies that the value 10, Valid does exist in the dataset (datatable), this is starting to make me mad!

 

[PGO01]

Is your connection in .NET using the correct database?

 

[andegre]

Yes, I have only added the values to one database.

 

[PGO01]

Very strange, dare I say a bug?

I looked a little deeper into what the DataTable.Select() does using .Net Reflector and it actually instantiates an internal sealed class called Select passing in your filter text to the constructor and returns SelectRows() on that object.

Still don't see why it is not working though, so I can only suggest getting your data a different way.

 

[andegre]

Thanks for your help PG, I'll switch and do a regular for loop with a counter instead of doing a foreach...

 

[andegre]

FYI - my code works just fine doing it like this:

                for (int counter = 0; counter < dsLookupValues.Tables["PWDPermitStatus"].Rows.Count; counter++)
                {
                    if (tb.Text == dsLookupValues.Tables["PWDPermitStatus"].Rows[counter][0].ToString())
                    {
                        valid = true;
                        break;
                    }
                }