troubleshooting Question

PHP and using a stored procedure to insert money value

Avatar of dogsareit
dogsareitFlag for United States of America asked on
PHPMicrosoft SQL Server
11 Comments1 Solution70 ViewsLast Modified:
I am using PHP 5.6.25, developing localhost -Windows 10.
I am passing a money value to my stored procedure. The money value was extracted from an email. It contains a decimal point - no currency symbol etc. -
i.e. 1234.67
 I have trimmed the value and when I view it via I.E view source, it doesn't appear that I am picking up any trash, and that doesn't mean it isn't just because I can "see" it displayed. But my stored procedure sure doesn't like the value I am passing.
I have used this very same stored procedure, under like circumstances i.e. pulling data, including a money amount  that only contains a decimal point, and it works every time.
The table column is defined as money.
If I set my variable to a value i.e. $mymoney = '123.66' - it will insert the record every time. I read about money_format but that is invalid in Windows.
What must I do to ensure that I have stripped the parsed value to just numbers a  decimal point ?? What am I failing to understand ?? or Do ??
Thank you.



I have included my stored procedure.
USE [SomeDB]
GO
/****** Object:  StoredProcedure [dbo].[ins_new_importIDocInfo]    Script Date: 12/6/2019 7:46:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[ins_new_importIDocInfo] 
   @StoreId int,
   @Supplier nvarchar(50),
   @IDoc nvarchar(50),
   @AmtRcvd money,
   @DateRcvd datetime,
   @RECID INT OUTPUT as
   Insert Into importIDocInfo(StoreId,Supplier,IDoc,AmtRcvd,DateRcvd)
               Values(@StoreId,@Supplier,@IDoc,@AmtRcvd,@DateRcvd)       
           
SELECT @RECID = SCOPE_IDENTITY()
RETURN @RECID


And my actual coding for the stored procedure"
               $myparams['RECID'] = intval(-2);
                $myparams['StoreId'] = $srtStoreId;
                $myparams['Supplier'] = $supplier;
                $myparams['IDoc'] = $wrkIDOCNbr;
	       $myparams['AmtRcvd'] = $strChkTotalMoney;
                $myparams['DateRcvd'] = $wrkFixedEmailSentDate;
          
                $procedure_params = array(
                    array(&$myparams['RECID'], SQLSRV_PARAM_OUT),
                    array(&$myparams['StoreId'], SQLSRV_PARAM_IN),
                    array(&$myparams['Supplier'], SQLSRV_PARAM_IN),
                    array(&$myparams['IDoc'], SQLSRV_PARAM_IN),
		    array(&$myparams['AmtRcvd'], SQLSRV_PARAM_IN,null,SQLSRV_SQLTYPE_MONEY),
		    array(&$myparams['DateRcvd'], SQLSRV_PARAM_IN,null,SQLSRV_SQLTYPE_DATETIME));

               	$myconnect = sqlsrv_connect($sname,$wrkinfo);
                $sqltxt = "EXEC ins_new_importIDocInfo @RECID= ?, @StoreId= ?, @Supplier= ?, @IDoc= ?, @AmtRcvd= ?, @DateRcvd= ? ";

				 $mystmt = sqlsrv_prepare($myconnect,$sqltxt, $procedure_params); 
                if ($mystmt == false)
                  {
                        trigger_error(sqlsrv_errors(), E_USER_ERROR);
						print_r($myparams); 
		                (print_r(sqlsrv_errors(), true));
		                sqlsrv_free_stmt($mystmt);
	                    sqlsrv_close($myconnect);
		                echo 'ERROR ** UNABLE TO CREATE IMPORTIDOCINFO RECORD';
                   }else{
                        if (sqlsrv_execute($mystmt))
                          {
                                while ($res = sqlsrv_next_result($mystmt))
								{
									
								}
							//  	print_r($myparams); 
							//	 print_r(sqlsrv_errors(), true);
                               $primaryid = $myparams[@RECID];
						  }	  
				  }                  
             sqlsrv_free_stmt($mystmt);
			 sqlsrv_close($myconnect);
			// echo 'Primary Key Returned   ' .  $primaryid . '<BR><br>';
	return $primaryid;
ASKER CERTIFIED SOLUTION
dogsareit
Owner

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros