WHERE {field name passed as a parameter} 'contains' {value passed as a parameter}

cuddling101 · #1 (**permalink**) August 25th, 2010, 07:31 AM

My standard Grid View display, or LINQ display, of my primary database is just how I want it, but I want to be able to make it display different sets of records based on parameters passed to it from a 'calling' page.

Code:

SELECT [ID], [Title], [Series_Available], [Available], [Authors_UK], [Illustrators_UK], [Authors_USA], [Illustrators_USA], [Book_Numbers], [ISBNs], [Publication_Dates], [Front_Cover_JPEGs] FROM [Titles]

what I want to be able to then do is have the system generate a varying WHERE clause based on the two parameters of field to test, and value to test, so that for example on one call it might generate

WHERE Available 'contains' "A"

BUT on another occasion it might be called on to generate

WHERE Authors_UK 'contains' "Notkin"

Data commentary

The contents of Available is an up to 16 character string with one, some or all of the letters A to P, each letter occuring, if at all, only once.

The contents of Authors_UK is a string such as "Gulkin, S; Notkin, J. J."

Bottom line questions -

1. Is there a 'contains' operator, or a function that can be called to do that? If it is a function, how would I incorporate that call to the building of the WHERE clause?

2. Can I pass a field name as a parameter and, if so, how do I do it?

Caveat

If these questions are beyond the scope of the Beginning book and more the scope of the Professional book, I am very happy to buy that book, and look to find the answers there, or on the forum for that book. Please advise accordingly.

Best regards

Philip

Imar · #2 (**permalink**) August 25th, 2010, 08:49 AM

Hi there,

It depends on where your data comes from. When using LINQ you can take a look at the Dynamic LINQ library: http://weblogs.asp.net/scottgu/archi...y-library.aspx

For a SqlDataSource you could habdle the Selecting event and change the CommandText:

Code:

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
  e.Command.CommandText+= " WHERE Description LIKE '%whatever%'";
}

Two things worth noting:

1. I am using LIKE with % symbols which acts like the contains you talked

2. Make sure you don't open your code for SQL injection. This dynamic nature of SQL statements make it easy to inject something like:

; DROP DATABASE MASTER; --

instead of the literal whatever, which would select all records from your table and then delete your master database. No fun.... ;-)

Imar

Imar · #3 (**permalink**) August 25th, 2010, 08:52 AM

BTW: these kind of questions are not necessarily beyond the scope of the book, but they are a bit off-topic. You're better off posting in a more general ASP.NET 4 forum like this one: http://p2p.wrox.com/asp-net-4-general-discussion-561/

Posting there will likely draw more attention to your questions, and makes this forum a bit more focused on the book.

Cheers,

Imar