SQL Statement in SelectCommand intermittent

[robpal]

Hi there, I hope someone can tell me what I'm doing wrong here.

I have a SelectCommand in my asp.net page (v4) that pulls a variable from the Profile to run a subquery to get the value to test within the main query. (Confused yet?) This works for 2 of the possible variable but doesn't for the others for some unknown reason. Please could somebody offer some suggestions on what may be the problem?

Here's my code:-

SELECT Act_ID, Act_Name, Genre_ID_1, Genre_ID_2, Genre_ID_3 FROM Acts WHERE (Genre_ID_1 = (SELECT Genre_ID FROM Genres WHERE (Genre_Name = @Genre1))) OR (Genre_ID_2 = (SELECT Genre_ID FROM Genres AS Genres_2 WHERE (Genre_Name = @Genre1))) OR (Genre_ID_3 = (SELECT Genre_ID FROM Genres AS Genres_1 WHERE (Genre_Name = @Genre1)))

Cheers, Rob

[robpal]

Okay, I figured it out and the code I posted wouldn't have helped anyone. I had the variable size set to 3 characters long when I thought I was using a three digit code, however I didn't change it when I started using names. The only names that worked had only three characters. It was this that tipped me off.

So my new variable code looks likes this:-

<SelectParameters>
            <asp:ProfileParameter Name="Genre1" PropertyName="Genre1" 
                Size="50" Type="String" DefaultValue="" />
        </SelectParameters>

I hope that if someone makes the same silly mistake that they can find the answer here in the future.

Rob

[stbuchok]

Why do you have 3 GenreID columns in Acts?

--I believe this should be easier to understand (not sure if it is right though)
Select a.Act_ID, a.Act_Name, a.Genre_ID_1, a.Genre_ID_2, a.Genre_ID_3 
From Acts a
Join Genres g On a.Genre_ID_1 = g.Genre_ID Or a.Genre_ID_2 = g.Genre_ID Or a.Genre_ID_3 = g.Genre_ID
Where g.Genre_Name = @Genrel

I think your bigger problem is your table structure. If you can have more than one Genre for an Act have a table that maps the 2.

ActGenres holds a mapping to the Genres for a particular Act, this allows you to easily add a 4th, 5th or 6th (or any number) of Genres later on without making a schema change.

Now obviously this is contingent on the fact that I read everything properly.

[robpal]

Why do you have 3 GenreID columns in Acts?

--I believe this should be easier to understand (not sure if it is right though)
Select a.Act_ID, a.Act_Name, a.Genre_ID_1, a.Genre_ID_2, a.Genre_ID_3 
From Acts a
Join Genres g On a.Genre_ID_1 = g.Genre_ID Or a.Genre_ID_2 = g.Genre_ID Or a.Genre_ID_3 = g.Genre_ID
Where g.Genre_Name = @Genrel

I think your bigger problem is your table structure. If you can have more than one Genre for an Act have a table that maps the 2.

ActGenres holds a mapping to the Genres for a particular Act, this allows you to easily add a 4th, 5th or 6th (or any number) of Genres later on without making a schema change.

Now obviously this is contingent on the fact that I read everything properly.

Thanks for your response and your suggestions. I don't want to add anymore than 3 genres per act as I feel that this should be enough to cover the majority of acts unless we delve into the depths of sub-genres which is not in the scope of my project.

[stbuchok]

Whenever I hear someone say blah blah blah should be enough, it always ring alarm bells. Just be prepared to make harder changes down the road if you don't make it easily extensible now.

[robpal]

Whenever I hear someone say blah blah blah should be enough, it always ring alarm bells. Just be prepared to make harder changes down the road if you don't make it easily extensible now.

I do understand and I make sure a lot of thought goes in to the design of any data storage, whether it be a database or a vast spreadsheet. In this instance however, I'm very intrigued as to hearing a possible reason that would require me to add more than 3 genre's per act. :-/