Using variable in SQL statement

Stimulsoft Reports.WPF discussion
umueglitz
Posts: 3
Joined: Wed Jul 24, 2019 8:39 pm

Using variable in SQL statement

Post by umueglitz »

Hallo,
I try the test version before i purchase a license. Target is to realize one report to check out if all works as we need it. All looks very good but I have a problem to define the Where condition with an variable in the SQL statement. I didn't found an corresponding example in your sample files and in the online documentation I also didn't find and explaination. Potentially I'm wrong but I think there should be such a possibility. I want to define the value in the C# main program and transfer this value to the SQL statement. I used a syntax for the variable {variabl name}, but the SQL assistant reports an syntax error. Can you show me an small example how to handle such an problem.

Thanks in advance for your support.
Lech Kulikowski
Posts: 6199
Joined: Tue Mar 20, 2018 5:34 am

Re: Using variable in SQL statement

Post by Lech Kulikowski »

Hello,

You can use Parameters for the data source, in the query. Also, you can use variables directly in the query:
select * from Table where column = {Variable}

Thank you.
David334
Posts: 29
Joined: Tue Jul 16, 2019 6:45 pm

Re: Using variable in SQL statement

Post by David334 »

Hello, I am trying to do what this is suggesting but I cannot get it to work.
I wish to store in a variable a value from a data source.
I want to use this variable to select data from another data source.

{Variable} = Data Source 1 info.

Select * From DataSource2
Where Column = {Variable}.

When I try to retrieve the columns, I am prompted to input a value for "Variable" which seems to suggest I am actually not able to dynamically issue the WHERE clause.
So then I removed the WHERE statement, retrieved the columns from the table, and then added the WHERE statement back and saving the data source.
The problem I have then is I get an error trying to convert something to int.

Clearly I am doing something wrong. Could someone provide more detail how to query a data source with the value of a variable yet unknown?
Lech Kulikowski
Posts: 6199
Joined: Tue Mar 20, 2018 5:34 am

Re: Using variable in SQL statement

Post by Lech Kulikowski »

Hello,

You should set the ReadOnly property for the variable which store value from the data source.

Thank you.
David334
Posts: 29
Joined: Tue Jul 16, 2019 6:45 pm

Re: Using variable in SQL statement

Post by David334 »

I have tried to set for ReadOnly, but I still get an error.
I do not know how the software queries the database. Does it only get queried when a column is used in the report?
I am thinking maybe the data source is getting queried trying to use a variable that has not be set with a value yet?
Lech Kulikowski
Posts: 6199
Joined: Tue Mar 20, 2018 5:34 am

Re: Using variable in SQL statement

Post by Lech Kulikowski »

Hello,

Please send us a sample report with test data which reproduces the issue for analysis.

Thank you.
David334
Posts: 29
Joined: Tue Jul 16, 2019 6:45 pm

Re: Using variable in SQL statement

Post by David334 »

I dont think I am able to send a sample. I am using some embedded stimulsoft web based version and doesn't allow me save out the file.
Here is a screen shot of my Variable.
Here is my DataSource I wish to use to Pull only the data associated with what that variable holds. Its not complete because it always gives me an error. How should it be configured?
Attachments
DataSource.JPG
DataSource.JPG (55.19 KiB) Viewed 5104 times
Variable.JPG
Variable.JPG (34.73 KiB) Viewed 5105 times
Lech Kulikowski
Posts: 6199
Joined: Tue Mar 20, 2018 5:34 am

Re: Using variable in SQL statement

Post by Lech Kulikowski »

Hello,

Please try to use the following query without parameters:
select * from ORDERS where OrderNo = {OrderNo}

Thank you.
David334
Posts: 29
Joined: Tue Jul 16, 2019 6:45 pm

Re: Using variable in SQL statement

Post by David334 »

I get
"Incorrect Syntax Near '='. "
Lech Kulikowski
Posts: 6199
Joined: Tue Mar 20, 2018 5:34 am

Re: Using variable in SQL statement

Post by Lech Kulikowski »

Hello,

Sorry, in your case, try to use:
select * from ORDERS where OrderNo = '{OrderNo}'

Thank you.
Post Reply