Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-01-02
6
Medium Priority
?
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 19

Assisted Solution

by:Rimvis
Rimvis earned 500 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 500 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 7

Assisted Solution

by:whosbetterthanme
whosbetterthanme earned 500 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 500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

618 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