1. 

   SteveD, Member

   I have a few reports that have been playing up, and as I have traced through them it appears to be an issue with the thousands separator being passed in function parameters. An example is shown below:

   select 'dbr.report', 'sp_DBR_stocklocpop', '[edit]', 'iItemID=GRNItemID', 'vGRN=GRNNumber', 'iItemNum=GRNItemNo', 'vStatus=GRNItemStatus', 'vDepot=Depot', 'nDWeight=DepotWeight', 'iWare=WarehouseID', 'iLocID=GRNItemLocationLocationID'; select WarehouseID, GRNItemLocationLocationID, GRNItemID, GRN, Depot, GRNNumber, GRNItemNo, Description, StockFamily, SupplierName, GRNItemStatus, AvailableWeight, DepotWeight, GRNItemLocationLocation, GRNItemLocationBin, GRNItemLocationWeight, ActiveOnLTS, 'EDIT LOCATION' as '[edit]' from #temp where AvailableWeight > 0

   When I hover the mouse cursor over the hyper link I can see that p1 is passing the thousand parameter of GRNItemID rather than just the value.
   /mydbr/report.php?r=46&p1=182%2C521&p2=UK-R%2FPGRN%2F13%2F0023&p3=2
   Obviously this causes problems with other reports, specifically in one incidence where a pop up uses the GRNItemID along with the built in isLogin param
   CREATE PROCEDURE sp_DBR_puLocations @mydbr_param2 int, @inLogin varchar(30)
   Unfortunately, when this procedure is called to populate the popup box the following error is returned
   DB error SQLSTATE: 42000, code: 8144 message: message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Procedure or function sp_DBR_puLocations has too many arguments specified.

   SQL: sp_DBR_puLocations 182,521,'dba'
   Because the thousand separator is being passed as part of an int variable the popup things it is being passed three paramaters (182 followed by 521 followed by 'dba') when in actuality it is only being passed 2 (182521 followed by 'dba').

   How can I stop myDBR from passing on the thousand separator as part of an integer value?
2. 

   myDBR Team, Key Master

   Steve,
   myDBR should be able to handle the thousand separator just fine. myDBR accepts any format that is valid according to user's settings.

   The problem seems valid though and it looks like there is a problem how connected parameter is handling it's parameters. We'll take a look at it asap.

   As a temporary fix, you can remove the default formatting from the field / use another nonformatted field.

   --
   myDBR Team
3. 

   myDBR Team, Key Master

   The error happened when a linked report used a formatted parameter with big enough value as source for a connected parameter.

   This has now been fixed. You can run the updater to get the latest build.

   Thanks for reporting the issue.

   --
   myDBR Team
4. 

   SteveD, Member

   Well that sort of worked. The good news is I no longer receive the 'too many arguments specified' error.

   The bad news is now instead of a popup box containing a list of values I instead get a box displayed containing a "No parameters available" error instead, just like in the initial thread found here http://mydbr.com/forums/topic.php?id=1191

   We know from the original error message that parameters were being passed, so if the new build simply corrects the too many arguments specified problem, what is the cause of this new error?
5. 

   SteveD, Member

   I have done further testing on this. The popup in question calls the procedure sp_DBR_puLocations to populate it's drop down list.

   Initially sp_DBR_puLocations was created as
   CREATE PROCEDURE sp_DBR_puLocations @mydbr_param2 int, @inLogin varchar(30)
   This however resulted in the No parameters available problem. In an attempt to get to the bottom of the problem I started removing some of the parameters from the procedure and hard coding in the values instead. However, even if the sp_DBR_puLocations procedure takes no parameteres like so:
   CREATE PROCEDURE sp_DBR_puLocations AS BEGIN
   The report still displays the No available parameters error, even though the procedure that should populate the popup box doesn't use parameters.
6. 

   myDBR Team, Key Master

   Does your linked parameter query return values with the selected parameter?

   For us to see what is going on, could you send the SQL output of the original report, the definition for the linked report and the connected parameter query and the values you expect it to return.

   --
   myDBR Team
7. 

   SteveD, Member

   Sorry, cockup on my part - @mydbr_param2 should have been mydbr_param1, I dropped one of the parameters from the report and didn't realise it.

   My bad - Working as expected now.

   Sorry
8. 

   myDBR Team, Key Master

   OK,
   did you know that you do not need to use the mydbr_paramX format in connected parameters, but can also refer to the parameters by the name. Makes life easier. See documentation for more info and examples.

   --
   myDBR Team

Reply

You must log in to post.