Creating reports and dashboards | Stimulsoft community forum

Can I use a Variable in a query, or must I pass the value of the variable to a paramater and use that? Are there any naming convention issues I need to know about? For example parameters must start with an @ symbol, etc.

I believe you have to transmit the Variable to a Parameter. This is because each and every datasource provider could provide slightly different conventions for their Parameters.

Think of it this way:
Variables are something created and provided by Stimulsoft for use inside the report engine.
Parameters are something created and provided by the datasource provider/database driver that is being used to connect to your data.

Because of this distinct difference the Database Driver the the query gets sent to will not understand the variables that the report engine is using to provide extra functions. The DB Drive only understands the use of parameters in the SQL Query that you provide in the datasource.

The naming convention for parameters inside these SQL Queries is again specific to the DB Provider/Driver in use. For example, SQL Server uses the "@" symbol. So your parameters must be named beginning with "@" symbols. Like and int with the name "@ID" could be used in the following query "Select * From MyTable Where ID = @ID". However if we use OLEDB the parameters are not named but instead use the ? and are used in order that they are declared. So this statement becomes "SELECT * FROM MyTable WHERE ID = ?".

Hope this helps.

Hi

Yes, John has written an absolutely correct statement.

You can use variables as parameters and there is no limitation on naming of them, you could write as follows:
and value of the MyVariable could be:
This example is shows the situation when parameters are not acceptable.

Thank you.

I am a little confused because John says I have to pass the variable to a parameter, but Edward is saying that I can use a variable in a query by surrounding the name of the variable in curly brackets. Am I reading something wrong? I will try both, but was just wondering if I was misreading.

Can my select statement look like this: If I have two variables named BeginDate and EndDate?

Or would it need to be that I have two variables of BeginDate and EndDate; then create two parameters of @BeginDate and @EndDate with the expression of {BeginDate} and {EndDate} respectively so that my query now looks like this:

And in my code I am calling: Thank you for the help so far.


*EDIT UPDATE*
It appears that either way will work for any of you that are wondering. You can create parameters for your query and pass the variable in as the expression, or you can just pass the variable in to the query string. The query below works:
That saves you the time of having to create parameters.

Hello,

I am a little confused because John says I have to pass the variable to a parameter, but Edward is saying that I can use a variable in a query by surrounding the name of the variable in curly brackets. Am I reading something wrong? I will try both, but was just wondering if I was misreading.

Can my select statement look like this:

Code: Select all

select * from tableA where StartDate between {BeginDate} and {EndDate}

You can use above expression, but! you need take in considatation string format of BeginDate and EndDate. If string format of date variable (depend on localization of .Net and windows) does not match with string format of SQL Server, your query can't work. You can use variables in sql query when you want change some parts of this query. For example:

Code: Select all

select * from tableA where StartDate between @BeginDate and @EndDate

And in my code I am calling:

Code: Select all

MyReport.CompiledReport.Dictionary.Variables["BeginDate"].Value = txtDateFrom.Text;
MyReport.CompiledReport.Dictionary.Variables["EndDate"].Value = txtDateTo.Text;

This is correct variant. You need use following code to run report:

Code: Select all

report.Compile();
report["@BeginDate"].Value = txtDateFrom.Text;
report["@EndDate"].Value = txtDateTo.Text;
report.Render();

where @BeginDate and @ EndDate names of sql parameters (you don't need use variables in this case).

p.s. You can check SqlParameters sample project from standard delivery.

Thank you.

Thank you Jan, I forgot to add that I had changed my code to do this instead of using the variables.

This is AWESOME and definitely new information for me. I have a couple questions though.

Will a direct variable in a query be "injection safe" by scrubbing the variable for invalid characters? For example: Normally this would cause an error. Does the variable system check for this? This is one HUGE advantage to using SQL parameters at least in other C# projects I've worked on. SQL Parameters are never treated as part of the statement and thus their values can't be used in injection attacks. Without them you have to be very good about scrubbing variable values for Control Characters.

My other question regards nesting Variables. Can I nest Variables? For Example: The above code is by no means production code, it is more conceptual but it should illustrate my point.

I'm not sure of that John, when I get a chance I'll test it out.