Solved

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

Posted on 2013-01-02
6
264 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 19

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now