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
JaimeJegoniaAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
Run the following Query and find out whether the column  'ProductID' exists or not, also check the datatype of that column ...


select Column_Name, Data_Type
from information_schema.columns
WHERE Table_name = 'NURI_ShoppingCart'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
JaimeJegonia,
> Invalid column name 'ProductID'.

Make sure that you didn't change the Column Name .    
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
JaimeJegoniaAuthor Commented:
aneeshattingal,

i tried your suggestion but i still get the same error.
0
 
JaimeJegoniaAuthor Commented:
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!! :)
0
 
TeknoBearSFCommented:
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...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.