Dynamic SQL Query gives one record

Wayne Barron
Wayne Barron used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hello.

Have you tested the SQL separately from the ASP? Does the SQL statement actually return 9 records?
Wayne BarronAuthor, Web Developer
Top Expert 2009

Author

Commented:
Yep, did that in SSMS, everything works like a charm.
Just does not render out the 9 records.
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
Print out 'getList.commandtext' and compare it to the query you used in SSMS,
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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 Developer
Top Expert 2009

Author

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 Developer
Top Expert 2009

Author

Commented:
Oh yea.
The record that it is displaying, is the very last record in the query.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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?
Wayne BarronAuthor, Web Developer
Top Expert 2009

Author

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 Developer
Top Expert 2009

Author

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 Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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 Developer
Top Expert 2009

Author

Commented:
I got it
Wayne BarronAuthor, Web Developer
Top Expert 2009

Author

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 Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
which?
a. the 9 rows
b. the white space
c. the table sequence
d. all the above

:)
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
LOL, thank you.
Generally don't earn that many ASP points
Wayne BarronAuthor, Web Developer
Top Expert 2009

Author

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 Developer
Top Expert 2009

Author

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 Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Cheers, you also.

just don't forget the table sequence (no need to reply)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial