Solved

Problem with WHERE Clause

Posted on 2010-11-15
8
527 Views
Last Modified: 2013-11-11
My where clause doesn't return the values from the database. This is how I'm trying to call the values:
WHERE REPORT_MONTH = '%" & Request.Form("MyReport_Month") & "%' AND REPORT_YEAR = '%" & Request.Form("MyReport_Year") & "%'
What am I doning wrong?
Thanks for your help
0
Comment
Question by:baxtalo
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 13

Accepted Solution

by:
samic400 earned 250 total points
ID: 34139234
WHERE REPORT_MONTH LIKE '%" & Request.Form("MyReport_Month") & "%' AND REPORT_YEAR LIKE '%" & Request.Form("MyReport_Year") & "%'
0
 
LVL 13

Assisted Solution

by:Rick
Rick earned 250 total points
ID: 34139238
"WHERE REPORT_MONTH = '" & Request.Form("MyReport_Month") & "' AND REPORT_YEAR = '" & Request.Form("MyReport_Year") & "'
0
 
LVL 13

Expert Comment

by:samic400
ID: 34139257
So it should be something like this -

SELECT * FROM suppliers WHERE supplier_name like '%IBM%';


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 95

Expert Comment

by:Lee W, MVP
ID: 34139326
To clarify so that perhaps in the future you don't have this problem, you only use % when using LIKE, not =.

Assume for the moment "MyReport_Month" = 11

Your code ends up saying:
WHERE REPORT_MONTH = '%11%'
If the field is not exactly, character for character '%11%' it won't find anything.

If you used LIKE instead of =, then you would get hits (but it would potentially return incorrect data - for example, if someone said the month was 1 as in January, then your query using LIKE would return data for January, October, November, and December since all those months have a 1 in them (unless you forced the search to look for 01).

So the correct solution should be rick_gwu's which removes the % signs.

However, if you're using a field where the data is ALWAYS a month number, then that field would likely be better as a numeric field and wouldn't require ' characters to denote text and you wouldn't use "%" signs at all - you would use standard math signs.
0
 

Author Closing Comment

by:baxtalo
ID: 34139327
Thank you, it's working now.
0
 

Author Comment

by:baxtalo
ID: 34139349
I'm sorry leew, I awarded the points before I read your reply. This is how I called the value:
" & Request.Form("MyReport_Year") & "
0
 

Author Comment

by:baxtalo
ID: 34139372
Thank you everyone, I learnt a lot.
Leew, thank you for the detailed explanation.
0
 
LVL 95

Expert Comment

by:Lee W, MVP
ID: 34139387
No problem, just try to understand what you did/what I said so this isn't an issue for you in the future.  I thought the previous posters did a poor job (well, didn't at all) explain what they did to correct your problem in which case, how could you learn?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Classic ASP, Search Recordset on pick list change 1 71
classic asp checkbox uncheck and check 2 69
alert(innerHTML); 8 33
Button function on table is in trouble 3 22
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 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