If Checkbox1.Value = 1 Then ??? ' If checked. strSQL = "DataList1.SELECT title FROM Songs WHERE Genre1 or Genre2 = ??? .text" End IfThank you!
If I understand your question correctly, you are looking for a way to change your SQL statement based on the status of certain visual controls. Although this is more of a programming issue, I'll see if I can help.
Then I would create a procedure--let's call it BuildSQL--that takes all of your visual controls into consideration when building the SQL. The procedure would initialize a string to hold the SELECT clause. Then, it would check each control to see if a WHERE condition should be added. Here's such a procedure in pseudo-code (it looks more complicated than it is):
SET SQLString = "SELECT Title FROM Songs" SET WhereClause = "" IF Country Checkbox IS CHECKED THEN IF WhereClause IS NOT EMPTY THEN WhereClause = WhereClause + " OR " END IF WhereClause = WhereClause + "(Genre1 = 'COUNTRY' OR " + "Genre2 = 'COUNTRY')" END IF IF Jazz Checkbox IS CHECKED THEN IF WhereClause IS NOT EMPTY THEN WhereClause = WhereClause + " OR " END IF WhereClause = WhereClause + "(Genre1 = 'JAZZ' OR " + "Genre2 = 'JAZZ')" END IF IF WhereClause IS NOT EMPTY THEN SQLString = SQLString + " WHERE " + WhereClause END IF
I would also recommend reviewing your design to verify your two Genre columns do not violate First Normal Form.
For More Information
- What do you think about this answer? E-mail the Edtior at email@example.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
This was first published in November 2001