Q

Changing SQL based on the status of visual controls

This Content Component encountered an error

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 If
Thank 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 tdichiara@techtarget.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

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close