Question
Problems with Stored procedures
Hi all,
Not sure if anyone else is having the same problems as me, but whenever I try to use a command that returns a recordset from a stored procedure I run into problems. I can create the command successfully, but whenever I drag a binding onto the page Dreamweaver crashes.
The stored procedure that I am using accepts 4 input parameters. This is the SQL in the SP:
SELECT dbo.tblManufacturer.web_manufacturer, dbo.tblProducts.itm_item, dbo.tblProducts.itm_desc, dbo.tblProducts.itm_wdesc, dbo.tblProducts.itm_wnotes,
dbo.tblProducts.itm_price, dbo.tblProducts.itm_wpicture, dbo.tblProducts.itm_unitweight, dbo.tblProducts.itm_unitvolume, dbo.tblProducts.Stock,
dbo.tblCategory.CategoryName, dbo.tblSubCat1.SubCat1Name, dbo.tblSubCat2.SubCat2Name
FROM dbo.tblProducts LEFT OUTER JOIN
dbo.tblSubCat2 ON dbo.tblProducts.SubCat2ID = dbo.tblSubCat2.SubCat2ID LEFT OUTER JOIN
dbo.tblSubCat1 ON dbo.tblProducts.SubCat1ID = dbo.tblSubCat1.SubCat1ID LEFT OUTER JOIN
dbo.tblCategory ON dbo.tblProducts.CategoryID = dbo.tblCategory.CatID LEFT OUTER JOIN
dbo.tblManufacturer ON dbo.tblProducts.itm_manufacturer = dbo.tblManufacturer.web_manid
WHERE (@SubCat2ID IS NULL OR dbo.tblProducts.SubCat2ID = @SubCat2ID) AND
(@SubCat1ID IS NULL OR dbo.tblProducts.SubCat1ID = @SubCat1ID) AND
(@CatID IS NULL OR dbo.tblProducts.CategoryID = @CatID) AND
(@ManID IS NULL OR dbo.tblManufacturer.web_manid = @ManID)
ORDER BY dbo.tblCategory.CategoryName, dbo.tblSubCat1.SubCat1Name, dbo.tblSubCat2.SubCat2Name, dbo.tblProducts.itm_item
I have tried to use this directly in Dreamweaver, replacing the @ variables with proper ones in the SQL panel, but I get and this error: Invalid column name 'MSubCat2ID'.
Since I am using WebAssists eCart extension I need the Recordset to work well within Dreamweaver. Does anyone know what I should do to get this to work in Dreamweaver? I would love to get the command to work as I prefer using Stored Procedures, but if I have to build the SQL within Dreamweaver then I will do that.
Cheers,
Steve
Not sure if anyone else is having the same problems as me, but whenever I try to use a command that returns a recordset from a stored procedure I run into problems. I can create the command successfully, but whenever I drag a binding onto the page Dreamweaver crashes.
The stored procedure that I am using accepts 4 input parameters. This is the SQL in the SP:
SELECT dbo.tblManufacturer.web_manufacturer, dbo.tblProducts.itm_item, dbo.tblProducts.itm_desc, dbo.tblProducts.itm_wdesc, dbo.tblProducts.itm_wnotes,
dbo.tblProducts.itm_price, dbo.tblProducts.itm_wpicture, dbo.tblProducts.itm_unitweight, dbo.tblProducts.itm_unitvolume, dbo.tblProducts.Stock,
dbo.tblCategory.CategoryName, dbo.tblSubCat1.SubCat1Name, dbo.tblSubCat2.SubCat2Name
FROM dbo.tblProducts LEFT OUTER JOIN
dbo.tblSubCat2 ON dbo.tblProducts.SubCat2ID = dbo.tblSubCat2.SubCat2ID LEFT OUTER JOIN
dbo.tblSubCat1 ON dbo.tblProducts.SubCat1ID = dbo.tblSubCat1.SubCat1ID LEFT OUTER JOIN
dbo.tblCategory ON dbo.tblProducts.CategoryID = dbo.tblCategory.CatID LEFT OUTER JOIN
dbo.tblManufacturer ON dbo.tblProducts.itm_manufacturer = dbo.tblManufacturer.web_manid
WHERE (@SubCat2ID IS NULL OR dbo.tblProducts.SubCat2ID = @SubCat2ID) AND
(@SubCat1ID IS NULL OR dbo.tblProducts.SubCat1ID = @SubCat1ID) AND
(@CatID IS NULL OR dbo.tblProducts.CategoryID = @CatID) AND
(@ManID IS NULL OR dbo.tblManufacturer.web_manid = @ManID)
ORDER BY dbo.tblCategory.CategoryName, dbo.tblSubCat1.SubCat1Name, dbo.tblSubCat2.SubCat2Name, dbo.tblProducts.itm_item
I have tried to use this directly in Dreamweaver, replacing the @ variables with proper ones in the SQL panel, but I get and this error: Invalid column name 'MSubCat2ID'.
Since I am using WebAssists eCart extension I need the Recordset to work well within Dreamweaver. Does anyone know what I should do to get this to work in Dreamweaver? I would love to get the command to work as I prefer using Stored Procedures, but if I have to build the SQL within Dreamweaver then I will do that.
Cheers,
Steve
AdChoices