This topic is locked
[SOLVED]

 Call a procedure with multiple parameters - Help

1/12/2021 3:16:49 PM
PHPRunner General questions
A
abhijit2020 author

Hello,
I am trying to execute a procedure with two parameters on "Add page" event. I have tried with the following code on "Custom Add" event but unsuccessful. Any help would be greatly appreciated.
CustomQuery("EXEC buildPayPeriod '" .$values["CurrentYear"]. "' , '" .$values["PayPeriod"]. "' ");

return false;
I can run the procedure successfully on the SQL Management Studio(e.g. exec buildPayPeriod 2021, 'Bi-Weekly'<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=28048&image=1&table=forumtopics' class='bbc_emoticon' alt=';)' />. The procedure populate a table based on the parameters. I am getting the following error message:
Call stack

File: line Function Arguments

0. connections\Connection.php:626 trigger_error 1. Source: Microsoft OLE DB Provider for SQL Server

Description: Incorrect syntax near '2020'.;

2. 256;

1. connections\Connection.php:626 Connection->triggerError 1. Source: Microsoft OLE DB Provider for SQL Server

Description: Incorrect syntax near '2020'.;

2. connections\MSSQLWinConnection.php:135 MSSQLWinConnection->query 1. EXEC buildPayPeriod('2020' '') ;

3. include\dal.php:24 CustomQuery 1. EXEC buildPayPeriod('2020' '') ;

4. include\payperiodbuild_vw_events.php:44 eventclass_payperiodbuild_vw->CustomAdd 1. Array ([CurrentYear] => 2020[PayPeriod] => ) ;

2. Array ( ) ;

3. ;

4. ;

5. AddPage;

5. classes\addpage.php:593 AddPage->callCustomAddEvent N/A

6. classes\addpage.php:391 AddPage->processDataInput N/A

7. classes\addpage.php:318 AddPage->process N/A

8. payperiodbuild_vw_add.php:86 Global scope N/A



Thank you,
Abhi

admin 1/12/2021

I see two issues here.

  1. PayPeriod field is empty. I don't know if you should still call your stored procedure in this case or need to cancel it.
  2. The SQL query that is being executed doesn't match your code.

EXEC buildPayPeriod('2020' '')


First, the comma is missing between the parameters, and second - there are parenthesis around the parameters that I do not see in your code either.

A
abhijit2020 author 1/13/2021



I see two issues here.

  1. PayPeriod field is empty. I don't know if you should still call your stored procedure in this case or need to cancel it.
  2. The SQL query that is being executed doesn't match your code.

EXEC buildPayPeriod('2020' '')


First, the comma is missing between the parameters, and second - there are parenthesis around the parameters that I do not see in your code either.


Good Morning and thank you for giving me time on my issue.
I freshly started with the following and getting the same issue (not reading the 2nd parameter).
I created a view with (SELECT 1 as CurrentYear, 2 as PayPeriod) to input my parameters. The field type for CurrentYear is number and for PayPeriod is Text. I added the following code in "Custom Add" event in the "Add page"
CustomQuery("EXEC buildPayPeriod '" .$values["CurrentYear"]. " , " .$values["PayPeriod"]. "' ");

return false;
I tried with CurrentYear=2021 and PayPeriod=Bi-Weekly in Add page and got the following error:
[size="+2"]php error happened[/size]

[size="+1"]Technical information[/size]Error type256Error description**Source: Microsoft OLE DB Provider for SQL Server

Description: Error converting data type varchar to int.URLlocalhost/payperiodbuild_vw_add.php?page=add&submit=1&Error fileC:\app\PHPRunners\Projects\CentaurusTSv104\output\connections\Connection.phpError line626SQL queryEXEC buildPayPeriod '2021 , ' More info
[size="+1"]
Call stack[/size] File: lineFunctionArguments#0. connections\Connection.php:626trigger_error1. Source: Microsoft OLE DB Provider for SQL Server

Description: Error converting data type varchar to int.;

2. 256;#1. connections\Connection.php:626Connection->triggerError1.
Source: Microsoft OLE DB Provider for SQL Server

Description:** Error converting data type varchar to int.;#2. connections\MSSQLWinConnection.php:135MSSQLWinConnection->query1. EXEC buildPayPeriod '2021 , ' ;#3. include\dal.php:24CustomQuery1. EXEC buildPayPeriod '2021 , ' ;#4. include\payperiodbuild_vw_events.php:44eventclass_payperiodbuild_vw->CustomAdd1. Array ( [CurrentYear] => 2021 [PayPeriod] => ) ;

2. Array ( ) ;

3. ;

4. ;

5. AddPage;#5. classes\addpage.php:593AddPage->callCustomAddEventN/A#6. classes\addpage.php:391AddPage->processDataInputN/A#7. classes\addpage.php:318AddPage->processN/A#8. payperiodbuild_vw_add.php:86Global scopeN/A
What I need to change in my code to read the 2nd parameter.
Thank you,
Abhi

A
abhijit2020 author 1/13/2021



Good Morning and thank you for giving me time on my issue.
I freshly started with the following and getting the same issue (not reading the 2nd parameter).
I created a view with (SELECT 1 as CurrentYear, 2 as PayPeriod) to input my parameters. The field type for CurrentYear is number and for PayPeriod is Text. I added the following code in "Custom Add" event in the "Add page"
CustomQuery("EXEC buildPayPeriod '" .$values["CurrentYear"]. " , " .$values["PayPeriod"]. "' ");

return false;
I tried with CurrentYear=2021 and PayPeriod=Bi-Weekly in Add page and got the following error:
[size="+2"]php error happened[/size]

[size="+1"]Technical information[/size]Error type256Error description**Source: Microsoft OLE DB Provider for SQL Server

Description: Error converting data type varchar to int.URLlocalhost/payperiodbuild_vw_add.php?page=add&submit=1&Error fileC:\app\PHPRunners\Projects\CentaurusTSv104\output\connections\Connection.phpError line626SQL queryEXEC buildPayPeriod '2021 , ' More info
[size="+1"]
Call stack[/size] File: lineFunctionArguments#0. connections\Connection.php:626trigger_error1. Source: Microsoft OLE DB Provider for SQL Server

Description: Error converting data type varchar to int.;

2. 256;#1. connections\Connection.php:626Connection->triggerError1.
Source: Microsoft OLE DB Provider for SQL Server

Description:** Error converting data type varchar to int.;#2. connections\MSSQLWinConnection.php:135MSSQLWinConnection->query1. EXEC buildPayPeriod '2021 , ' ;#3. include\dal.php:24CustomQuery1. EXEC buildPayPeriod '2021 , ' ;#4. include\payperiodbuild_vw_events.php:44eventclass_payperiodbuild_vw->CustomAdd1. Array ( [CurrentYear] => 2021 [PayPeriod] => ) ;

2. Array ( ) ;

3. ;

4. ;

5. AddPage;#5. classes\addpage.php:593AddPage->callCustomAddEventN/A#6. classes\addpage.php:391AddPage->processDataInputN/A#7. classes\addpage.php:318AddPage->processN/A#8. payperiodbuild_vw_add.php:86Global scopeN/A
What I need to change in my code to read the 2nd parameter.
Thank you,
Abhi


Wondering why it is erroring out with data conversion (varchar to int). Is it trying to convert the data type of the second parameter? if so why it is doing? Procedure run successfully from SQL Management Studio. The data types of the 1st and 2nd parameters are integer and varchar in the SQL procedure respectively.

A
abhijit2020 author 1/15/2021



Wondering why it is erroring out with data conversion (varchar to int). Is it trying to convert the data type of the second parameter? if so why it is doing? Procedure run successfully from SQL Management Studio. The data types of the 1st and 2nd parameters are integer and varchar in the SQL procedure respectively.


I was able to run the procedure by recreating the view on a table. Not sure why the "Custom Add" event code was passing null value for the 2nd parameter.
Fix:

  1. Create a table,
    create table pram_log(pramValue1 int,

    pramValue2 varchar(25));
  2. Create the view,
    SELECT pramValue1 as CurrentYear, pramValue2 as PayCycle FROM pram_log
  3. Add the following code in Custom Add
    CustomQuery("EXEC buildPayPeriod '".$values["CurrentYear"]."', '".$values["PayCycle"]."' ");
    return false;
    I hope above will help someone.
    Abhi