Dynamic SQL Query gives one record

Hello all;
I hope that I titled this question properly.

OK.
This is the way I have it set up.
Textarea has the name of songs.
One song per Row

Now, this is the Variable, with the generated code from the Textarea.

getAll = "Songs.SongTitle='Strutter ' or Songs.SongTitle='Black_Diamond ' or Songs.SongTitle='Parasite ' or Songs.SongTitle='Strange_Ways ' or Songs.SongTitle='Rock_Bottom ' or Songs.SongTitle='God_of_Thunder' or Songs.SongTitle='Love_Gun' or Songs.SongTitle='She ' or Songs.SongTitle='I_Stole_Your_Love'" 

Open in new window


OK, this is my SQL Query.

getList.commandtext="SELECT Albums.AlbumName, Songs.SongTitle, Songs.Writers, Songs.Vocals, Songs.SID FROM Albums RIGHT OUTER JOIN Songs ON Albums.AID = Songs.AID WHERE "&getAll&""

set rsList = getList.execute
while not rsList.eof%>
<%=rsList("Writers")%>
<%rsList.movenext
wend
%>

Open in new window


The results is:
One single records, instead of the 9 that are listed.

Just now, I removed the WHILE NOT.
To see what it would give, and it gives the same exact results as it does when it is in place.

The script works, with the way I am doing it, just giving on 1 result, instead of the nine that is intended.

Any idea's on this?
(I have been answering my own questions hear lately, just right after posting, so, maybe it will happen again)
Carrzkiss
LVL 31
Wayne BarronAuthor, Web DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bcnagelCommented:
Hello.

Have you tested the SQL separately from the ASP? Does the SQL statement actually return 9 records?
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Yep, did that in SSMS, everything works like a charm.
Just does not render out the 9 records.
Dave BaldwinFixer of ProblemsCommented:
Print out 'getList.commandtext' and compare it to the query you used in SSMS,
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

PortletPaulEE Topic AdvisorCommented:
Please try reversing the table order so that you use a left outer join.

SELECT Songs.SID, Songs.SongTitle, Songs.Writers, Songs.Vocals, Albums.AlbumName
FROM Songs
LEFT OUTER JOIN Albums ON Songs.AID = Albums.AID
WHERE "&getAll&""

Do you actually need an outer join? The point is that whenever you use an outer join there is potential for NULLS, in your query anything from [Albums] could be null if there is no match in [Songs].

btw:
there is no rule for this, but most folks who write a lot of SQL don't use right outer joins, preferring instead to re-order the table precedence so the less important tables are left joined. Here for example 4 of 5 columns come from Songs so i would use that as the "FROM table".

{+edit}
the devil can be in the detail.
WHAT is INSIDE that  "&getAll&"" ?
Wayne BarronAuthor, Web DeveloperAuthor Commented:
@Dave Baldwin
I did the
response.write getList.commandtext
And it listed out what I expected, and it ran in SSMS, and returned ALL 9 records as expected.

SELECT Albums.AlbumName, Songs.SongTitle, Songs.Writers, Songs.Vocals, Songs.SID FROM Albums left OUTER JOIN Songs ON Albums.AID = Songs.AID WHERE Songs.SongTitle='Strutter ' or Songs.SongTitle='Black_Diamond ' or Songs.SongTitle='Parasite ' or Songs.SongTitle='Strange_Ways ' or Songs.SongTitle='Rock_Bottom ' or Songs.SongTitle='God_of_Thunder ' or Songs.SongTitle='Love_Gun ' or Songs.SongTitle='She ' or Songs.SongTitle='I_Stole_Your_Love'

Open in new window


@Paul Maxwell
Changing it to left, did not make a difference, still outputs 1 record out of the nine.
As for this...
the devil can be in the detail.
WHAT is INSIDE that  "&getAll&"" ?

the getAll = gets everything from the textarea, that is filtered, through everything else, that makes it into a suitable string that SQL can read.

Example.

SongName = protectSQL(Request.form("SongName"))
Under = replace(SongName," ","_")
theList = replace(Under, "/","' or Songs.SongTitle='")
theWhole = Right(theList,Len(theList)-5)
getAll = theWhole&"'"

Open in new window

The above code, will filter it out, and give you the output, that SQL can read.

So, without everything, still only 1 record
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Oh yea.
The record that it is displaying, is the very last record in the query.
PortletPaulEE Topic AdvisorCommented:
You changed the JOIN but nothing else. That's not correct. You also need to swap the table references. See my previous post. Also not I changed the column layout.
Songs.SID, Songs.SongTitle, Songs.Writers, Songs.Vocals, Albums.AlbumName
SELECT
      Albums.AlbumName
    , Songs.SongTitle
    , Songs.Writers
    , Songs.Vocals
    , Songs.SID
FROM Albums <<< change
      LEFT OUTER JOIN Songs ON Albums.AID = Songs.AID <<< change
WHERE Songs.SongTitle = 'Strutter '
      OR Songs.SongTitle = 'Black_Diamond '
      OR Songs.SongTitle = 'Parasite '
      OR Songs.SongTitle = 'Strange_Ways ' << trailing white spaces
      OR Songs.SongTitle = 'Rock_Bottom '  << trailing white spaces
      OR Songs.SongTitle = 'God_of_Thunder ' << trailing white spaces
      OR Songs.SongTitle = 'Love_Gun ' << trailing white spaces
      OR Songs.SongTitle = 'She ' << trailing white spaces
      OR Songs.SongTitle = 'I_Stole_Your_Love'

Open in new window

While you get 9 rows in SSMS, it is worth noting that you have trailing spaces in some of those song names. Which row of the 9 do you get to see?

Do all 9 rows have an AlbumName?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Also, if I past in the output from the
response.write getList.commandtext
into the actual page, and run it, it gives me 9 records.
Wayne BarronAuthor, Web DeveloperAuthor Commented:
I was wondering about that as well.
How can I get rid of the trailing spaces?
Using the code here.

SongName = protectSQL(Request.form("SongName"))
Under = replace(SongName," ","_")
theList = replace(Under, "/","' or Songs.SongTitle='")
theWhole = Right(theList,Len(theList)-5)
getAll = theWhole&"'"

Open in new window

PortletPaulEE Topic AdvisorCommented:
don't know (I'm SQL not ASP) but perhaps it isn't a space? some other non printing character maybe?

but don't go down an ASP rabbit hole on the basis of my guess.

Please make sure you swap the tables around if you stick with the left join.
Wayne BarronAuthor, Web DeveloperAuthor Commented:
I got it
Wayne BarronAuthor, Web DeveloperAuthor Commented:
I put everything on a single line on the textarea, and it worked!!!
So, having it all on multiple lines, (one word per line) broke it and created the whitespace at the end.
I changed everything to a single line, and it worked like a charm!!!

That took care of it.
Paul, since you brought up about the space at the ends, I am going to throw the points your way.

Have a good one all.
Wayne
PortletPaulEE Topic AdvisorCommented:
which?
a. the 9 rows
b. the white space
c. the table sequence
d. all the above

:)
PortletPaulEE Topic AdvisorCommented:
LOL, thank you.
Generally don't earn that many ASP points
Wayne BarronAuthor, Web DeveloperAuthor Commented:
The trailing space comment, is what made me think of a way to resolve the issue.
Of which, was to put everything on a single line in a textarea (Of which I am going to most likely change into a input field instead)

It is resolved.
Wayne
Wayne BarronAuthor, Web DeveloperAuthor Commented:
I was wondering about the space at the end, but, like in my other question that I figured out, due to the person coming in, and pointing out the obvious.
sometimes, we just need a fresh pair of eyes to take a look at something with us.
That is the reason why it is good to post your SQL UNEDITED...
I hate it when I try to help someone, and find out that they have edited the heck out of the code, that they need help with.
(I've done it myself....)

Have a good one Paul
Wayne
PortletPaulEE Topic AdvisorCommented:
Cheers, you also.

just don't forget the table sequence (no need to reply)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.