• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1688
  • Last Modified:

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
0
JaimeJegonia
Asked:
JaimeJegonia
  • 3
  • 2
1 Solution
 
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
 
JaimeJegoniaAuthor Commented:
aneeshattingal,

i tried your suggestion but i still get the same error.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Aneesh RetnakaranDatabase 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
 
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now