Hello,
Wonderful module, but I'm having a frustrating problem. Every once in a while, all the SQL code I enter in the Primary Command field just disappears. Everything else, the text search, filter selection options, everything stays the same, but the code just disappears. There are no errors of any kind. I couldn't find any other threads describing a problem like this. I'm using version 5.10.1, and DNN 5.6.1. I'm using the module on other places on the site without a problem, so could it be something in the SQL code itself?
Thanks,
Richard
SELECT ArticleID, Title, NumberofViews as 'Views', StartDate as 'Publish', CommentCount, CAST(DnnForge_NewsArticles_Article.Summary as NVARCHAR(4000)) Summary
,(SELECT dbo.RemoveNonAlphaCharacters('http://www.advisorconnexions1.com/v2/MarshMarketPlace/CarrierContent/tabid/1803/ID/'+CAST(ArticleID as varchar(10))+'/language/en-US/'+Title+'.aspx')) as Link
INTO #SearchResults
FROM dbo.DnnForge_NewsArticles_Article
WHERE ModuleID = 4857
ALTER TABLE #SearchResults
ADD Carrier varchar(50) NULL
ALTER TABLE #SearchResults
ADD Industry varchar(50) NULL
ALTER TABLE #SearchResults
ADD Product varchar(50)
--Declare the Cursor and variables
DECLARE testingCursor CURSOR for
SELECT ArticleID, CustomFieldID, CustomValue FROM dbo.DnnForge_NewsArticles_CustomValue
WHERE CustomFieldID IN (132, 129, 133)
OPEN TestingCursor
declare @field1 varchar(40) ;
declare @field2 varchar(40) ;
declare @field3 varchar(40) ;
FETCH NEXT FROM testingcursor
INTO @field1, @field2, @field3
While @@FETCH_STATUS = 0
Begin
IF (@field2 = 129)
BEGIN
UPDATE #SearchResults
SET Carrier = @field3
WHERE #SearchResults.ArticleID = @field1
END
ELSE IF (@field2 = 132)
BEGIN
UPDATE #SearchResults
SET Industry = @field3
WHERE #SearchResults.ArticleID = @field1
END
ELSE IF (@field2 = 133)
BEGIN
UPDATE #SearchResults
SET Product = @field3
WHERE #SearchResults.ArticleID = @field1
END
FETCH NEXT FROM testingCursor
INTO @field1, @field2, @field3
End
CLOSE testingCursor
DEALLOCATE testingCursor
-- The results to be returned to the browser
SELECT
[@MakeLink:Link:[Title]:TRUE],
Views
, CommentCount
, Summary
, Publish
, Industry
, Product
,'http://www.advisorconnexions1.com/Portals/11/images/MMP/Documents/' +
(CASE CAST(Carrier AS NVARCHAR(40))
WHEN 'Affiliated FM' THEN 'Affiliated FM/Affiliated_FM_Small_Logo.jpg'
WHEN 'Allianz' THEN 'Allianz/Allianz_Small_Logo.gif'
WHEN 'Axis' THEN 'Axis/AXIS_Small_Logo.jpg'
WHEN 'Beazley' THEN 'Beazley/Beazley_Small_Logo.gif'
WHEN 'Catlin' THEN 'Catlin/Catlin_Small_Logo.jpg'
WHEN 'CNA' THEN 'CNA/CNA_Small_Logo.gif'
WHEN 'Crum & Forster' THEN 'Crum and Foster/Crum_and_Foster_Small_Logo.gif'
WHEN 'Endurance' THEN 'Endurance/Endurance_Small_Logo.jpg'
WHEN 'Fireman''s Fund' THEN 'Fireman''s Fund/Firemans_Fund_Small_Logo.gif'
WHEN 'GAPRIS' THEN 'GAPRIS/Gapris_Small_Logo.gif'
WHEN 'Hartford Steam Boiler' THEN 'HSB/HSB_Small_Logo.jpg'
WHEN 'Hiscox' THEN 'Hiscox/Hiscox_Small_Logo.gif'
WHEN 'Ironshore' THEN 'Ironshore/Ironshore_Small_Logo.JPG'
WHEN 'Liberty International Underwriters' THEN 'LIU/LIU_Small_Logo.gif'
WHEN 'Liberty Mutual Group' THEN 'Liberty Mutual/Liberty_Mutual_Small_Logo.jpg'
WHEN 'Maiden Specialty' THEN 'Maiden Re/Maiden_Specialty_Small.gif'
WHEN 'Navigators' THEN 'Navigators/Navigators_Small_Logo.gif'
WHEN 'PMA' THEN 'PMA/PMA_Small_Logo2.jpg'
WHEN 'Safety National' THEN 'Safety National/SAFETYNAT_Small_Logo.jpg'
WHEN 'Sentry' THEN 'Sentry/Sentry_Small_Logo.gif'
WHEN 'Swiss Re' THEN 'Swiss Re/Swiss_Re_Small_Logo.jpg'
WHEN 'The Hartford' THEN 'The Hartford/The_Hartford_Small_Logo.gif'
WHEN 'Travelers' THEN 'Travelers/Travelers_Small_Logo.jpg'
WHEN 'X.L.' THEN 'XL/XL_Small_Logo.gif'
WHEN 'Zurich' THEN 'Zurich/Zurich_Small_Logo.jpg'
WHEN 'Victor O. Schinnerer & Co.' THEN 'Schinnerer/Schinnerer_Small_Logo.gif'
ELSE 'noPicture300.gif'
END) as 'CarrierLogo'
, Title as altText
,[@MakeLink:Link:Read More >>:TRUE] as NewLink
FROM #SearchResults
WHERE Carrier LIKE '%[Parm:Carrier]%'
AND Industry LIKE '%[Parm:Industry]%'
AND Product LIKE '%[Parm:Product]%'
AND (Title like '%[Search:Text]%' OR Summary like '%[Search:Text]%' OR Carrier like '%[Search:Text]%' OR Industry like '%[Search:Text]%' OR Product like '%[Search:Text]%')
DROP TABLE #SearchResults