Solved

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

Posted on 2013-01-02
6
284 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
0
Comment
Question by:garethtnash
6 Comments
 
LVL 19

Assisted Solution

by:Rimvis
Rimvis earned 125 total points
ID: 38736696
Hi  garethtnash,

You are most likely hitting this part of your query:

SELECT
3 as AdLive


As you see, it does not return JB fields. As an easy workaround, you can change it to this:

SELECT
3 as AdLive, 0 AS JBAID, '' AS JBATitle, '' AS JBALocation


But you should review your query logic and parameters to ensure this result is what you are expecting.
0
 

Author Comment

by:garethtnash
ID: 38736730
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
0
 
LVL 20

Assisted Solution

by:Daniel Van Der Werken
Daniel Van Der Werken earned 125 total points
ID: 38736734
Without the data in my own hand and not having the NetSolva application, I can't even begin to try this out on my own. However, I can try to point you in where I think you need to go:

First off, the issue you're having is because the final SELECT statement in your stored procedure is returning a null result set. Basically, something doesn't exist in the return value.

You are not handling this. Rather than, say handling a dbNull return via a mid-tier layer or something, you're simply using the result set values from the stored procedure (I think). When there isn't a value, you blow up.

You need to initially figure out what is causing the null result set. I can't help you with that as I don't have the data or the SQL plug-in you're using. If you want to be safe, you could set a condition in the stored procedure that returns *something* no matter what. For example, test if the result set would be null and return some constant string values and and such regardless. At least then you'd have something for your vb .net code to compare against.

Or implement an additional layer, such as a data tier, that manages this and then use that. Check for null returns and handle that separately.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Assisted Solution

by:whosbetterthanme
whosbetterthanme earned 125 total points
ID: 38738204
I suppose it's possible that some of your column references need the table alias such as:

AD.JBADescription

Instead of:
JBADescription

The error is basically saying that the column or alias can't be found in the query.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 38747767
There is no match in either the dbo.Advert or dbo.JBExpiredAdvert tables for:
 JBASiteID = @SiteID AND JBAID = @ID

So it is falling into the last query:
  SELECT  3 AS AdLive

Which does not contain all the other columns in your front-end app.
0
 

Author Closing Comment

by:garethtnash
ID: 38835562
Sorry for the LATE response all. not been around for a while..

Thanks
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question