asked on

Getting Name from String and display the output (not working with LOOP)

Hello all;

I used the following code and it works great

Code #1:

<%
theName="Strutter (KISS Song)"
str=theName
lbracket=instr(1,str,"(",1)
if lbracket > 1 then
FirstPart = trim(mid(str,1,lbracket-1))
NewSentance = FirstPart
response.write NewSentance 
end if
%>

Open in new window

However, when I put this into a loop, it does not work.

Code #2:

<%
'In the table, there will be many of these rows.
'Strutter (KISS Song)
'Hotter Than Hell (KISS Song)
'Tears are Falling (KISS Song)
'exec...
' So the output should just be the name of the song, and everything inside of the () needs to be gone.
' Mind you, that the (KISS Song), is just the name of this group, other groups will be added later, so I cannot
' Just use a simple replace, to remove the (KISS Song) from the line.

while not rsSongs.eof
'theName="Strutter (KISS Song)"
theName = rsSongs("evTitle")
str=theName
lbracket=instr(1,str,"(",1)
if lbracket > 1 then
FirstPart = trim(mid(str,1,lbracket-1))
NewSentance = FirstPart
response.write NewSentance 
end if
rsSongs.movenext
wend
%>

Open in new window

I did the following, to make sure that I was getting what I supposed to be getting, and I do.

theName = rsSongs("evTitle")
Response.write "|"&rsSongs("evTitle")&"|"
response.end

Open in new window

However, the Code #2 does not give the output, like Code one does.
Is there an issue with having this in a LOOP?

Big Monty

there's no reason why you can't do this in a loop, I'm wondering what you'll get written out to the screen if you do:

while not rsSongs.eof
'theName="Strutter (KISS Song)"
theName = rsSongs("evTitle")
str=theName
lbracket=instr(1,str,"(",1)
Response.Write str & " - " & lbracket & "<br/>"
if lbracket > 1 then
FirstPart = trim(mid(str,1,lbracket-1))
NewSentance = FirstPart
response.write NewSentance
end if
rsSongs.movenext
wend

Scott Fell

A lot of this has to do with your table set up and can be made easy if the data is broken out in your tables.

tblArtist - id, name

tblBand - id, name

tblBandMembers - id, contact_id

tblSongs - id, name, album

tblAlbums - id, name, band_id

tblAlbumSongs - id, song_id

tblSongWriters, id,song_id,artist_id

Then you set up your relations and what you are asking for becomes much easier.

Wayne Barron

ASKER

@ BigMonty.
This was the output

Strutter (KISS Song) - 0
KISS (KISS Album) - 0

OK, this is my Query (Done through a search on the page)

sqlGetSong.commandtext = "SELECT evid, AlbumOrder, evTitle, CONVERT(INT, reverse(SUBSTRING(REVERSE(AlbumOrder), 1, charindex('_', reverse(AlbumOrder))-1))) as theNum from evContent  where AlbumOrder like ? ORDER BY CONVERT(INT, reverse(SUBSTRING(REVERSE(AlbumOrder), 1, charindex('_', reverse(AlbumOrder))-1)));"

Open in new window

The AlbumOrder contains the following.
KISS_Song_1_1
KISS_Song_4_3
KISS_Song_3_8

Broken down.
Band Name _ Song _ Album# _ Song#

Now, the output will be the following.
evTitle = (In order of the list above)
Strutter (KISS Song)
God of Thunder (KISS Song)
She (KISS Song)

So, the output I need is just the song title, without the (KISS Song) on it.

Big Monty

what if you change

lbracket=instr(1,str,"(",1)

to

lbracket=instr( str,"(" )

then what gets written out?

Wayne Barron

ASKER

No change.
If I run the code without the IF Statement,

str=theName
lbracket=instr(1,str,"(")
Response.Write str & " - " & lbracket & "<br/>"
'if lbracket > 1 then
FirstPart = trim(mid(str,1,lbracket-1))
NewSentance = FirstPart
'response.write NewSentance 
'end if

Open in new window

I get the following error.

Invalid procedure call or argument: 'Mid'

Anthony Perkins

Should this:
if lbracket > 1 then
not be:
if lbracket > 0 then

Wayne Barron

ASKER

Hey Anthony, long time.

It does not seem to matter rather I use the 1 or 0
It does not work in a loop.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wayne Barron

ASKER

Works like a charm.
I do not know why I did not think about doing it in SQL instead.
Thank you very much, Scott.

So, that is one down, and still one more to go.