JaimeJegonia
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
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
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
(
@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
ASKER
aneeshattingal,
i tried your suggestion but i still get the same error.
i tried your suggestion but i still get the same error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!! :)
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...
> Invalid column name 'ProductID'.
Make sure that you didn't change the Column Name .