jlcannon
asked on
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 'BoilerAvailableWorkEffici ency';"
sql = "SELECT * FROM tblData where Asset ='FB%DPO' AND TagName = 'PercentMCR' OR 'BoilerCycles' OR 'BoilerThermalEfficiency' OR 'BoilerAvailableWorkEffici
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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');
ASKER
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 = 'BoilerAvailableWorkEffici ency');"
set RS = server.createobject("adodb .recordset ")
RS.open sql,Conn,adOpenForwardOnly ,adLockRea dOnly
Copy.xlsx
sql = "SELECT * FROM tblData where Asset Like 'FB%DPO' AND (TagName = 'PercentMCR' OR TagName = 'BoilerCycles' OR TagName = 'BoilerThermalEfficiency' OR TagName = 'BoilerAvailableWorkEffici
set RS = server.createobject("adodb
RS.open sql,Conn,adOpenForwardOnly
Copy.xlsx
The wildcard is the asterisk *.
ASKER
I have used both * and % and neither return the data
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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....
Open in new window