Combobox combinatorics

Question

I have a form with 3 combo boxes. The value of the combo boxes generates a SQL query with 3 parameters. The SQL generates a RDLC report.

For example:

Combox1 with values(v1,v2,v3,ALL)

Combox2 with values(v1,v2,ALL)

Combox3 with values(v1,v2,v3,4,ALL)

Me.ExampleTableAdapter.QueryWithParams(Me.ExampleDataSet.Table, Combobox1.SelectedIndex, Combobox2.SelectedIndex,Combobox1.SelectedIndex)

QueryWithParams:

SELECT * FROM Table WHERE Column1=@c1 AND Column2=@c2 AND Column3=@c3

Ok, the problem is when the selected value is ALL in the combo X, I cannot get the values because I must change the query and kick out the parameter. For example: If the combo1, combo2, combo3 have the values ALL. I don't need parameters.

SELECT * FROM Table

If the combo1 is ALL.

SELECT * FROM Table WHERE Column2=@c2 AND Column3=@c3

If combo1 and combo3 are ALL

SELECT* FROM Table WHERE Column2=@c2

... Then in code I look which is the selected value of the combo and upon it I do the query.

Is there a way that i do this thing without generating all 8 types of queries?

PS. I cannot use stored procedures because I'm using SQL Server Compact

Johan · Accepted Answer · 2011-03-02 09:14:47Z

1

If the value for 'ALL' should be sent to the query with value 'NULL'

SELECT * 
FROM Table 
WHERE (Column1=@c1 OR @c1 IS NULL)
AND (Column2=@c2 OR @c2 IS NULL)
AND (Column3=@c3 OR @c3 IS NULL)

Edit: Of course same can be done for any specified 'ALL'-value Example of 'ALL' = -1

(Column1=@c1 OR @c1 = -1)

answered Mar 2, 2011 at 9:14

Johan

1,1927 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Schmelter · Accepted Answer · 2011-03-02 09:17:19Z

0

You should be a ble to use IsNull in your query for the columns where nothing/all was selected:

WHERE Column1 = ISNULL(@c1, Column1)

MSDN

answered Mar 2, 2011 at 9:17

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Collectives™ on Stack Overflow

Combobox combinatorics

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related