SQL query on ASP page not working

I am pulling data to an ASP page from excel data in a named range. the named range is called "tblData" and I want the data from all the assets that are named a certain thing. My current sql statement is below and it retuns all assets but it does only pull the tagnames I am looking for but just not the specific assets, it pull all assets. the piece in italics is whats not working.

sql = "SELECT * FROM tblData where Asset ='FB%DPO' AND TagName = 'PercentMCR' OR 'BoilerCycles' OR 'BoilerThermalEfficiency' OR 'BoilerAvailableWorkEfficiency';"
jlcannonAsked:
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.

ste5anSenior DeveloperCommented:
Seems you want:

SELECT * 
FROM tblData 
WHERE Asset ='FB%DPO'  
  AND TagName IN ('PercentMCR', 'BoilerCycles', 'BoilerThermalEfficiency', 'BoilerAvailableWorkEfficiency');

Open in new window

0
Paul JacksonSoftware EngineerCommented:
You need to repeat the column you are comparing against in the where clause when using OR :

SELECT * FROM tblData 
where Asset ='FB%DPO'  AND (TagName = 'PercentMCR' OR TagName =  'BoilerCycles' OR TagName = 'BoilerThermalEfficiency' OR TagName = 'BoilerAvailableWorkEfficiency');

Open in new window

0
jlcannonAuthor Commented:
when i use that it returns nothing. my asset names are like FB3-DPO or FB34DPO so I thought using the wildcard % would get all of what I needed for asset name.  but the above solution does not pull any data and it should as FB%DPO should return the records to the two assets I listed above.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Paul JacksonSoftware EngineerCommented:
try using the like operator :

SELECT * FROM tblData 
where Asset Like 'FB%DPO'  AND (TagName = 'PercentMCR' OR TagName =  'BoilerCycles' OR TagName = 'BoilerThermalEfficiency' OR TagName = 'BoilerAvailableWorkEfficiency'); 

Open in new window

0
jlcannonAuthor Commented:
Hmm still returns nothing I have attached a copy of my excel sheet.  and my code is simple, using the lates solution:

sql = "SELECT * FROM tblData where Asset Like 'FB%DPO'  AND (TagName = 'PercentMCR' OR TagName =  'BoilerCycles' OR TagName = 'BoilerThermalEfficiency' OR TagName = 'BoilerAvailableWorkEfficiency');"

set RS = server.createobject("adodb.recordset")
RS.open sql,Conn,adOpenForwardOnly,adLockReadOnly
Copy.xlsx
0
ste5anSenior DeveloperCommented:
The wildcard is the asterisk *.
0
jlcannonAuthor Commented:
I have used both * and % and neither return the data
0
jlcannonAuthor Commented:
It seems as if If I use Asset LIKE 'FB%' is works to pull up all records with Asset names that begin with FB but if I use '%DPO' it pulls nothing. Same if I use 'FB*' it pulls all FB but if I use '*DPO' it pulls nothing. Also if I add in the line from AND on to the end it again pulls nothing even when used in conjunction with FB% than by itself returns all records where the asset name begins with FB... I am at a loss.
0
ste5anSenior DeveloperCommented:
Yup, it's Excel. You cannot rely on what you see.

The value is 'FB4-DPO    ' with trailing spaces. Thus you need to go for [Asset] LIKE 'FB%DPO%'.
0

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
jlcannonAuthor Commented:
thank you. found it was trailing spaces in the cells so now to figure out how to use the equivelent of TRIM in the vba code I am using to import the data from a CSV file. Off to a new searcha nd probably new question here....
0
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
Query Syntax

From novice to tech pro — start learning today.