Is there a quick way to select from sqldatabase using multiple dropdownlist selected values?

Question

0.00/5 (No votes)

See more:

What I am trying to do is fill a gridview with data from what the user selects from a dropdownlist, or multiple ddl's. Kind of like a report based on their selections and different combinations of the dropdownlists. I can get the data I need using If-else statements on button click event. Is there a shorter way to do this?

I'll explain further.
I have 3 ddl's and a show button. Also a gridview hidden until the click event fired.

ddl1 is a list of employees, ddl2 is a list of expenses, ddl3 is a list of units. All with datatext and datavalues.

I am also selecting from 4 tables to get the results...

If ddl1.selectedvalue = "1" and ddl2 and ddl3 values are "" then

VB

"SELECT * FROM TRXTransaction INNER JOIN EXPExpense ON EXPExpenseID=TRXEXPID INNER JOIN SUBContractor ON SUBConID=TRXSCID LEFT JOIN UNIUnits ON UNIID=TRXUNIID WHERE SUBConID LIKE '%" & (PersonDDL.SelectedValue) & "%' AND TRXDate BETWEEN '" & stb1.Text & "' AND '" & stb2.Text & "' ORDER BY SUBConTitle, TRXDate ASC"

Now I want to "SELECT the same where ddl1.selectedvalue = '1' and ddl2.selectedvalue = '13'

VB

"SELECT * FROM TRXTransaction INNER JOIN EXPExpense ON EXPExpenseID=TRXEXPID INNER JOIN SUBContractor ON SUBConID=TRXSCID LEFT JOIN UNIUnits ON UNIID=TRXUNIID WHERE SUBConID LIKE '%" & (PersonDDL.SelectedValue) & "%' AND EXPExpenseID LIKE '%" & (ExpenseDDL.SelectedValue) & "%' AND UNIID LIKE '%" & (UnitDDL.SelectedValue) & "%' AND TRXDate BETWEEN '" & stb1.Text & "' AND '" & stb2.Text & "' ORDER BY SUBConTitle, TRXDate ASC"

Now SELECT where ddl1.SV = '1' and ddl2.SV = '13' and ddl3.SV = '92'
Then further down the line: ddl1 ="" and ddl2='6' and ddl3='80'

There's got to be a faster way, and right now I'm too burnt out to even think about it, i would have to write 27 IF,Else statements to acheive all of this.

can anyone help me with a faster way?

Posted 13-Apr-13 10:14am

PTG.Jake

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

db7uk · Answer 1 · 2013-04-13T12:27:00

One way would be to create a stored procedure like:

SQL

ALTER PROCEDURE dbo.usp_SomeProc
	@TrxStartDate DATETIME,
	@TrxEndDate DATETIME,
	@PersonValue VARCHAR(10) = '',
	@ExpenseValue VARCHAR(10) = '',
	@UnitValue VARCHAR(10) = ''
AS
BEGIN
	SET NOCOUNT ON;

    DECLARE @Sql VARCHAR(MAX)

	-- Set the base qyery text first
	SET @Sql = 'SELECT * 
				FROM TRXTransaction 
					INNER JOIN EXPExpense ON EXPExpenseID = TRXEXPID 
					INNER JOIN SUBContractor ON SUBConID = TRXSCID 
					LEFT JOIN UNIUnits ON UNIID = TRXUNIID
				WHERE (SUBConID LIKE ''%' + @PersonValue + '%'')
				AND (TRXDate BETWEEN CONVERT(DateTime, ''' + CONVERT(VARCHAR, ISNULL(@TrxStartDate, GETDATE())) + ''') AND CONVERT(DATETIME, ''' + CONVERT(VARCHAR, ISNULL(@TrxEndDate, GETDATE())) + '''))'

	-- build extra where clauses
	IF (NOT @ExpenseValue = '')
		SET @Sql = @Sql + ' AND (EXPExpenseID LIKE ''%' + @ExpenseValue + '%'')'

	IF (NOT @UnitValue = '')
		SET @Sql = @Sql + ' AND (UNIID LIKE ''%' + @UnitValue + '%'')'

	-- add order by
	SET @Sql = @Sql + ' ORDER BY ORDER BY SUBConTitle, TRXDate ASC'

	-- print the sql out for debugging.
	PRINT @Sql


	-- execute the sql
	--EXEC(@SQL)
END

In your code you would call the SP but passing in all values from each DDL even if the values are not selected. These should be passed into the SQL Command using SQL Parameters. This reduces the risk of SQL injection. Secondly you can maintain dynamic queries even after your application has been deployed.

Now I am not going to go into the why's and where's of dynamic sql. There are uses for it. There are also things to avoid. You could easily do this in code using a string builder and or the string.format using parametrised text. I dont see how you could write 27 if elses. 3 maybe or 4 at the most but not 27.

You dont even have to write a proc if this causes concern. just an idea.