SQL problem with parameter

[jing]

Hi,

I have a sql query

Code: Select all

SELECT si.stockcode, si.stockgroup, sg.groupname, si.latestcost, si.description, si.bincode, si.sellprice1 as sellprice, si.x_prev_sellprice, si.x_sellprice_changed, sl.qty as quantity, si.STOCKCODE as barcode FROM stock_items si LEFT OUTER JOIN stock_groups sg ON si.stockgroup = sg.groupno LEFT OUTER JOIN stock_loc_info sl ON si.stockcode = sl.stockcode WHERE sl.location = 6 AND si.stockcode IN ( @stockCode) ORDER BY si.description

where @stockCode is a parameter based on a variable "StockCode" of type string.

I set the default value of "StockCode" to be @,UTP.5 as they are 2 valid stock codes - this doesn't work in the preview. If I set the default value of "StockCode" to @ (just 1 stock code), the report looks fine in the preview.

So, I guess I would like to know how to pass in parameter values when the parameter represents a string of different stock codes ?

thanks

[Edward]

Please define parameters for the DataSource according the following flash tutorial.
http://www.stimulsoft.com/livedemos/Rep ... eters.html

Also you can see the example Master-Detail in the group SQL from the Demo.exe Application which is provided with the standard delivery.

Thank you.

[jing]

Hi, Thanks for your reply.

I think we have misunderstand each other.
I have no problem with creating parameters while on the report designer. I have created a parameter called @StockCode dynamically through code and I am planning to change the value of @StockCode dynamically as well, so the user doesn't need to worry about this.

I think the problem is to do with the SQL query

Code: Select all

SELECT si.stockcode, si.stockgroup, sg.groupname, si.latestcost, si.description, si.bincode, si.sellprice1 as sellprice, si.x_prev_sellprice, si.x_sellprice_changed, sl.qty as quantity, si.STOCKCODE as barcode FROM stock_items si LEFT OUTER JOIN stock_groups sg ON si.stockgroup = sg.groupno LEFT OUTER JOIN stock_loc_info sl ON si.stockcode = sl.stockcode WHERE sl.location = 6 AND si.stockcode IN ( @StockCode) ORDER BY si.description

The value of @StockCode can not contain more than 1 stock code !!

If @StockCode = "@" (where @ is a valid stock code), the report works fine

However,

If @StockCode = "@, UTP.5" (where UTP.5 is also a valid stock code), the report doesn't work - there is no output ??

Can you help me with this please?

thanks

[jing]

Hi,

I have found a way around this.

Instead of @StockCode, I put {StockCode} - this reference to a variable called StockCode.

so if the variable StockCode has '@', 'UTP.5' as its values, then the query executes correctly.

I wonder why it doesn't work with parameter?

[Edward]

In that case also may be used the unnamed parameters. To use it please change the name of the parameter to the ? symbol in the SQL text.

Code: Select all

SELECT si.stockcode, si.stockgroup, sg.groupname, si.latestcost, si.description, si.bincode, si.sellprice1 as sellprice, si.x_prev_sellprice, si.x_sellprice_changed, sl.qty as quantity, si.STOCKCODE as barcode FROM stock_items si LEFT OUTER JOIN stock_groups sg ON si.stockgroup = sg.groupno LEFT OUTER JOIN stock_loc_info sl ON si.stockcode = sl.stockcode WHERE sl.location = 6 AND si.stockcode IN (?) ORDER BY si.description

For more detail about unnamed parameters please see the following flash tutorial
http://www.stimulsoft.com/livedemos/Rep ... ers_2.html

Thank you.