Stored Procedure Sending For StiReport

[IskandarAir]

Dear Stimulsoft Team,

Please provide reliable documentation \ link to work with stored procedure in database. As you know DataSource of StiReport can have two Types. StoredProcedure or Table. It is pretty clear how to work with Table Type. BUT there is few useful docs how to implement with StoredProcedure Type. Can we work in both types with "parameterized" Stored procedure or only StoredProcedure is allowed?

My few simple question. Please respond as soon as you can:

1 Please provide documentation \ link how to implement storedProcedure Type of Datasource (we have already watched video available from youtube - the case covered is too simple). Especially Docs for .Net Framework
2 How to properly write statement in Designer. in video the execute procedure name was covered. Both following cases returning " Could not find stored procedure '' Exception
execute sp_TL_Rep_PayPeriod @pFromDate, ... [other properties]
execute [dbo].[sp_TL_Rep_PayPeriod] @pFromDate, ... [other properties]
3. How to pass String, datetime and int values. There is several properties:
dataSource.Parameters["@pGetDataFrom"].Value
dataSource.Parameters["@pPeriodType"].ParameterValue
Which one to use ? Should we surround them quation or sending "as it is " is enough?

4. Please provide some explanation for this Exception - "Exceeding the allowed number of characters in a character constant - Превышение допустимого числа символов в символьной константе "? What does it means and how to resolve it?


Thank you beforehand,
Iskandar

[Alex K.]

Hello,

There are two types of SQL data sources - Query(Table) and Stored Procedure. For the "Query" type, you can use any SQL queries in the SQL command:
select * from Table
execute StoredProcName
call StoredProcName
etc.

for the "Stored Procedure" type you should specify only stored procedure name in the sql command property of the data source.

> 4. Please provide some explanation for this Exception - "Exceeding the allowed number of characters in a character constant - Превышение допустимого числа символов в символьной константе "? What does it means and how to resolve it?

Could you explain your issue in more details, how to reproduce that issue?

Thank you.

[IskandarAir]

Hi,

The generated error is the following : c:\\Users\\HP\\AppData\\Local\\Temp\\rb5glidx.0.cs(2826,65) : error CS1012: Превышение допустимого числа символов в символьной константеc:\\Users\\HP\\AppData\\Local\\Temp\\rb5glidx.0.cs(2827,63) : error CS1012: Превышение допустимого числа символов в символьной константеc:\\Users\\HP\\AppData\\Local\\Temp\\rb5glidx.0.cs(2839,84) : error CS1040: Перед директивами препроцессора могут находиться только символы пробелов"?

It is happening when i send parameters to report (code of adding parameters are attached as image).

What is occurring ? Please a bit of explanation why it is happening?

One more details to clarify the situation. Parameters are obtained from StoredProc itself, thus they are identical.AND When i try to get columns with default parameters' value, I get the following image attached as last image.


Please clarify or give a link to documentation so clear example can be seen?

As you know, parameter request for StoredProc is way more faster ? the client is forcing to increase the performance!!!


Regards,
Iskandar

[Alex K.]

Hello,

Please send us a simple sample - simple stored procedure with several parameters and report which reproduces the issue for analysis.

Unfortunately, there is no documentation about the using parameters for stored procedures.

Thank you.

[IskandarAir]

Alex,

It is not reproducable purely from Designer. Moreover, the logic is complex.

Please, if there is no documentation you can describe the steps from here. How to pass parameters from .net c#. Please considering the case of date, nvarchar and int.

Guys, Please. Any hint about the mentioned exceptions?
--------------------------------------------------------------------------------------------------------------------
"c:\\Users\\HP\\AppData\\Local\\Temp\\5kmlumdz.0.cs(2839,84) : error CS1040: Перед директивами препроцессора могут находиться только символы пробелов" ( error CS1040: Before the preprocessor directives, only space characters").
--------------------------------------------------------------------------------------------------------------------
As i believe this is error is custom within Stimulsoft Report

From Designer everything is pretty simple.
I am using dataSource Type as Stored Procedure and in sql Command there is only storedProcedure name.

Iskandar

[IskandarAir]

Hi,

One additional Question, Our stored procedure accepts Datetime as ('02/17/2016'). But when submitting the following error is returned.

- Failed to convert parameter value from a Int32 to a DateTime.

And submitting the same DateTime from Designer returns above error. So the qeustion is - is it possible to send datetime as '02/17/2017'? Any tricks?

Thanks,
Iskandar

[Edward]

Hi Iskandar,

It looks like the individual parameters in the Stored Procedure in the Designer do not have their types detected correctly for some reason when you retrieve the parameters. Please check how the types of the individual parameters could be set manually in the following video: https://www.youtube.com/watch?v=u6F3nbG5RLE
The video contains the common steps of working with the parameters.
If you still receive the error you mentioned, could you please send the report template to support[at]stimulsoft.com for analysis?

Thank you,
Edward

[IskandarAir]

Hi Edward,

Thank you. Video is helpful. Can you please clarify Error CS1040: Перед директивами препроцессора могут находиться только символы пробелов" ( error CS1040: Before the preprocessor directives, only space characters allowed").

I have specified only StoredProcedure name in sqlCommand and passing all parameters as described in youtube video. But i believe it is error or bug of the Stimulsoft engine ? Unforunately, i cant send report template Due to complexity of issue regarding to our business logic, c# and design.


Thanks,
Iskandar

[Edward]

Hi Iskandar,

The report template would require the SQL Connection to the database, in order to reproduce the problem, you are correct, not the easiest way to reproduce the problem even on our side only by using the report template without SQL connection.
Please try to comment out the assigning of the "@InSelect" parameter as per your screenshot above.
If that does not help, please try to comment out assigning of other parameters one by one, until the problem disappears.
If this is not a parameter-related issue and the exception is still there, then try to use a different stored proc in that query, with much simple structure. If the problem goes away at this point, then the complexity of the stored proc was the problem.

Another idea that the problem might not be related to that Data Source at all - just try to remove it temporarily to check if the problem goes away and if it remains there - then it would be a chance to find another problem component in the report.

Anyway, it is difficult to say something only by looking at the error message.

Thank you,
Edward

[IskandarAir]

Dear stimulsoft team,

We almost succeeded at sending parameters. No errors is being thrown. But no result is being returned from stored procedure. In order to debug, we Run Profiler, to see upcoming sql commands. There, we san that empty params are being sent to sql server. I have attached screenshot. We are doing everything from code purely, nothing depends on from designer file (in terms of data source). HERE is our code.

Code: Select all

 var dataSource = (StiSqlSource)report.DataSources[0];


                // Set new query
                dataSource.SqlCommand = "sp_TL_Rep_PayPeriod";// query;
                dataSource.Type = StiSqlSourceType.StoredProcedure;
                var rParams = new StiDataParametersCollection();

                var pFromDate = new StiDataParameter("@pFromDate", (int)SqlDbType.DateTime, 0);
                pFromDate.ParameterValue = parameters.Date.ToSqlValue();
                rParams.Add(pFromDate);
                var pToDate = new StiDataParameter("@pToDate", (int)SqlDbType.DateTime, 0);
                pToDate.ParameterValue = parameters.Date.ToSqlValue();
                rParams.Add(pToDate);
                var pFromGroup = new StiDataParameter("@pFromGroup", (int)SqlDbType.NVarChar,10);
                pFromGroup.ParameterValue = parameters.EmployeeGroupFrom;
                rParams.Add(pFromGroup);
                var pToGroup = new StiDataParameter("@pToGroup", (int)SqlDbType.NVarChar, 10);
                pToGroup.ParameterValue = parameters.EmployeeGroupTo;
                rParams.Add(pToGroup);
                var pFromAgree = new StiDataParameter("@pFromAgree", (int)SqlDbType.NVarChar,8);
                pFromAgree.ParameterValue = parameters.AgreeFrom;
                rParams.Add(pFromAgree);
                var pToAgree = new StiDataParameter("@pToAgree", (int)SqlDbType.NVarChar, 8);
                pToAgree.ParameterValue = parameters.AgreeTo;
                rParams.Add(pToAgree);
                var inOrder = new StiDataParameter("@inOrder", (int)SqlDbType.NVarChar, 200);
                inOrder.ParameterValue = parameters.InOrder;
                rParams.Add(inOrder);
                var pBreakOrder = new StiDataParameter("@BreakOrder", (int)SqlDbType.SmallInt, 0);
                pBreakOrder.ParameterValue = (parameters.PageBreak == 2).ToSqlValue();
                rParams.Add(pBreakOrder);
                var pGetDataFrom = new StiDataParameter("@pGetDataFrom", (int)SqlDbType.SmallInt, 0);
                pGetDataFrom.ParameterValue = parameters.GetDataFrom;
                rParams.Add(pGetDataFrom);
                var pPeriodType = new StiDataParameter("@pPeriodType", (int)SqlDbType.SmallInt, 0);
                pPeriodType.ParameterValue = parameters.PeriodType;
                rParams.Add(pPeriodType);
                var pActive = new StiDataParameter("@pActive", (int)SqlDbType.SmallInt, 0);
                pActive.ParameterValue = parameters.Active.ToSqlValue();
                rParams.Add(pActive);
                var pxByParm = new StiDataParameter("@xByParm", (int)SqlDbType.VarChar, 3);
                pxByParm.ParameterValue = parameters.ByParam;
                rParams.Add(pxByParm);
                var pxEmpDepList = new StiDataParameter("@xEmpDepList", (int)SqlDbType.VarChar, 50);
                pxEmpDepList.ParameterValue = parameters.EmployeeDepartmentList;
                rParams.Add(pxEmpDepList);
                var pinSelect = new StiDataParameter("@inSelect", (int)SqlDbType.VarChar, -1);
                pinSelect.ParameterValue = "";
                rParams.Add(pinSelect);
                dataSource.UpdateParameters();
                dataSource.Parameters = rParams;

Can you please clarify what is going from here and what can be done about it?


Regards,
Iskandar