CODEWITHSUNDEEP
phpsqlsql-serverpdoodbc
vaskaloidis
vaskaloidis

Reputation: 51

PHP MS SQL Invalid parameter number: number of bound variables does not match number of tokens

I am attempting to run a Stored Procedure from my MS SQL database. The stored procedure is from the database of a POS (Point of sales system) for an auto parts store - thus the procedure is correctly written and has been proved to work already with the POS system.

I keep getting errors when attempting to run this stored procedure - SQLSTATE[HY093] - Invalid Parameter number: number out of bound variables does not match number of tokens.

Here is my PHP code

$db = new PDO($dsn, $user, $password);

$stmt = $db->prepare("CALL insertworkorder(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ");

$WorkOrderID = 4;
$StoreNumber = 4;
$YardNumber = 4;
$WorkOrderNumber = 3;
$EstimateNumber = '4';
$AccountNumber = 4;
$CustomerNumber = 4;
$CreatedBy = 5;
$DeliveryDate = 4332;
$BillToBusinessName = 'example';
$BillToContactName = 'example';
$BillToAttentionLine = 'example';
$BillToAddress1 = 'example';
$BillToAddress2 = 'example';
$BillToCity = 'example';
$BillToCounty = 'example';
$BillToStateOrProvince = 'example';
$BillToPostalCode = 'example';
$BillToCountry = 'USA';
$BillToContactFAX = 5466;
$BillToContactPhone = example;
$BillToContactOtherPhone = v; 
$BillToContactEMail = '[email protected]';
$ShipToBusinessName = 'example';
$ShipToContactName = 'example';
$ShipToAttentionLine = 'example';
$ShipToAddress1 = 'example';
$ShipToAddress2 = 'example';
$ShipToCity = 'example';
$ShipToCounty = 'USA';
$ShipToStateOrProvince = 'CT';
$ShipToPostalCode = 'example';
$ShipToCountry = 'USA';
$ShipToContactFax = 'example';
$ShipToContactPhone = 'example';
$ShipToContactOtherPhone = 'example';
$ShipToContactEMail = '';
$ShipVIA = '22';
$CustomerPO = '22';
$WorkOrderStatus = '22';
$WorkOrderNotes = '2';
$Amount = '2';
$TotalDiscountAmount = '2';
$TotalFreightAmount = '2';
$ApplyCustomerTaxRate = '22';
$TotalFreightTaxAmount = '22';
$FreightIsTaxable = '2';
$GSTTaxExempt = '22';
$TotalPartsAmount = '22';
$TotalServicesAmount = '22';
$TotalCoreAmount = '';
$TotalLineItemTaxAmount = '22';
$OrderSource = 'website';
$TrackingNumber = '1226412258223';
$ReasonForVoid = '2';
$EComRequestID = 22;
$CurrentUserID = 22;
$PaypalTransactionID = '3';
$TaxExemptID = '3';
$LegalTraceID = '3';
$BillToContactPhoneExt = '3';
$BillToContactOtherPhoneExt = '3';
$ShipToContactPhoneExt = '3';
$ShipToContactOtherPhoneExt = '3';


$stmt->bindValue(':@StoreNumber', $StoreNumber, PDO::PARAM_INT); //REQUIRED
$stmt->bindValue(':@YardNumber', $YardNumber, PDO::PARAM_INT); //REQUIRED
$stmt->bindValue(':@EstimateNumber', $EstimateNumber, PDO::PARAM_STR);
$stmt->bindValue(':@AccountNumber', $AccountNumber, PDO::PARAM_INT); //REQUIRED
$stmt->bindValue(':@CustomerNumber', $CustomerNumber, PDO::PARAM_INT); //REQUIRED
$stmt->bindValue(':@CreatedBy', $CreatedBy, PDO::PARAM_INT); //REQUIRED
$stmt->bindValue(':@DeliveryDate', $DeliveryDate, PDO::PARAM_INT);
$stmt->bindValue(':@BillToBusinessName ', $BillToBusinessName, PDO::PARAM_STR);
$stmt->bindValue(':@BillToContactName', $BillToContactName, PDO::PARAM_INT);
$stmt->bindValue(':@BillToAttentionLine ', $BillToAttentionLine , PDO::PARAM_STR);
$stmt->bindValue(':@BillToAddress1', $BillToAddress1, PDO::PARAM_STR);
$stmt->bindValue(':@BillToAddress2 ', $BillToAddress2 , PDO::PARAM_STR);
$stmt->bindValue(':@BillToCity', $BillToCity, PDO::PARAM_STR);
$stmt->bindValue(':@BillToCounty', $BillToCounty, PDO::PARAM_STR);
$stmt->bindValue(':@BillToStateOrProvince', $BillToStateOrProvince, PDO::PARAM_STR);
$stmt->bindValue(':@BillToPostalCode', $BillToPostalCode, PDO::PARAM_STR);
$stmt->bindValue(':@BillToCountry', $BillToCountry, PDO::PARAM_STR);
$stmt->bindValue(':@BillToContactFAX', $BillToContactFAX, PDO::PARAM_STR);
$stmt->bindValue(':@BillToContactPhone', $BillToContactPhone, PDO::PARAM_STR);
$stmt->bindValue(':B@illToContactOtherPhone', $BillToContactOtherPhone, PDO::PARAM_STR);
$stmt->bindValue(':@BillToContactEMail', $BillToContactEMail, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToBusinessName', $ShipToBusinessName, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToContactName', $ShipToContactName, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToAttentionLine', $ShipToAttentionLine, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToAddress1', $ShipToAddress1, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToAddress2', $ShipToAddress2, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToCity', $ShipToCity, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToCounty', $ShipToCounty, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToStateOrProvince', $ShipToStateOrProvince, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToPostalCode', $ShipToPostalCode, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToCountry', $ShipToCountry, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToContactFax', $ShipToContactFax, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToContactPhone', $ShipToContactPhone, PDO::PARAM_STR);
$stmt->bindValue(':@ToContactOtherPhone', $ShipToContactOtherPhone, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToContactEMail', $ShipToContactEMail, PDO::PARAM_STR);
$stmt->bindValue(':@ShipVIA', $ShipVIA, PDO::PARAM_STR);
$stmt->bindValue(':@CustomerPO', $CustomerPO, PDO::PARAM_STR);
$stmt->bindValue(':@WorkOrderStatus', $WorkOrderStatus, PDO::PARAM_STR);
$stmt->bindValue(':@WorkOrderNotes', $WorkOrderNotes, PDO::PARAM_STR);
$stmt->bindValue(':@Amount', $Amount, PDO::PARAM_STR); //REQUIRED
$stmt->bindValue(':@TotalDiscountAmount', $TotalDiscountAmount, PDO::PARAM_STR); //REQUIRED
$stmt->bindValue(':@TotalFreightAmount', $TotalFreightAmount, PDO::PARAM_STR);
$stmt->bindValue(':@ApplyCustomerTaxRate', $ApplyCustomerTaxRate, PDO::PARAM_STR); //REQUIRED
$stmt->bindValue(':@TotalFreightTaxAmount', $TotalFreightTaxAmount, PDO::PARAM_STR); //REQUIRED
$stmt->bindValue(':@FreightIsTaxable', $FreightIsTaxable, PDO::PARAM_BOOL); //REQUIRED
$stmt->bindValue(':@GSTTaxExempt', $GSTTaxExempt, PDO::PARAM_BOOL); //REQUIRED
$stmt->bindValue(':@TotalPartsAmount', $TotalPartsAmount, PDO::PARAM_STR);
$stmt->bindValue(':@TotalServicesAmount', $TotalServicesAmount, PDO::PARAM_STR); //REQUIRED
$stmt->bindValue(':@TotalCoreAmount', $TotalCoreAmount, PDO::PARAM_STR);
$stmt->bindValue(':@TotalLineItemTaxAmount', $TotalLineItemTaxAmount, PDO::PARAM_STR);
$stmt->bindValue(':@OrderSource', $OrderSource, PDO::PARAM_STR);
$stmt->bindValue(':@TrackingNumber', $TrackingNumber, PDO::PARAM_STR);
$stmt->bindValue(':@ReasonForVoid', $ReasonForVoid, PDO::PARAM_STR);
$stmt->bindValue(':@EComRequestID', $EComRequestID, PDO::PARAM_INT);
$stmt->bindValue(':@CurrentUserID', $CurrentUserID, PDO::PARAM_INT);
$stmt->bindValue(':@PaypalTransactionID', $PaypalTransactionID, PDO::PARAM_STR);
$stmt->bindValue(':@TaxExemptID', $TaxExemptID, PDO::PARAM_STR);
$stmt->bindValue(':@LegalTraceID', $LegalTraceID, PDO::PARAM_STR);
$stmt->bindValue(':@BillToContactPhoneExt', $BillToContactPhoneExt, PDO::PARAM_STR);
$stmt->bindValue(':@BillToContactOtherPhoneExt', $BillToContactOtherPhoneExt, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToContactPhoneExt', $ShipToContactPhoneExt, PDO::PARAM_STR);
$stmt->bindValue(':@ShipToContactOtherPhoneExt', $ShipToContactOtherPhoneExt, PDO::PARAM_STR);
$stmt->bindParam(':@WorkOrderID', $WorkOrderID, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT);
$stmt->bindParam(':@WorkOrderNumber', $WorkOrderNumber, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT);

$result = $stmt->execute();

print_r($stmt->errorInfo());

And Here is the sql procedure:

ALTER procedure [dbo].[insertworkorder]
    (
    @StoreNumber                int,
    @YardNumber                 int,
    @EstimateNumber             varchar(20),
    @AccountNumber              accountnumber,
    @CustomerNumber             customernumber,
    @CreatedBy                  int,
    @DeliveryDate               datetime,
    @BillToBusinessName         contactlong,
    @BillToContactName          contactlong,
    @BillToAttentionLine        contactlong,
    @BillToAddress1             AddressLong,
    @BillToAddress2             AddressLong,
    @BillToCity                 CityLong,
    @BillToCounty               varchar(25),
    @BillToStateOrProvince      stateorprovince,
    @BillToPostalCode           postalcode,
    @BillToCountry              country,
    @BillToContactFAX           phonenumber,
    @BillToContactPhone         phonenumber,
    @BillToContactOtherPhone    phonenumber,
    @BillToContactEMail         email,
    @ShipToBusinessName         contactlong,
    @ShipToContactName          contactlong,
    @ShipToAttentionLine        contactlong,
    @ShipToAddress1             AddressLong,
    @ShipToAddress2             AddressLong,
    @ShipToCity                 CityLong,
    @ShipToCounty               varchar(25),
    @ShipToStateOrProvince      stateorprovince,
    @ShipToPostalCode           postalcode,
    @ShipToCountry              country,
    @ShipToContactFax           phonenumber,
    @ShipToContactPhone         phonenumber,
    @ShipToContactOtherPhone    phonenumber,
    @ShipToContactEMail         email,
    @ShipVIA                    varchar(20),
    @CustomerPO                 varchar(20),
    @WorkOrderStatus            status,
    @WorkOrderNotes             notes,
    @Amount                     money,
    @TotalDiscountAmount        money,
    @TotalFreightAmount         money,
    @ApplyCustomerTaxRate       boolean,
    @TotalFreightTaxAmount      money,
    @FreightIsTaxable           boolean,
    @GSTTaxExempt               boolean,
    @TotalPartsAmount           money,
    @TotalServicesAmount        money,
    @TotalCoreAmount            money,
    @TotalLineItemTaxAmount     money,
    @OrderSource                char(1),
    @TrackingNumber             varchar(50),
    @ReasonForVoid              varchar(255),
    @EComRequestID              int,
    @CurrentUserID              int,
    @PaypalTransactionID        varchar(30),
    @TaxExemptID                varchar(24),
    @LegalTraceID               varchar(24),
    @BillToContactPhoneExt      PhoneExt,
    @BillToContactOtherPhoneExt PhoneExt,
    @ShipToContactPhoneExt      PhoneExt,
    @ShipToContactOtherPhoneExt PhoneExt,
    @WorkOrderID                int output,
    @WorkOrderNumber            int output
    )
as

    set nocount on

    exec dbo.getnextcounter 'WORKORDER', 'WorkOrderID', @WorkOrderID output
    exec dbo.getnexttransactioncounter @YardNumber, @StoreNumber, 'WORKORDER', 'WorkOrderNumber', @WorkOrderNumber output



    insert dbo.WORKORDER
        (
        WorkOrderID,
        StoreNumber,
        Revision,
        YardNumber,
        WorkOrderNumber,
        EstimateNumber,
        AccountNumber,
        CustomerNumber,
        CreatedBy,
        DeliveryDate,
        DateCreated,
        BillToBusinessName,
        BillToContactName,
        BillToAttentionLine,
        BillToAddress1,
        BillToAddress2,
        BillToCity,
        BillToCounty,
        BillToStateOrProvince,
        BillToPostalCode,
        BillToCountry,
        BillToContactFAX,
        BillToContactPhone,
        BillToContactOtherPhone,
        BillToContactEMail,
        ShipToBusinessName,
        ShipToContactName,
        ShipToAttentionLine,
        ShipToAddress1,
        ShipToAddress2,
        ShipToCity,
        ShipToCounty,
        ShipToStateOrProvince,
        ShipToPostalCode,
        ShipToCountry,
        ShipToContactFax,
        ShipToContactPhone,
        ShipToContactOtherPhone,
        ShipToContactEMail,
        ShipVIA,
        CustomerPO,
        WorkOrderStatus,
        WorkOrderNotes,
        Amount,
        TotalDiscountAmount,
        TotalFreightAmount,
        ApplyCustomerTaxRate,
        TotalFreightTaxAmount,
        FreightIsTaxable,
        GSTTaxExempt,
        TotalPartsAmount,
        TotalServicesAmount,
        TotalCoreAmount,
        TotalLineItemTaxAmount,
        OrderSource,
        TrackingNumber,
        ReasonForVoid,
        EComRequestID,
        IsLastRevision,
        CurrentUserID,
        PaypalTransactionID,
        TaxExemptID,
        LegalTraceID,
        BillToContactPhoneExt,
        BillToContactOtherPhoneExt,
        ShipToContactPhoneExt,
        ShipToContactOtherPhoneExt
        )

    values
        (
        @WorkOrderID,
        @StoreNumber,
        0,                      /* Revision         */
        @YardNumber,
        @WorkOrderNumber,
        @EstimateNumber,
        @AccountNumber,
        @CustomerNumber,
        @CreatedBy,
        @DeliveryDate,
        getdate(),              /* @DateCreated     */
        @BillToBusinessName,
        @BillToContactName,
        @BillToAttentionLine,
        @BillToAddress1,
        @BillToAddress2,
        @BillToCity,
        @BillToCounty,
        @BillToStateOrProvince,
        @BillToPostalCode,
        @BillToCountry,
        @BillToContactFAX,
        @BillToContactPhone,
        @BillToContactOtherPhone,
        @BillToContactEMail,
        @ShipToBusinessName,
        @ShipToContactName,
        @ShipToAttentionLine,
        @ShipToAddress1,
        @ShipToAddress2,
        @ShipToCity,
        @ShipToCounty,
        @ShipToStateOrProvince,
        @ShipToPostalCode,
        @ShipToCountry,
        @ShipToContactFax,
        @ShipToContactPhone,
        @ShipToContactOtherPhone,
        @ShipToContactEMail,
        @ShipVIA,
        @CustomerPO,
        @WorkOrderStatus,
        @WorkOrderNotes,
        @Amount,
        @TotalDiscountAmount,
        @TotalFreightAmount,
        @ApplyCustomerTaxRate,
        @TotalFreightTaxAmount,
        @FreightIsTaxable,
        @GSTTaxExempt,
        @TotalPartsAmount,
        @TotalServicesAmount,
        @TotalCoreAmount,
        @TotalLineItemTaxAmount,
        @OrderSource,
        @TrackingNumber,
        @ReasonForVoid,
        @EComRequestID,
        1,
        @CurrentUserID,                       /* IsLastRevision   */
        @PaypalTransactionID,
        @TaxExemptID,
        @LegalTraceID,
        @BillToContactPhoneExt,
        @BillToContactOtherPhoneExt,
        @ShipToContactPhoneExt,
        @ShipToContactOtherPhoneExt
        )

    set nocount off

    return (0)

Thank you for any of your help!!

Vas

Upvotes: 0

Views: 792

Answers (2)

vaskaloidis
vaskaloidis

Reputation: 51

Hey I know this is an old post - but just an update on where I ended with this.

After I spent hours (and another employee spent hours trying), we decided that Microsoft Sql Stored Procedures was not going to work with the PHP using the PDO Connector.

We eventually created an export feature in PHP that dumped everything to an XML file and then we developed a Java program that ran every minute in CRON that scanned our folder for XML files and executed the Ms SQL Stored Procedures using the JDBC driver and passing in the content read from the XML files, then deleting the XML files...

Not sure if they have fixed this, but with my experience PHP does not work with Microsoft SQL Stored Procedures

Cheers

Upvotes: 0

chrislondon
chrislondon

Reputation: 12031

You have two major problems. 1) Your query uses question mark place holders and your ->bindValue functions use named parameters. 2) You only have 20 question marks and you are trying to bind around 60 values (I stopped counting around 40 or so).

If you want to use named parameters you need to do your code like this:

$stmt = $db->prepare("CALL insertworkorder(:StoreNumber, :YardNumber, ... etc ... ");

$stmt->bindValue(':StoreNumber', $StoreNumber, PDO::PARAM_INT); //REQUIRED
$stmt->bindValue(':YardNumber', $YardNumber, PDO::PARAM_INT);
// ... etc ...

If you want to use question mark place holders you do:

$stmt = $db->prepare("CALL insertworkorder(?, ?, ?, ... etc ...");

$stmt->bindValue(1, $StoreNumber, PDO::PARAM_INT);
$stmt->bindValue(2, $YardNumber, PDO::PARAM_INT);
// ... etc ...

You have to make sure you have the same number of bindValues as you do question marks (or named parameters) otherwise you will get the Invalid Parameter number: number out of bound variables does not match number of tokens. error

Upvotes: 1

Related Questions