|
|
 |
Joined: 5/31/2010
Posts: 71
|
|
|
I have a question about deleting products. Items that I have deleted still show up in the manage products list if I search by the category or name. How can I permanently delete an item so it is completely gone?
Thanks.
|
|
|
|
 | |
|
|
 |
Joined: 7/28/2009
Posts: 1452
|
|
|
Products are never actually deleted from the products table there is a bit field called "LogicallyDeleted" that is set to 1 when you delete a product then the manage products screen filters for records where logicallydeleted != 1. Can you check the stored procedure titled "Smith_productByFilter" in your database and confirm you have the filters in the where clause for P.LogicallyDeleted !=''1'' as highlighted in yellow below
ALTER procedure [dbo].[Smith_productByFilter]
@Page int,
@RecsPerPage int,
@sortfield varchar(50),
@sortorder varchar(10),
@filter varchar(50),
@searchtext varchar(50) ,
@tabModuleId int,
@PortalId int
as
CREATE TABLE #TempItems
(
ProductAutoID int IDENTITY,
ProductID int
)
DECLARE @SearchSQL varchar(5000)
if(@Filter='All')
Begin
SELECT @SearchSQL ='INSERT INTO #TempItems (ProductID) select P.ProductID from Smith_Products P left outer join ' +
'Smith_Category C on C.CategoryID=P.CategoryID'+
' where ( p.TabModuleId = ' + CAST ( @tabModuleId AS VARCHAR(10)) + ' OR p.Share = 1 ) and P.LogicallyDeleted !=''1'''
if( @sortfield='CategoryName')
SELECT @SearchSQL=@SearchSQL+' '+' order by C.' +@sortfield+' '+ @sortorder
else
SELECT @SearchSQL=@SearchSQL+' '+' order by P.'+@sortfield+' '+ @sortorder
End
else if(@Filter='CategoryName')
Begin
SELECT @SearchSQL ='INSERT INTO #TempItems (ProductID) select P.ProductID from Smith_Products P left outer join ' +
' Smith_Category C on C.CategoryID=P.CategoryID'+
' where ( p.TabModuleId = ' + CAST ( @tabModuleId AS VARCHAR(10)) + ' OR p.Share = 1 ) and P.LogicallyDeleted !=''1'' and ( C.'+@filter+' Like ''%' + @searchtext + '%'')'
if( @sortfield='CategoryName')
SELECT @SearchSQL=@SearchSQL+' '+' order by C.' +@sortfield+' '+ @sortorder
else
SELECT @SearchSQL=@SearchSQL+' '+' order by P.'+@sortfield+' '+ @sortorder
End
else
Begin
SELECT @SearchSQL ='INSERT INTO #TempItems (ProductID) select p.ProductID from Smith_Products P left outer join ' +
' Smith_Category C on C.CategoryID=P.CategoryID'+
' where ( p.TabModuleId = ' + CAST ( @tabModuleId AS VARCHAR(10)) + ' OR p.Share = 1 ) and P.LogicallyDeleted !=''1'' and ( P.'+@filter+' Like ''%' + @searchtext + '%'')'
if( @sortfield='CategoryName')
SELECT @SearchSQL=@SearchSQL+' '+' order by C.' +@sortfield+' '+ @sortorder
else
SELECT @SearchSQL=@SearchSQL+' '+' order by P.'+@sortfield+' '+ @sortorder
End
EXECUTE(@SearchSQL)
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- SELECT P.*,TotalRecords=(
--SELECT COUNT(*)
--FROM #TempItems TI),
-- MoreRecords =
SELECT p.*,c.categoryname, TotalRecords=(
SELECT COUNT(*)
FROM #TempItems TI),
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ProductAutoID >= @LastRec
)
FROM Smith_Products P left outer join
Smith_Category C on C.CategoryID=P.CategoryID RIGHT OUTER JOIN
#TempItems T ON P.ProductID = T.ProductID
WHERE ProductAutoID > @FirstRec AND ProductAutoID < @LastRec and p.PortalID = @PortalId
Order By P.SortOrder , P.ModelName
At your service,
Dave Smith
|
|
|
|
| |
|
|
|
Joined: 5/31/2010
Posts: 71
|
|
|
I only had the first one (P.LogicallyDeleted !=''1'') and so I added the other two and now it is fine.
So if they are never actually deleted, does that mean the table will become bloated over time? The store I am using your cart for will have thousands of different products and often changing (many one of a kind). In a year they could have had alot of items gone through that would no longer be in use. Is there a way to periodically clear out deleted items permanently from the database tables?
|
|
|
|
| |
|
|
|
Joined: 7/28/2009
Posts: 1452
|
|
|
How many products are you expecting in your table? SQL Server can handle millions of rows in a table and im sure you wont hit its capacity where it will slow down. All the sql that display products in the cart is optimized only to retrieve a page of records at a time. The products table has a primary key and is indexed. However, if you want to delete the logically deleted records you can issue the following sql stmt from query analyzer:
delete * from smith_products where logicallydeleted = 1
At your service,
Dave Smith
|
|
|
|
| |
|
|
|
Joined: 5/31/2010
Posts: 71
|
|
|
OK. If it won't affect performance then I won't bother with it. Thank you for explaining it to me.
|
|
|
|
| |
|
|
 |
|
Joined: 6/6/2010
Posts: 37
|
|
|
Encountered the same issue. Modified the stored procedure, but deleted products still show up in the store. ?
|
|
|
|
| |
|
|
|
Joined: 7/28/2009
Posts: 1452
|
|
|
Can you try resetting the sql server service its possible that the old sproc is cached.
At your service,
Dave Smith
|
|
|
|
| |
|
|
|
Joined: 5/31/2010
Posts: 71
|
|
|
Just FYI - after I upgraded to 2.97 I had to re-add the modifications to the sproc earlier in this post. So apparently the 2.97 update removed them. But after adding it back it works properly again.
|
|
|
|
| |
|
|
|
Joined: 7/28/2009
Posts: 1452
|
|
|
Thanks for the heads up I just added it to the sqldataprovider file
At your service,
Dave Smith
|
|
|
|
| |
|
|
|
|
Joined: 6/6/2010
Posts: 37
|
|
|
Clearing the stored proc cache didn't fix it.
|
|
|
|
| |
|
|
|
|
Joined: 6/6/2010
Posts: 37
|
|
|
I wanted to share additional information to hopefully resolve this problem. I added a second module to sell other products in a different location on my site. The new site only showed the non-deleted items. The original store configuration still displays all products including deleted.
Any thing else to try?
|
|
|
|
| |
|
|
|
Joined: 7/28/2009
Posts: 1452
|
|
|
Both instances of the buynow module use the same code base and stored procs so it doesnt make sense that first instance of the buynow module would show deleted products and the second one would not. For the products you deleted in the buynow module #1 can you check the smith_products table and confirm that the logicallydeleted flag is set to 1
At your service,
Dave Smith
|
|
|
|
| |
|
|
|
|
Joined: 6/6/2010
Posts: 37
|
|
|
Yes, they are set to 1 in the smith_products table.
I really need to take this cart live. Can we resolve this issue a different way? For example, deleting the entries manually from sql?
|
|
|
|
| |
|
|
|
Joined: 7/28/2009
Posts: 1452
|
|
|
I checked this issue out on my dev system and on the test portal www9.smith-consulting.com here are the results:
1. I deleted product PS1220 12V from store 1 and it no longer displays in the front end product listing or in the backend manage product screen. Working correctly
I am not able to replicate this issue on my dev system or on the test portal www9.smith-consulting.com. Let me know if you still see the issue on the test portal and if so the steps to replicate.
At your service,
Dave Smith
|
|
|
|
| |
|
|
|
|
Joined: 6/6/2010
Posts: 37
|
|
|
Completely removing the cart modules and reinstalling 2.99 appears to have fixed it (email also sent).
|
|
|
|