We help IT Professionals succeed at work.

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

326 Views
Last Modified: 2013-01-30
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
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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
Daniel Van Der WerkenIndependent Consultant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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

Thanks

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.