error on bulk edit

If you wanna bitch use the PM...Please!!

garethjnic wrote:

I was not bitching, I was responding to your less than complimentary and unnecessary  pm.

I won't take part in a slagging match  so, thank you for your kind offer, but I regret I must respectfully decline.

lhenschel wrote:



Thank you for the soild feedback, I still can't get that approch to work on my DB.

I really just went thru the stored procedure until I found something that returned a null and then took a closer look at the tables.

try to update this Sp: (this should solve the problem)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[Nop_ProductVariantLoadAll]
(
  @CategoryID      int = 0,
  @ManufacturerID    int = 0,
  @Keywords      nvarchar(MAX),
  @ShowHidden      bit = 0,
  @PageIndex      int = 0,
  @PageSize      int = 2147483644,
  @TotalRecords    int = null OUTPUT
)
AS
BEGIN
  
  --init
  SET @Keywords = isnull(@Keywords, '')
  SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'

  --display order
  CREATE TABLE #DisplayOrder
  (
    ProductID int not null PRIMARY KEY,
    DisplayOrder int not null
  )  

  IF @CategoryID IS NOT NULL AND @CategoryID > 0
    BEGIN
      INSERT #DisplayOrder
      SELECT pcm.ProductID, pcm.DisplayOrder
      FROM [Nop_Product_Category_Mapping] pcm WHERE pcm.CategoryID = @CategoryID
    END
    ELSE IF @ManufacturerID IS NOT NULL AND @ManufacturerID > 0
    BEGIN
      INSERT #DisplayOrder
      SELECT pmm.ProductID, pmm.Displayorder
      FROM [Nop_Product_Manufacturer_Mapping] pmm WHERE pmm.ManufacturerID = @ManufacturerID
    END
  ELSE
    BEGIN
      INSERT #DisplayOrder
      SELECT p.ProductID, 1
      FROM [Nop_Product] p
      ORDER BY p.[Name]
    END
  
  --paging
  DECLARE @PageLowerBound int
  DECLARE @PageUpperBound int
  DECLARE @RowsToReturn int
  
  SET @RowsToReturn = @PageSize * (@PageIndex + 1)  
  SET @PageLowerBound = @PageSize * @PageIndex
  SET @PageUpperBound = @PageLowerBound + @PageSize + 1
  
  CREATE TABLE #PageIndex
  (
    IndexID int IDENTITY (1, 1) NOT NULL,
    ProductVariantID int NOT NULL,
    DisplayOrder int NOT NULL,
  )
  INSERT INTO #PageIndex (ProductVariantID, DisplayOrder)
  SELECT DISTINCT isnull(pv.ProductVariantID,0) ProductVariantID -- pv.ProductVariantID
                     , do.DisplayOrder
  FROM Nop_Product p with (NOLOCK)
  LEFT OUTER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON p.ProductID=pcm.ProductID
  LEFT OUTER JOIN Nop_Product_Manufacturer_Mapping pmm with (NOLOCK) ON p.ProductID=pmm.ProductID
  LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID
  JOIN #DisplayOrder do on p.ProductID = do.ProductID
  WHERE
    (
      (
        @ShowHidden = 1 OR p.Published = 1
      )
    AND
      (
        @ShowHidden = 1 OR pv.Published = 1
      )
    AND
      (
        p.Deleted=0
      )
    AND (
        @CategoryID IS NULL OR @CategoryID=0
        OR pcm.CategoryID=@CategoryID
      )
    AND (
        @ManufacturerID IS NULL OR @ManufacturerID=0
        OR pmm.ManufacturerID=@ManufacturerID
      )
    AND  (
        -- search standard content
        patindex(@Keywords, isnull(p.name, '')) > 0
        or patindex(@Keywords, isnull(pv.name, '')) > 0
        or patindex(@Keywords, isnull(pv.sku , '')) > 0
      )
    AND
      (
        @ShowHidden = 1
        OR
        (getutcdate() between isnull(pv.AvailableStartDateTime, '1/1/1900') and isnull(pv.AvailableEndDateTime, '1/1/2999'))
      )
    )
  ORDER BY do.DisplayOrder

  --total records
  SET @TotalRecords = @@rowcount  
  SET ROWCOUNT @RowsToReturn
  
  --return
  SELECT  
    pv.*
  FROM
    #PageIndex [pi]
    INNER JOIN Nop_ProductVariant pv on pv.ProductVariantID = [pi].ProductVariantID
  WHERE
    [pi].IndexID > @PageLowerBound AND
    [pi].IndexID < @PageUpperBound
    AND [pi].ProductVariantID <> 0
  ORDER BY
    IndexID
  
  SET ROWCOUNT 0

  DROP TABLE #DisplayOrder
  DROP TABLE #PageIndex
END