I am new to SQL and have been trying to get a statement correct for a VB6 database program I'm writing for myself....
Here is the problem: I am composing a SQL string for a checkbox component that, when selected, affects what data is returned in a datalist component. I don't know how to reference the datalist component in the checkbox SQL statement. For example, if I have a database table called "genre" that has two fields (ID(key)and genre)and another table called "songs" that has four fields (genre1, genre2 [which both will contain the IDs from the "genre" table], title, and artist. I want to have multiple checkboxes for genre types (country, jazz, pop, classical)so I can select one or more genres and return the applicable titles (from the "songs" table) in the datalist component. How would the SQL statement be worded? Here is what I have tried, although it's not right:
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 firstname.lastname@example.org 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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.