Showing posts with label operator. Show all posts
Showing posts with label operator. Show all posts

Thursday, March 22, 2012

Dynamic Operator Help

Hi,

I'm trying to build a form that will allow users to choose their own parameters for the Select statement in the SqlDataSource. These results would then be displayed back to a GridView control. The only problem I am having is figuring out how to allow them to choose the operator (=, <, >, <>, etc) from a dropdown list. Does anyone have any suggestions on how to do this with a SqlDataSource control? It is probably something simple and any help would be much appreciated.

Thanks

You would just build the statement dynamically and set the SqlDataSource.SelectCommand propertyhttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selectcommand.aspx

But, be advise that using dynamic SQL is frought with danger due to sql injection attacks. You will really need to scrub your input. Seehttp://en.wikipedia.org/wiki/SQL_Injection or just google "sql injection" for many, many articles on this topic

|||

Dbland thanks for the reply. I do understand that the select command will be built dynamically and this is what I have done. Here is the Data Source.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:UsersConnectionString %>"
ProviderName="<%$ ConnectionStrings:UsersConnectionString.ProviderName %>" SelectCommand="SELECT Username, Email, Active, BuildingNum, UserRole, UserID FROM UserInfo WHERE (BuildingNum = @.BuildingNum)">
<SelectParameters>
<asp:ControlParameter ControlID="BuildingDropDown" Name="BuildingNum" PropertyName="Text" />
</SelectParameters>

I would like to parameterize the operator to a dropdown control which would have the =, >, < values options. That way people can select any building less than, greater than or, equal to a building number.

So my question is how do I allow this part of the select statement, "=", to be determined and populated from a users form input. (BuildingNum = @.BuildingNum)">

Is this possible?

|||

In your code you will have to take the value of the drop down and build your statement, eg

string sql = .................

SqlDataSource.SelectCommand = sql;

I'm not exactly sure in what event you would do this but I'm sure there is one just before the SelectCommand gets executed

Is this what you were after?

|||

Can you give me or point me to an example of how to do this?

|||

Let's say you have a Submit button. When they click that you build up your sql statement and set the sqldatasource as follows:

SqlDataSource.SelectCommand = sql;

Or you can put it in one of the data datasource events, like the Selecting event which occurs just before the select is done (http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selecting.aspx). I'm not sure which event gives you the flexibility you want, you'll have to play read about them and play around with it.

|||

Thanks for all your help dbland. I took your advise and set the select command upon the button click event. It seems to work out well. Thanks again.

dynamic operator ?

Is there anyway we can have dynamic operator in filters '
instead of > i want to put parameters!operator.valueYes but you need to generate your sql dynamically... see my post her
http://www.sqltalk.org/ftopic34616.htm
So you'll need a datasource of > < = >= <= then get you
user to pick from the field
Then dynamically add that parameter into your sq
so you'd have 'WHERE quantity ' + @.operator + @.valueparamate
Then exec the sql

Sunday, March 11, 2012

Dynamic Filter Operator

Here is what I am trying to do in SSRS 2005.

Setting up filters based on parameters with an expression like this:

=Iif(Parameters!Company.Value = "", "", Fields!company.Value)

In the wizard we are creating, we are also letting the user choose the operator for each parameter that they choose. My question is how can I change the filter dynamically based on the user choosing a specific parameter and also choosing an operator to associate with that parameter?

Example 1: User 1 chooses the Company parameter to filter their report, and they choose the parameter to equal (=) a specific value. So the filter expression would be like the one previously mentioned and the operator would be an equal sign.

Example 2: User 2 chooses the Company parameter to filter their report, and they choose the parameter to be LIKE a specific value. So the filter expression would be like the one previously mentioned and the operator would be LIKE.

How can I do this?

Thanks in advance for your help.

You indicate you have a parameter named "Company"

first:
Create a parameter named "operator" and give it values "equals" and "like"

label value

equals equals
like like

Set the default value if you choose.

then:
Create a parameter named "filter" and leave it blank

Open the "table properties" and select "filter"

1st expression -
for the filter expression like:
=Iif(Parameters!Operator.Value = "equals", "", Fields!company.Value))
for the operator:
select the "like" operator
for the value:
=Iif(Parameters!Operator.Value = "equals", "", Switch(Parameters!Filter.Value = Parameters!Filter.Value, Parameters!Filter.Value & "*", Parameters!Filter.Value = nothing, "*"))

2nd expression -
for the filter expression equals:
=Iif(Parameters!Operator.Value = "like", "", Fields!company.Value))
for the operator:
select the "=" operator
for the value:
=Iif(Parameters!Operator.Value = "like", "", Parameters!Filter.Value)

This only covers one data field so when you select "like" you can enter the leter "A" in the filter parameter and all fields that start with "A" will be returned.
However if you select "equals" you need to know exactly what to type in other wise a dropdown would be great here.
If you leave the filter parameter blank and select "like" it will return all the data.
The options are endless. . .

|||

That is exactly what I was looking for!

Thanks very much

Dynamic Filter Operator

Here is what I am trying to do in SSRS 2005.

Setting up filters based on parameters with an expression like this:

=Iif(Parameters!Company.Value = "", "", Fields!company.Value)

In the wizard we are creating, we are also letting the user choose the operator for each parameter that they choose. My question is how can I change the filter dynamically based on the user choosing a specific parameter and also choosing an operator to associate with that parameter?

Example 1: User 1 chooses the Company parameter to filter their report, and they choose the parameter to equal (=) a specific value. So the filter expression would be like the one previously mentioned and the operator would be an equal sign.

Example 2: User 2 chooses the Company parameter to filter their report, and they choose the parameter to be LIKE a specific value. So the filter expression would be like the one previously mentioned and the operator would be LIKE.

How can I do this?

Thanks in advance for your help.

You indicate you have a parameter named "Company"

first:
Create a parameter named "operator" and give it values "equals" and "like"

label value

equals equals
like like

Set the default value if you choose.

then:
Create a parameter named "filter" and leave it blank

Open the "table properties" and select "filter"

1st expression -
for the filter expression like:
=Iif(Parameters!Operator.Value = "equals", "", Fields!company.Value))
for the operator:
select the "like" operator
for the value:
=Iif(Parameters!Operator.Value = "equals", "", Switch(Parameters!Filter.Value = Parameters!Filter.Value, Parameters!Filter.Value & "*", Parameters!Filter.Value = nothing, "*"))

2nd expression -
for the filter expression equals:
=Iif(Parameters!Operator.Value = "like", "", Fields!company.Value))
for the operator:
select the "=" operator
for the value:
=Iif(Parameters!Operator.Value = "like", "", Parameters!Filter.Value)

This only covers one data field so when you select "like" you can enter the leter "A" in the filter parameter and all fields that start with "A" will be returned.
However if you select "equals" you need to know exactly what to type in other wise a dropdown would be great here.
If you leave the filter parameter blank and select "like" it will return all the data.
The options are endless. . .

|||

That is exactly what I was looking for!

Thanks very much

Friday, March 9, 2012

dynamic evaluation of expression operator (was "Substitution")

Hi I am trying to do something like the following:

DECLARE @.Operator varchar(1)
DECLARE @.Rate float
DECLARE @.Quantity float
DECLARE @.Converted float

SET @.Quantity = 6
SET @.Operator = '/'
SET @.Rate = 2
SET @.Converted = 0

@.Converted = (@.Quantity substituteTheValueOfThis(@.Operator) @.Rate)

PRINT @.Converted

so that the output would be 3

The reason I need to do it like this is that @.Operator will change at runtime...

Any suggestions appreciated, I have looked at EXEC sp_execsql but somehow can't get the syntax right.First of all, the following

DECLARE @.Operator varchar(1)
DECLARE @.Rate float
DECLARE @.Quantity float
DECLARE @.Converted float

SET @.Quantity = 6
SET @.Operator = '/'
SET @.Rate = 2
SET @.Converted = 0

Could be shortened to

DECLARE
@.Operator varchar(1)
, @.Rate float
, @.Quantity float
, @.Converted float
, @.cmd varchar(1000)

select @.Quantity = 6, @.Operator = '/', @.Rate = 2, @.Converted = 0
set @.cmd = 'select convert(decimal(38, 2), ' + convert(varchar, @.Quantity) + convert(Varchar,@.Operator) + convert(varchar, @.Rate) + ')'
exec (@.Cmd)

Sorry about the poor formatting, but this is cut n' pasted directly from Query Analyzer.

Generally when you do calculations in SQL Server, your result will be with or without decimals depending on whether you supply decimals to the calculation at hand, hence
3/2=1
3.0/2=1.500000|||Thanks, this seems to work great when:

select @.Quantity = 6, @.Operator = '/', @.Rate = 2, @.Converted = 0

but returns 0 when

select @.Quantity = 6, @.Operator = '/', @.Rate = 100, @.Converted = 0

rather than

.06

Ah!! Is this what you mean at the end of your post by...

"Generally when you do calculations in SQL Server, your result will be with or without decimals depending on whether you supply decimals to the calculation at hand, hence
3/2=1
3.0/2=1.500000"

I am trying to do conversions between grams and kilograms to 3dp.. so values maybe something like...

34.876 grams and i need to convert that to kilograms so would

0.034876 which I would probably round to 0.035 kilograms

This works but also value maybe

6 grams to kilograms which at the moment returns 0

Thanks for tips on the code and for making the title to thread more appropriate, I still have a lot to learn about transact sql and using this forum... :)|||Sorry I forgot, can I also assign the result of the EXEC statement to a variable like this...

@.Result = EXEC (@.cmd)

I know this doesn't work but it illustrates what I am hoping to do!

Thanks again!!!|||Yes it IS possible to write dynamic SQL in a way that makes the execution of the string return a value (do a search on the words "dynamic SQL OUTPUT" on these boards and you'll find quite a few posts about it.
However, dynamic SQL is not optimized by SQL Server (and couldn't be since it's not decided what it looks like until runtime.) and therefore it can be quite slow. If I understand your post correctly, basically what you are going for is a "mini-calculator"-procedure. You wanna send a first parameter, a operator and another operator to a procedure, and then do the evaluation and return the results in a proc.

If that is the case, you could probably get away with
-- Start proc
create procedure test as
@.Param1 float,
@.Param2 float,
@.Operator char(1)

as

select
case @.Operator
when '-' then @.Param1 - @.Param2
when '+' then @.Param1 + @.Param2
when '*' then @.Param1 * @.Param2
when '/' then @.Param1 / @.Param2
else
-1
end
return
-- End proc
However I cannot see how this is needed, since the calculation should probably be done in the client. (Given that this procedure is in a serverside solution, which I assumed that it is ...)
No need to do the dynamic SQL there. Perhaps I've simplified your problem and not considered all other factors, but as far as I can see my solution could work nicely, from a SQL point of view, but probably not from a architectual point of view. However, I choose not to discuss those matters here.

Good luck. :cool:|||Thank you very very much! You've set me thinking about the placing of this routine and maybe as you say, it should really be in the middle tier.

Thanks again for your time and very helpful comments

Cheers :)