Results 1 to 9 of 9

Thread: Add -- Please Select --

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2009
    Posts
    1,058

    Add -- Please Select --

    Hi,

    I have 2 combo boxes on my form (cmbPracID & cmbPatID) - All with bounded data.

    When you select a value in cmbPracID it populates cmbPatID with all records that match that PracID (this bit works fine)

    Now, on my cmbPatID it contains all the values as expected and the select statement I have is shown below

    Code:
    SELECT        GOLDPatID, PracNo
    FROM            TblPracPatDetails
    WHERE        (CONVERT(nvarchar(50), PracNo) = @cmbPracID)
    ORDER BY CAST(GOLDPatID AS INT)
    E.g.,

    Code:
    cmbPatID (dropdownlist)
    12
    524
    6262
    Now, I want to have an extra line to state -- Please Select --


    So it will be;

    Code:
    cmbPatID (dropdownlist)
    -- Please Select -- 
    12
    524
    6262
    Thank you in advance for your help

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,466

    Re: Add -- Please Select --

    I'd suggest inserting -- Please Select -- as the first item in your datatable, and it will display as you want
    You will need to change your column datatype to string if it's a numeric column

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2009
    Posts
    1,058

    Re: Add -- Please Select --

    How can I do that ?

    My Datatable is having the following SQL statement;

    Code:
    SELECT        GOLDPatID, PracNo
    FROM            TblPracPatDetails
    WHERE        (CONVERT(nvarchar(50), PracNo) = @cmbPracID)
    ORDER BY CAST(GOLDPatID AS INT)

  4. #4
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Add -- Please Select --


  5. #5
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Add -- Please Select --

    I knew that this thread seemed familiar...
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  6. #6

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2009
    Posts
    1,058

    Re: Add -- Please Select --

    Changed the code to;

    Code:
    SELECT        '--Please Select--' AS GoldPatID, 0 AS OrderCol
    UNION ALL
    SELECT        GoldPatID, PracNo
    FROM            TblPracPatDetails
    WHERE (CONVERT(nvarchar(50), PracNo) = @cmbPracID)
    ORDER BY OrderCol, CAST(GOLDPatID AS INT)

    I receive the error - Invalid column name 'GOLDPatID'. Order by items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator

    Please help ..

    Thanks

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2009
    Posts
    1,058

    Re: Add -- Please Select --

    This worked,

    Code:
    SELECT        '--Please Select--' AS GoldPatID, 0 AS OrderCol
    UNION ALL
    SELECT        GOLDPatID, PracNo
    FROM            TblPracPatDetails
    WHERE        (CONVERT(nvarchar(50), PracNo) = @cmbPracID)
    ORDER BY OrderCol
    However, it orders like ;

    Code:
    GoldPatId             OrderCol
    -- Please Select --           0
    1162335                        21844
    666                              21844
    9                                  21844

    I want the result to be;

    Code:
    GoldPatId                   OrderCol
    -- Please Select --                 0
    9                                        21844
    666                                     21844
    1162335                              21844
    Thanks

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Add -- Please Select --

    OK, so you do need to order by that column and you require numerical ordering. It's not that difficult if the column never goes negative.

    You need to select any column you want to order by into an ordering column before you union it. Then you select 0 into that column for the artificial "---please select---" row you're inserting. That zero is what forces the artificial row to the start of the ordering sequence. So in your case you'll need up with something like this (typed straight in so watch for syntax errors):-
    Code:
    SELECT        '--Please Select--' AS GoldPatID, 0 AS OrderCol1, 0 as OrderCol2
    UNION ALL
    SELECT        GoldPatID, PracNo, GoldPatID
    FROM            TblPracPatDetails
    WHERE        (CONVERT(nvarchar(50), PracNo) = @cmbPracID)
    ORDER BY OrderCol1, OrderCol2
    If your GoldPatID or PracNo can ever be negative then things get a little more complicated. You either need to insert an arbitrarily low number in place of the zeros (if you can guarantee that your GoldPatID and PracNo don't go below that number) or you need to use the RowNum function in a sub query and order by the result of that. I suggest you don't bother exploring this route unless you absolutely need to.



    Edit> I'm returning the OrderCol column to the output here for simplicity. If you want to eliminate them you simply wrap the whole query up into a sub-query or cte and then select only the columns you need from that.
    Last edited by FunkyDexter; Feb 16th, 2015 at 06:46 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2009
    Posts
    1,058

    Re: Add -- Please Select --

    Thank you so much - It worked!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width