Link to home
Avatar of Wayne Barron
Wayne BarronFlag for United States of America

asked on

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
Avatar of bcnagel
bcnagel
Flag of United States of America image

Hello.

Have you tested the SQL separately from the ASP? Does the SQL statement actually return 9 records?
Avatar of Wayne Barron

ASKER

Yep, did that in SSMS, everything works like a charm.
Just does not render out the 9 records.
Print out 'getList.commandtext' and compare it to the query you used in SSMS,
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&"" ?
@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
Oh yea.
The record that it is displaying, is the very last record in the query.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
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.
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

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.
I got it
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
which?
a. the 9 rows
b. the white space
c. the table sequence
d. all the above

:)
LOL, thank you.
Generally don't earn that many ASP points
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
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
Cheers, you also.

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