Custom Search

[Rdurrance]

Hi all,

I am trying to create a way to do a simple search

Here is my Query:
"SELECT [Rods].* FROM [Rods] WHERE (([Rods].[CrankType] = @CrankType) AND ([Rods]." & _
"[Action] = @Action) AND ([Rods].[Material] = @Material) AND ([Rods].[Application" & _
"] = @Application) AND ([Rods].[Power] = @Power))"


All of the variables are generated from DDlists. ex:
@Power = MyVariable & MyVariable=MyDDL.selectedvalue.

Since this is a search, not all of the DDLs have searchable data in them all of the time. The database that I am querying is full of numbers and if the DDL has not been changed the selected value is 0. None of the fields in database that I am querying have zeros in them.

Finally the question: How can I make a query using only the changed DDLs without creating 30 if:then:else statements to Account for all of the scenarios?

I am pretty new to SQL. Asp.Net Web Matrix created the code for me but I understand about the @ Variables

Sorry if the post is wordy, I just want to give all of the information

Thank you

Rob

 

[cjard]

To me, DDL is short for Data Definition Language, i.e. something a CREATE TABLE... sql statement or ALTER TRIGGER blah.. statement. As such, your post doesnt seem to make much sense. Can you rephrase it?

 

[Rdurrance]

Sure,


What I mean by DDL is Drop Down List Does that Help?

Thank you,

Rob

 

[cjard]

Hi all,

I am trying to create a way to do a simple search

Here is my Query:
"SELECT [Rods].* FROM [Rods] WHERE (([Rods].[CrankType] = @CrankType) AND ([Rods]." & _
"[Action] = @Action) AND ([Rods].[Material] = @Material) AND ([Rods].[Application" & _
"] = @Application) AND ([Rods].[Power] = @Power))"


All of the variables are generated from DDlists. ex:
@Power = MyVariable & MyVariable=MyDDL.selectedvalue.

OK, this doesnt seem to make sense

Assuming the MyDDL.selectedvalue is 0, and MyVariable is "abc" your @Power variable contains the string:
abcFalse


Huh?

Since this is a search, not all of the DDLs have searchable data in them all of the time. The database that I am querying is full of numbers and if the DDL has not been changed the selected value is 0. None of the fields in database that I am querying have zeros in them.

Finally the question: How can I make a query using only the changed DDLs without creating 30 if:then:else statements to Account for all of the scenarios?

You ahve to be smart with the SQL:

SELECT *
FROM Table
WHERE
(powerField = @Power OR @Power = 0) AND
(crankTypeField = @CrankType OR @CrankType = 0) AND
...


This is basic logics. You want ALL the predicates to be true so that a row is returned
Either the user fills ina value, or it is at the default of 0.. Hence in these cases you want a predicate to be true if the row matches or if 0 is entered.

Hopefully you can see, with this logic:
(powerField = @Power OR @Power = 0)

The following truths are seen:
row power = 100bhp, @Power = 100bhp, result = true
row power = 200bhp, @Power = 100bhp, result = false
row power = 100bhp, @Power = 0, result = true
row power = 200bhp, @Power = 0, result = true


So the only row that is dropped is the 200bhp row.. and even then only if power is specified.

Hopefully this will help you write your query