Avatar of garethtnash
garethtnash
Flag for United Kingdom of Great Britain and Northern Ireland asked on

SQL Query not functioning as expected,... help!

Hello All, Happy New Year,

I've got a little issue with a SQL Query not responding as expected, sometimes it works sometimes it does't (causes the asp page to crash)... I've tried debugging, but I'm stumped.

Here's the Query..

CREATE PROCEDURE [dbo].[JBViewAdvert]
@SiteID int,
@ID int,
@agent nvarchar(1000)
AS
SET NOCOUNT ON;
BEGIN
DECLARE @views int;

/*Set Site Parameters */
IF Exists (Select JBAID from dbo.JBAdvert WHERE JBASiteID = @SiteID AND JBAID = @ID)
BEGIN
IF (
@agent NOT LIKE '%Google%' 
AND @agent NOT LIKE '%google%' 
AND @agent NOT LIKE '%slurp%' 
AND @agent NOT LIKE '%twiceler%' 
AND @agent NOT LIKE '%msnbot%' 
AND @agent NOT LIKE '%inspyder%' 
AND @agent NOT LIKE '%baidu%' 
AND @agent NOT LIKE '%BDFetch%' 
AND @agent NOT LIKE '%bdbrandprotect%' 
AND @agent NOT LIKE '%Feed%' 
AND @agent NOT LIKE '%jobcrawler%' 
AND @agent NOT LIKE '%wise-guys%' 
AND @agent NOT LIKE '%tesco%' 
AND @agent NOT LIKE '%Wanadoo%' 
AND @agent NOT LIKE '%eSobiSubscriber%' 
AND @agent NOT LIKE '%Jeeves%' 
AND @agent NOT LIKE '%Jobrapido%' 
AND @agent NOT LIKE '%spbot%' 
AND @agent NOT LIKE '%trovitBot%' 
AND @agent NOT LIKE '%twiceler%' 
AND @agent NOT LIKE '%oodlebot%' 
AND @agent NOT LIKE '%Pingdom%' 
AND @agent NOT LIKE '%PostRank%' 
AND @agent NOT LIKE '%R6_FeedFetcher%'
AND @agent NOT LIKE '%TwitterFeed%'
AND @agent NOT LIKE '%Wget%'
AND @agent NOT LIKE '%Yandex%'
AND @agent NOT LIKE '%SimpleFeed%'   
AND @agent IS NOT NULL) 
BEGIN
SELECT @views=SUM(AdViews) +1
            FROM dbo.JBADView
            WHERE AdvertID = @ID;
 
IF (@views IS NOT NULL)
    UPDATE dbo.JBADView
    SET AdViews = @views
    Where AdvertID = @ID
ELSE 
   INSERT INTO dbo.JBADView(AdvertID, SiteID, AdViews)
       VALUES(@ID, @SiteID, 1)
END
SELECT
1 as AdLive,
AD.JBAID as JBAID, 
NetSolva.CleanHTML(Lower(AD.JBATitle)) AS JBATitle, 
AD.JBAEmplymentType as JBAEmplymentType, 
NetSolva.CleanHTML(AD.JBAReference) as JBAReference, 
AD.JBALocation as JBALocation, 
AD.JBACategory as JBACategory, 
NetSolva.CleanHTML(AD.JBAPayRate) as JBAPayRate, 
CONVERT(CHAR(11),AD.JBADatePosted,106) AS JBADatePosted, 
JBADescription, 
NetSolva.CleanHTML(LEFT(JBADescription,200)) AS MetaJBADescription, 
CASE WHEN [Hours] = 2 then 'Part Time' ELSE 'Full Time' End as [Hours], 
CL.Name 
JBCLName, 
CL.Logo 
JBCLLogo, 
CL.ID 
JBCLID 
FROM dbo.JBAdvert AD 
inner join dbo.Client CL on CL.ID = AD.ClientID 
WHERE AD.JBAID = @ID AND AD.JBASiteID = @SiteID
END
ELSE IF EXISTS(Select JBAID from dbo.JBExpiredAdvert WHERE JBASiteID = @SiteID AND JBAID = @ID)
BEGIN
IF (
@agent NOT LIKE '%Google%' 
AND @agent NOT LIKE '%google%' 
AND @agent NOT LIKE '%slurp%' 
AND @agent NOT LIKE '%twiceler%' 
AND @agent NOT LIKE '%msnbot%' 
AND @agent NOT LIKE '%inspyder%' 
AND @agent NOT LIKE '%baidu%' 
AND @agent NOT LIKE '%BDFetch%' 
AND @agent NOT LIKE '%bdbrandprotect%' 
AND @agent NOT LIKE '%Feed%' 
AND @agent NOT LIKE '%jobcrawler%' 
AND @agent NOT LIKE '%wise-guys%' 
AND @agent NOT LIKE '%tesco%' 
AND @agent NOT LIKE '%Wanadoo%' 
AND @agent NOT LIKE '%eSobiSubscriber%' 
AND @agent NOT LIKE '%Jeeves%' 
AND @agent NOT LIKE '%Jobrapido%' 
AND @agent NOT LIKE '%spbot%' 
AND @agent NOT LIKE '%trovitBot%' 
AND @agent NOT LIKE '%twiceler%' 
AND @agent NOT LIKE '%oodlebot%' 
AND @agent NOT LIKE '%Pingdom%' 
AND @agent NOT LIKE '%PostRank%' 
AND @agent NOT LIKE '%R6_FeedFetcher%'
AND @agent NOT LIKE '%TwitterFeed%'
AND @agent NOT LIKE '%Wget%'
AND @agent NOT LIKE '%Yandex%'
AND @agent NOT LIKE '%SimpleFeed%'   
AND @agent IS NOT NULL) 
BEGIN
SELECT @views=SUM(AdViews) +1
            FROM dbo.JBADView
            WHERE AdvertID = @ID;
 
IF (@views IS NOT NULL)
    UPDATE dbo.JBADView
    SET AdViews = @views
    Where AdvertID = @ID
ELSE 
   INSERT INTO dbo.JBADView(AdvertID, SiteID, AdViews)
       VALUES(@ID, @SiteID, 1)
END
SELECT
2 as AdLive,
AD.JBAID, 
AD.JBAID as JBAID, 
NetSolva.CleanHTML(Lower(AD.JBATitle)) AS JBATitle, 
AD.JBAEmplymentType as JBAEmplymentType, 
NetSolva.CleanHTML(AD.JBAReference) as JBAReference, 
AD.JBALocation as JBALocation, 
AD.JBACategory as JBACategory, 
NetSolva.CleanHTML(AD.JBAPayRate) as JBAPayRate, 
CONVERT(CHAR(11),AD.JBADatePosted,106) AS JBADatePosted, 
JBADescription,
CASE WHEN [Hours] = 2 then 'Part Time' ELSE 'Full Time' End as [Hours], 
NetSolva.CleanHTML(LEFT(JBADescription,200)) AS MetaJBADescription, 
CL.Name 
JBCLName, 
CL.Logo 
JBCLLogo, 
CL.ID 
JBCLID 
FROM dbo.JBExpiredAdvert AD 
inner join dbo.Client CL on CL.ID = AD.ClientID 
WHERE AD.JBAID = @ID AND AD.JBASiteID = @SiteID
END
ELSE
BEGIN
SELECT
3 as AdLive
END
END



GO

Open in new window


The intermittent error I get is --

File Name: /detail.asp
Line Number: 159
Description: Item cannot be found in the collection corresponding to the requested name or ordinal.
Category: ADODB.Fields
ASP Number: -2146825023
ASP Column: -1

Line 159 of my code is --

If (Request.ServerVariables("HTTP_X_REQUEST_URI")) <> "/i/" & descript.Fields.Item("JBAID") & "/" & LCase(SEOFriend(descript.Fields.Item("JBATitle"))) & "-jobs-in-" & LCase(SEOFriend(descript.Fields.Item("JBALocation"))) Then 

Open in new window


of

<% 
If Not descript.EOF Then 
If (Request.ServerVariables("HTTP_X_REQUEST_URI")) <> "/i/" & descript.Fields.Item("JBAID") & "/" & LCase(SEOFriend(descript.Fields.Item("JBATitle"))) & "-jobs-in-" & LCase(SEOFriend(descript.Fields.Item("JBALocation"))) Then 
Response.Status="301 Moved Permanently"
Response.AddHeader "Location", "/i/" & descript.Fields.Item("JBAID") & "/" & LCase(SEOFriend(descript.Fields.Item("JBATitle"))) & "-jobs-in-" & LCase(SEOFriend(descript.Fields.Item("JBALocation")))
End If 

Else
Response.Status = "404 Not Found" 
End if
%>

Open in new window


I'm stumped,

Please help......

Thank you
Microsoft SQL Server 2008ASPMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
garethtnash

8/22/2022 - Mon
SOLUTION
Rimvis

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
garethtnash

ASKER
Thanks Rimvis, but in this example we are hitting the section

SELECT
1 as AdLive

I have tested the query on the Server.

Any other suggestions?

Thanks
SOLUTION
Daniel Van Der Werken

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
whosbetterthanme

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Anthony Perkins

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
garethtnash

ASKER
Sorry for the LATE response all. not been around for a while..

Thanks
Your help has saved me hundreds of hours of internet surfing.
fblack61