Solved

SQL query on ASP page not working

Posted on 2014-03-27
10
233 Views
Last Modified: 2014-03-27
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';"
0
Comment
Question by:jlcannon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 34

Expert Comment

by:ste5an
ID: 39958760
Seems you want:

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

Open in new window

0
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 250 total points
ID: 39958781
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
 

Author Comment

by:jlcannon
ID: 39958787
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
How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

 
LVL 29

Expert Comment

by:Paul Jackson
ID: 39958792
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
 

Author Comment

by:jlcannon
ID: 39958975
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
 
LVL 34

Expert Comment

by:ste5an
ID: 39959041
The wildcard is the asterisk *.
0
 

Author Comment

by:jlcannon
ID: 39959059
I have used both * and % and neither return the data
0
 

Author Comment

by:jlcannon
ID: 39959095
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
 
LVL 34

Accepted Solution

by:
ste5an earned 250 total points
ID: 39959136
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
 

Author Closing Comment

by:jlcannon
ID: 39959740
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

Featured Post

ClickHouse in a General Analytical Workload

We have mentioned ClickHouse in some recent posts, where it showed excellent results.

In this article on Experts Exchange, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question