-
Feb 13th, 2015, 09:53 AM
#1
Thread Starter
Frenzied Member
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
-
Feb 13th, 2015, 10:14 AM
#2
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Feb 13th, 2015, 10:25 AM
#3
Thread Starter
Frenzied Member
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)
-
Feb 13th, 2015, 10:39 AM
#4
Re: Add -- Please Select --
-
Feb 13th, 2015, 11:19 AM
#5
Re: Add -- Please Select --
I knew that this thread seemed familiar...
-
Feb 16th, 2015, 06:19 AM
#6
Thread Starter
Frenzied Member
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
-
Feb 16th, 2015, 06:28 AM
#7
Thread Starter
Frenzied Member
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
-
Feb 16th, 2015, 06:42 AM
#8
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
-
Feb 16th, 2015, 07:59 AM
#9
Thread Starter
Frenzied Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|