help with select query in asp.net

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with select query in asp.net

Author

Topic

jak3f
Starting Member

33 Posts

Posted - 2009-12-01 : 14:54:47

I am planning on filtering a GridView with a few DropDownLists.

I have one drop down list with all of the columns, one drop down list with operators like =, <,>, IS NULL, IS NOT NULL, and a textbox that they can put a value in

I want to search with a sql statement hypothetically like: SELECT * FROM Table1 WHERE 'ddlColumns.SelectedItem' 'ddlOperator.SelectedItem' 'txtFilter.Text'

So for example someone was like: I want results where Quantity(ddlColumn.selecteditem) < (ddlOperator.selecteditem) 400(txtfilter.text)

Heres my code so far
= "SELECT CustomerID, Name, Address, City, State, ZipCode, Phone, Email, Efin FROM Customers WHERE '%" + ddlColumns.SelectedItem.Text + ddlOperator.SelectedItem.Text + txtFilter.Text + "%'";

And that returns the following error message:

An expression of non-boolean type specified in a context where a condition is expected, near '%Name=fred%'.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-01 : 15:07:15

One way is to create a stored procedure that will accept all these values as input. You need to use dynamic sql inside the stored proc like...

SET @SQL = 'SELECT CustomerID, Name, Address, City, State, ZipCode, Phone, Email, Efin FROM Customers WHERE ' + quotename(@ColumnName) +  @Operator + quotename(@Value,'''')

EXEC (@SQL)

Also, its better you read about SQL Injection...before attempting any of this.

jak3f
Starting Member

33 Posts

Posted - 2009-12-01 : 15:12:01

hmm cool, never thought about a sproc. I have never really set one up like this. any more on that kinda execution for what I am tryin to do would be excellent.
yeah i know about the sql injection, but its on an intranet in our small company so I am pretty sure noone will be attacking my db. Even if I was worrying about sql injection, how could I prevent that? (kinda going off course here but I am just curious)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-01 : 15:27:05

Ur sproc wud look something like this...

CREATE PROCEDURE dbo.uspSample
(
@ColumnName SYSNAME,
@Operator VARCHAR(2),
@Value VARCHAR(100)
)
AS

SET NOCOUNT ON

DECLARE @SQL VARCHAR(2000)

SET @SQL = 'SELECT CustomerID, Name, Address, City, State, ZipCode, Phone, Email, Efin FROM Customers WHERE ' + quotename(@ColumnName) +  @Operator + quotename(@Value,'''')

EXEC (@SQL)

Just search for SQL injection in sqlteam...you will find all you need.

EDIT: The QUOTENAME is something I learnt to use from Peso's solutions..it can, in a way, protect from SQL injection.

jak3f
Starting Member

33 Posts

Posted - 2009-12-01 : 18:40:51

vijayisonly: thank you for the elegant solution, however its a bit overkill for my situation. Plus its a little advanced for my lil brain.
If you can help me with the following situation -
ok I got the query I wanted with this code:
SqlDataSource1.SelectCommand = "SELECT CustomerID, Name, Address, City, State, ZipCode, Phone, Email, Efin FROM Customers WHERE " + ddlColumns.SelectedItem.Text + ddlOperator.SelectedItem.Text + "'" + txtFilter.Text + "'";

BUT it only relays exact solutions, I need to have the wildcard built it there.
Right now if i search a record where name = 'Joe', it brings up Joe but not Joel. How can i forumlate this select query to include the wildcard?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-02 : 11:15:22

You cannot pass an "=" operator and expect Joel in your result..you need to use "LIKE" as below.

SqlDataSource1.SelectCommand = "SELECT CustomerID, Name, Address, City, State, ZipCode, Phone, Email, Efin FROM Customers WHERE " + ddlColumns.SelectedItem.Text + "LIKE" + "'" + txtFilter.Text + "'%";

jak3f
Starting Member

33 Posts

Posted - 2009-12-02 : 11:48:40

awesome, i got it to work:
SqlDataSource1.SelectCommand = "SELECT CustomerID, Name, Address, City, State, ZipCode, Phone, Email, Efin FROM Customers WHERE " + ddlColumns.SelectedItem.Text + " " + ddlOperator.SelectedItem.Text + " " + "'" + "%" + txtFilter.Text + "%" + "'";
GridView1.DataBind();

One new issue though,
when I run a query selecting * from customers where EFIN is null it doesnt return all the values.
Instead I run a query selecting * from customers where EFIN < 1 and it gives me all the correct values.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-02 : 12:05:04

Can't really say unless I can see your data...

I'm hoping EFIN has null values in it.

and

EFIN = ''

is different from

EFIN = 'null'

is different from

EFIN is null

jak3f
Starting Member

33 Posts

Posted - 2009-12-02 : 13:16:40

vijayisonly,
would I be able to select * from Customers where EFIN LIKE ''
THEN
Update Customers Set EFIN = null WHERE EFIN LIKE ''
that type of query possible?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources