locked
selectcommand and tiny int datatype RRS feed

  • Question

  • User-914320107 posted

    Hello.  Have a column in the sql table that is a tiny int.  This holds a 0 for No and 1 for Yes.  There is a combobox that has the info in it.  When I run the listview on the browser it shows a 0 or 1 in the field.  Is there a way in the SelectCommand to indicate what each is for and then show it on the browser?

    Thanks....John

    Friday, July 8, 2011 2:00 PM

Answers

  • User-843484705 posted

    you need to Use CASE in your query

    SELECT Col1, Col2,

     CASE TinyColumn
      WHEN 0 THEN 'No'
      WHEN 1 THEN 'Yes'
       END
    'TinyColumn'

    FROM TableName

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 8, 2011 2:37 PM

All replies

  • User-843484705 posted

    you need to Use CASE in your query

    SELECT Col1, Col2,

     CASE TinyColumn
      WHEN 0 THEN 'No'
      WHEN 1 THEN 'Yes'
       END
    'TinyColumn'

    FROM TableName

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 8, 2011 2:37 PM
  • User-914320107 posted

    Here is the whole SelectCommand line.  The tinyint field is IsISOCertified.  I suspect I didn't add it right.

    SelectCommand ="SELECT [ID], [Status], [Name], [ContactName], [IsISOCertified], CASE IsISOCertified WHEN 0 THEN 'NO' WHEN 1 THEN 'YES' END 'IsISOCertified' FROM [Suppliers] WHERE Status = 'Active'">

    Friday, July 8, 2011 2:44 PM
  • User-914320107 posted

    I got it.  Had forgotten to replace the first [IsISOCertified] with the CASE statement.  Thanks for the help on this....John

    SelectCommand ="SELECT [ID], [Status], [Name], [ContactName], CASE IsISOCertified WHEN 0 THEN 'NO' WHEN 1 THEN 'YES' END 'IsISOCertified' FROM [Suppliers] WHERE Status = 'Active'"

    Friday, July 8, 2011 2:48 PM
  • User-97721543 posted

    If you are just using it for 0 and 1 for yes and no, you can use datatype BIT instead of tinyint. Save some space. The case code for the yes and now would work the same.

    Wednesday, November 23, 2011 4:42 PM