Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6
This is the forum to discuss the Wrox book ASP.NET 2.0 Instant Results by Imar Spaanjaars, Paul Wilton, Shawn Livermore; ISBN: 9780471749516
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 21st, 2007, 02:10 PM
Registered User
 
Join Date: Aug 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Bugbase Search Stored Procedure

I think I have this right. The stored procedure for the bug search is designed to return records even if all the search criteria is not met. For example: If you search for a bug Status of New and also Open, it will display a bug that is new and not open because one of the criteria was met. For my needs it should only display a bug that absolutely meets all of the criteria highlighted in the list boxes. If a bug is Open, but not New, then it should not appear on the list if the criteria was for Open and New. I know that this can probably be done by modifying the stored procedure but it is beyond my knowledge of sql. Am I correct? Any ideas? Thanks in advance and thanks for a great book!

  #2 (permalink)  
Old August 21st, 2007, 02:20 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

This may be a lot easier to fix than you think.

The values for properties like Status are mutually exclusive. So, a bug cannot be New and Open at the same time.

To make sure you only get the data that you require, simple disable multiple selection on the drop down list. This way, you can only select a single status and thus get the records you require.

Does this help?

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
  #3 (permalink)  
Old August 21st, 2007, 02:35 PM
Registered User
 
Join Date: Aug 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I explained that poorly. I am modifying the bugbase to keep track of attorneys instead of bugs (I know, I know, whats the difference) and allowing the user to select multiple items that are not mutually exclusive. I am using tables with one to many relationships. Example, the main table is called attorney, the languages the attorney speaks are stored in AttorneyLanguages, the locations the attorney will go to are stored in AttornyLocations and so on. The stored procedure is below if that is helpful. Thanks so much for the quick reply Imar. By the way, do you have a new book coming soon?



CREATE PROCEDURE [dbo].[sprocAttorneySearchList]

@keyword nvarchar(50) = null,
@panels nvarchar(50) = null,
@languages nvarchar(50) = null,
@locations nvarchar(50) = null,
@specialNeeds nvarchar(50) = null,
@status nvarchar(10) = null

AS
SELECT DISTINCT Attorney.Id, Attorney.FirstName, Attorney.LastName, Attorney.Status

FROM

Attorney

LEFT OUTER JOIN AttorneyPannels ON Attorney.Id = AttorneyPannels.AttorneyId
LEFT OUTER JOIN dbo.fnSplit(@panels, ',') AS joinPanels ON AttorneyPannels.PanelId LIKE joinPanels.value
LEFT OUTER JOIN AttorneyLanguages ON Attorney.Id = AttorneyLanguages.AttorneyId
LEFT OUTER JOIN dbo.fnSplit(@languages, ',') AS joinLanguages ON AttorneyLanguages.LanguageId LIKE joinLanguages.value
LEFT OUTER JOIN AttorneyLocations ON Attorney.Id = AttorneyLocations.AttorneyId
LEFT OUTER JOIN dbo.fnSplit(@locations, ',') AS joinLocations ON AttorneyLocations.LocationId LIKE joinLocations.value
LEFT OUTER JOIN AttorneySpecialNeeds ON Attorney.Id = AttorneySpecialNeeds.AttorneyId
LEFT OUTER JOIN dbo.fnSplit(@specialNeeds, ',') AS joinSpecialNeeds ON AttorneySpecialNeeds.SpecialNeedId LIKE joinSpecialNeeds.value




WHERE (AttorneyPannels.PanelId LIKE joinPanels.value OR @panels IS NULL)
AND (Attorney.Status LIKE @status OR @status IS NULL)
AND (AttorneyLanguages.LanguageId LIKE joinLanguages.value OR @languages IS NULL)
AND (AttorneyLocations.LocationId LIKE joinLocations.value OR @locations IS NULL)
AND (AttorneySpecialNeeds.SpecialNeedId LIKE joinSpecialNeeds.value OR @specialNeeds IS NULL)
AND (Attorney.LastName LIKE '%' + @keyword + '%' OR Attorney.FirstName LIKE '%' + @keyword + '%' OR @keyword IS NULL)

  #4 (permalink)  
Old August 21st, 2007, 03:04 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Right, that's indeed a different story.

If I understand the situation correctly, look into INNER JOIN instead of LEFT OUTER JOIN. The latter returns all records from the left table, regardless of whether there's a match with the right table.

Using an INNER JOIN gives you only those records that have a match on both sides. So, giving a split string with two record IDs should give you the two records in the matching junction table.

Yes, working on a new project. Will post an update on that pretty soon on my own blog.

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
  #5 (permalink)  
Old August 21st, 2007, 06:15 PM
Registered User
 
Join Date: Aug 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks again. I guess the best way to describe what I am after is this: If one of the comma-delimited lists that I am sending to the stored procedure has 3 items in it, and I only have a match for 2 of those items, I should end up with an empty record set, not 2 records. I think I need to find a different solution, but I wanted to use stored procedures becuase I am just learning them. Thanks again.

John

  #6 (permalink)  
Old August 22nd, 2007, 12:51 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Quote:
quote:I think I need to find a different solution
Yeah, this is the kind of problem that you need to approach step by step. Remove 90% of the code and then start adding things back in.

It's not an easy requirement, and it's not something I can do over a P2P post. Sorry...

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Fulltext Search Stored Procedure. maulik77 SQL Server 2000 0 December 28th, 2006 11:52 PM
Stored Procedure rajanikrishna SQL Server 2000 0 July 18th, 2005 05:01 AM
Help About Stored Procedure zhuge6 BOOK: ASP.NET Website Programming Problem-Design-Solution 3 May 20th, 2005 09:27 AM
advance search with stored procedure harpua Classic ASP Databases 1 December 29th, 2004 04:02 AM
Stored Procedure help flyin SQL Server 2000 4 August 3rd, 2004 07:37 AM



All times are GMT -4. The time now is 07:30 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.