Query in DataRow.Select

Hi,

    I want to place a query to get all rows which matches the input string with specified column for this i want to do a compare on lower case how it is possible.

if employeename is a column in a table , i want to find all employees with  name= "xyz" so i want to do a comparison in name and also both sides should be in lowercase.

datarow.select("LOWER(employeename) = " + name.ToLower().Trim())

the above is invalid, how can i achieve the above result.

Thanks & Regards
Ajay
ajaybhalekarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anarki_jimbelSenior DeveloperCommented:
First, if you want to find rows you call datatable.select(), not datarow.select()
Try:   datarow.select("employeename = '" + name.ToLower().Trim() + "'")
boy8964Commented:
datarow.select("LOWER(employeename) = " + name + ".ToLower().Trim()")
anarki_jimbelSenior DeveloperCommented:
Sorry, datatable.select("employeename = '" + name.ToLower().Trim() + "'")

Try the code below (don't use LOWER - it will crash your application)

        private void button1_Click(object sender, EventArgs e)
        {
            DataTable tbl = new DataTable();
            DataColumn col = new DataColumn();
            col.ColumnName = "employeename";
            tbl.Columns.Add(col);

            DataRow r0 = tbl.NewRow();
            r0[0] = "XyZ";
            DataRow r1 = tbl.NewRow();
            r1[0] = "XYZ";
            DataRow r2 = tbl.NewRow();
            r2[0] = "asd";
            DataRow r3 = tbl.NewRow();
            r3[0] = "xyz";

            tbl.Rows.Add(r0);
            tbl.Rows.Add(r1);
            tbl.Rows.Add(r2);
            tbl.Rows.Add(r3);

            string name = "xyz ";
            // SEE HERE!
            DataRow[] rs = tbl.Select("employeename = '" + name.ToLower().Trim() + "'");

            string rowsToString = "";
            foreach (DataRow r in rs)
            {
                rowsToString += r[0] + "\r\n";
            }
            MessageBox.Show(rowsToString);
        }

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

shtsonCommented:
Hai2u,

In SQL, strings are distinguished by a single comma (') on each side

when filtering a data set, you are creating a SQL where clause, and need the same thing.

so from your line: datarow.select("LOWER(employeename) = " + name.ToLower().Trim())

you need to add those commas, like so:

datarow.select("LOWER(employeename) = '" + name.ToLower().Trim() + "'")

Hope I help,
Shai
cjardCommented:
shtson, a single comma up in the air like this: '

is called an apostrophe :)
shtsonCommented:
cjard, ty

Didn't know that as I'm not a native english speaker :o
cjardCommented:
OP

here is some info on other commands implemented for datatables...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp


I dont know if LOWER is actually implemented, but you may find that datasets understand a limited subset of t-sql as both are microsoft technologies

Other docuemntation seems to indicate that setting the datatable's CaseSensitive property to false will let you perform case insensitive searching.
Do also remember that you can rewrite the Fill() method to fill case insensitively from the database. If you are selecting often, you may wish to utilise the database more, as it will undoubtedly be better at more complex evaluations for your select
grayeCommented:
Actually, just adust the DataTable's CaseSensitive property to make the comparision case insenstive and use the normal syntax

DataRowCollection.Select("EmployeeName='" + name + "'");
anarki_jimbelSenior DeveloperCommented:
2 shtson
What 't the point to repeat somebody ideas? Look at my code - I use apostrophes, or single quotas if you want.
This is just common in SQL.

Again, I posted FULLY WORKING piece of code to search in a table. What is the point insist on row.select?
All later posts just repeat mine.

It is good idea to try to write working code before one wants to say something...

Search in a datatable is case insensitive.
anarki_jimbelSenior DeveloperCommented:
Sorry, misprint: Search in a datatable is case insensitive  on default.

As graye pointed set datatable.CaseSensitive = false for peace of mind.
shtsonCommented:
anarki_jimbel,

His error was forgetting a comma, you didn't teach him what his mistake was - you just blabbered out code.

Please try and be more helpful and less of a "here, take code, copy, it works" guy.

kthxbai
Shai
anarki_jimbelSenior DeveloperCommented:
2 shtson

Main error was using datarow.select instead of datatable.select. Second error was using unsupported 'LOWER'.
And nothing can teach better than WORKING example (by the way I ran it before posting as you may reckon,
not just "blabbered it out" like you did :)).
Using single quotation marks (not commas! - English is not my native language either but still... using wrong terms leads to more confusion:))
should be obvious I believe for anyone with most basic knowledge of sql.

If you believe writing code to assist somebody is not helpful... Hmmm... OK then..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.