Link to home
Start Free TrialLog in
Avatar of JaimeJegonia
JaimeJegoniaFlag for United States of America

asked on

Microsoft SQL-DMO (ODBC SQLState: 42S22)

I wanted to change to data type of ProductID to nvarchar in my stored procedure but I get
the following error. (I changed the data type of ProductID from int to nvarchar in my database.)

Microsoft SQL-DMO (ODBC SQLState: 42S22)

Error 207: Invalid column name 'ProductID'.
Invalid column name 'ProductID'.
Invalid column name 'ProductID'.
Invalid column name 'ProductID'.

---------- Stored Procedure ----------

CREATE Procedure NURI_ShoppingCartAddItem
(
    @CartID nvarchar(50),
    @ProductID int,
    @Quantity int
)
As

DECLARE @CountItems int

SELECT
    @CountItems = Count(ProductID)
FROM
    NURI_ShoppingCart
WHERE
    ProductID = @ProductID
  AND
    CartID = @CartID

IF @CountItems > 0  /* There are items - update the current quantity */

    UPDATE
        NURI_ShoppingCart
    SET
        Quantity = (@Quantity + NURI_ShoppingCart.Quantity)
    WHERE
        ProductID = @ProductID
      AND
        CartID = @CartID

ELSE  /* New entry for this Cart.  Add a new record */

    INSERT INTO NURI_ShoppingCart
    (
        CartID,
        Quantity,
        ProductID
    )
    VALUES
    (
        @CartID,
        @Quantity,
        @ProductID
    )


GO
Avatar of Aneesh
Aneesh
Flag of Canada image

JaimeJegonia,
> Invalid column name 'ProductID'.

Make sure that you didn't change the Column Name .    
CREATE Procedure NURI_ShoppingCartAddItem
(
    @CartID nvarchar(50),
    @ProductID nvarchar(50), ------------Chnage1
    @Quantity int
)
As


IF EXISTS(SELECT 1 FROM   NURI_ShoppingCart WHERE  ProductID = @ProductID AND    CartID = @CartID )-- change 2
/* There are items - update the current quantity */

    UPDATE NURI_ShoppingCart
    SET   Quantity = (@Quantity + NURI_ShoppingCart.Quantity)
    WHERE ProductID = @ProductID
    AND   CartID = @CartID

ELSE  /* New entry for this Cart.  Add a new record */

    INSERT INTO NURI_ShoppingCart
    (
        CartID,
        Quantity,a
        ProductID
    )
    VALUES
    (
        @CartID,
        @Quantity,
        @ProductID
    )


GO
Avatar of JaimeJegonia

ASKER

aneeshattingal,

i tried your suggestion but i still get the same error.
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ah ok. THANKS!! That solved my problem. I don't have ProductID in my NURI_ShoppingCart table.
Instead I have ProductSizeID (from the relationship table of my Products and Size tables.) I must have overlooked it because I change my database structure. THANKS AGAIN!! :)
aneeshattingal, thanks a lot!! it really worked for me .I never imagined i will be able to fix it. After half day of doing all sort of things ,i was finally able to resolve it with your second suggestion...