IsAlpha in a select... 1

[Ju1c3]

MemberList.Source = "SELECT * FROM MemberList WHERE " + ISNUMERIC(LEFT(CompanyName, 1)) = TRUE + " ORDER BY CompanyName ASC"


ok what i am trying to do is when someone makes a selection from a list a-z and with a #, i want it to return all of the companies that start with a letter or a number. i have the other 2 instances taken care of, but i cant for the life of me figure this out. if anyone has any insight into this, i would be much appreciated.

 

[gmmastros]

For company names that start with a letter, use this...

[tt][blue]Select * From MemberList Where CompanyName Like '[A-Z]%'[/blue][/tt]

For company names that start with a number, use this...

[tt][blue]Select * From MemberList Where CompanyName Like '[0-9]%'[/blue][/tt]



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Ju1c3]

i've tried that before too. this is my whole if statement block i am trying to run and it seems like it doesnt even get into the second if. so i am starting to get really peeved. i am a networking guy and i dont know why my boss took this job on but i got stuck with it.

btw.... newalpha is the value it returs after it makes a selection and i have a parser in the thing taking out the %20's so i am just trying to get this done. Also, num is the alpha returned when the hit the # cuz the damn url doesnt seem to like it. any more help is always appreciated

if len(newAlpha)>4 then
MemberList.Source = "SELECT * FROM MemberList WHERE CompanyType LIKE '" + newAlpha + "%' ORDER BY CompanyName ASC"
elseif newAlpha = "NUM" then
MemberList.Source = "Select * FROM MemberList WHERE CompanyName Like '[0-9]%' ORDER BY CompanyName ASC"
else
MemberList.Source = "SELECT * FROM MemberList WHERE CompanyName LIKE '" + Replace(MemberList__MMColParam, "'", "''") + "%' ORDER BY CompanyName ASC"
end if

 

[gmmastros]

I'm guessing that this is classic ASP? I would suggest that you add some debug code so you can see what's going on. It may help you to narrow down the problem.

if len(newAlpha)>4  then
    MemberList.Source = "SELECT * FROM MemberList WHERE CompanyType LIKE '" + newAlpha + "%' ORDER BY CompanyName ASC"
elseif newAlpha = "NUM" then
    MemberList.Source = "Select * FROM MemberList WHERE CompanyName Like '[0-9]%' ORDER BY CompanyName ASC"
else
    MemberList.Source = "SELECT * FROM MemberList WHERE CompanyName LIKE '" + Replace(MemberList__MMColParam, "'", "''") + "%' ORDER BY CompanyName ASC"
end if

Response.Write "NewAlpha: " & newAlpha & "<br />" & MemberList.Source & "<br /><br />"
Response.End

Obviously, the lines I added at the bottom are for debug purposes only, but it will show you what the values are so that you can see where things are going goofy. Also, since the SQL will display, I suggest you copy/paste it to a Query Analyzer window and run it.

I know this isn't much help, but I don't know what else to say. Sorry.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Ju1c3]

got it. the whole not debugging thing was throwing me off. its why i like java and such better the this. anyways, it all had to do with the fact i was using NUM instead of Num when i was passing that value for new alpha when they clicked the #. it works, and i am very greatful for the debug code. thanks a lot

 

[gmmastros]

I'm glad I was able to help, even if just a little. I'm also glad that you got your problem resolved.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom