I am not sure were to start on how to fix this. I am not having any problems connecting to the mssql 2000 server. My problem is Could not find stored procedure 'CMRC_ShoppingCartAddItem'. The user has exec permissions on that procedure. This is a custom VB.net 2005 web application.. Does any one have any ideas on how to check whats wrong?
Public Sub AddItem(ByVal cartIDAs String,ByVal productIDAs String,ByVal CompanyAs String,ByVal quantityAs Integer)
' Create Instance of Connection and Command ObjectDim myConnectionAs SqlConnection =New SqlConnection("Data Source=MANDB02;Initial Catalog=db_name;UId=nobigaccess;Password=$$$$$$$")
Dim myCommandAs SqlCommand =New SqlCommand("CMRC_ShoppingCartAddItem", myConnection)
' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure' Add Parameters to SPROCDim parameterProductIDAs SqlParameter =New SqlParameter("@.ProductID", SqlDbType.NVarChar, 15)
parameterProductID.Value = productID
myCommand.Parameters.Add(parameterProductID)
Dim parameterCompanyAs SqlParameter =New SqlParameter("@.Company", SqlDbType.NVarChar, 8)
parameterCompany.Value = Company
myCommand.Parameters.Add(parameterCompany)
Dim parameterCartIDAs SqlParameter =New SqlParameter("@.CartID", SqlDbType.NVarChar, 50)
parameterCartID.Value = cartID
myCommand.Parameters.Add(parameterCartID)
Dim parameterQuantityAs SqlParameter =New SqlParameter("@.Quantity", SqlDbType.Int, 4)
parameterQuantity.Value = quantity
myCommand.Parameters.Add(parameterQuantity)
' Open the connection and execute the Command myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close()End Sub
This is what the procedure looks like in sql:
CREATE Procedure CMRC_ShoppingCartAddItem
(
@.CartIDnvarchar(50),
@.ProductIDnvarchar(23),
@.Companynvarchar(8),
@.Quantityint)AsDECLARE @.CountItemsint
SELECT @.CountItems =Count(ProductID)
FROM CMRC_ShoppingCartWHERE ProductID = @.ProductIDAND Company = @.Company
AND CartID = @.CartIDIF @.CountItems > 0/* There are items - update the current quantity */UPDATE CMRC_ShoppingCartSET Quantity = (@.Quantity + CMRC_ShoppingCart.Quantity)WHERE ProductID = @.ProductIDAND Company = @.Company
AND CartID = @.CartIDELSE/* New entry for this Cart. Add a new record */INSERT INTO CMRC_ShoppingCart
(
CartID,
Quantity,
ProductID,
Company
)
VALUES ( @.CartID, @.Quantity, @.ProductID, @.Company )GO
Try creating the proc with dbo as the owner.
CREATE Procedure dbo.CMRC_ShoppingCartAddItem
|||Changing it to dbo allowed the program to see it, just not access it. I changed the owner to the user id I am logging on with and that worked. Is that a bad idea? Should I try something else?|||
If you change the owner to the userid you login with, it may not be visible to other users. IDeally you create objects (tables/procs/views/functions etc) with dbo.<name>. You can then give the user access to the proc by giving EXEC permissions. you can also grant access as:
GRANT EXEC ON dbo.<yourproc> TO <yourUserLoginId>
