September 9, 2005 at 9:11 am
I'm attempting to load several comboboxes (C# .NET) from an SQL 2000 data table (Reports). Now the problem is each combobox loads everything, even nulls that I don't need.
Simpy coding a 'Distinct' in the SQL will eliminate the nulls, however I have more than one column to be selected as 'Distinct'.. The following code is an example of what I need. It obviously does not work though.
Select distinct col_1, distinct col_2, distinct col_3 from table_1
September 9, 2005 at 9:25 am
I think you'll need to make a single statement per combo and select only the data you need.
September 9, 2005 at 9:49 am
I attempted to insert the data into another table like so, but the results are not desirable as shown below..
TRUNCATE TABLE ReportsTemp
insert into ReportsTemp (col_1) (select distinct col_1 from Reports where col_1 is not null)
insert into ReportsTemp (col_2) (select distinct col_2 from Reports where col_2 is not null)
insert into ReportsTemp (col_3) (select distinct col_3 from Reports where col_3 is not null)
Results:
col_1 col_2 col_3
data1 null null
data1 null null
data1 null null
null data2 null
null data2 null
null null data3
September 9, 2005 at 9:50 am
Or, if the combo boxes need to relate to each, you may need to re-write your query with either CASE statements or INNER JOINs on Subselects...
SELECT DISTINCT col_1, col_2, col_3
FROM table_1
WHERE 1 = CASE
WHEN col_1 IS NULL
THEN 0
ELSE 1
WHEN coL_2 IS NULL
THEN 0
ELSE 1 ...
END
OR
SELECT DISTINCT col_1, Two.col_1, ...
FROM table_1
INNER JOIN( SELECT DISTINCT col_2 FROM table_1 WHERE col_2 IS NOT NULL) Two
ON( table_1.[ID] = Two.[ID]) ..... [ not sure what field you will need to JOIN upon ]
WHERE col_1 IS NOT NULL
I wasn't born stupid - I had to study.
September 9, 2005 at 9:53 am
This was posted while I was writing my first response.
You will not want to INSERT col_2, col_3, etc. You will want to UPDATE your #TempTable based upon some key...
I wasn't born stupid - I had to study.
September 9, 2005 at 10:02 am
I like both responces. For now I would like to try the Update. I do have a Primary col_Pri, but not sure how I would make this work. Can you show syntax......
Results:
col_Pri col_1 col_2 col_3
1 data1 null null
2 data1 null null
3 data1 null null
4 null data2 null
5 null data2 null
6 null null data3
September 9, 2005 at 10:06 am
Can you tell us again why you need to do this?????????
September 9, 2005 at 10:17 am
"I'm attempting to load several comboboxes (C# .NET) from an SQL 2000 data table (Reports). Now the problem is each combobox loads everything, even nulls that I don't need. "
September 9, 2005 at 10:26 am
Why can't the combo boxes be loaded independently?
I wasn't born stupid - I had to study.
September 9, 2005 at 10:51 am
I have 12 comboboxes to load and I don't want that much C# code to do what I believe can be done with less code using SQL. Here is an SQL Update I've been working with. Two problems: 1. No primary key in truncated temptable_A, 2. I still an null in col_2 of temptable_A, but not near as many as before......
truncate temptable_A
update temptable_A
set col_2 = table_B.col_2
from temptable_A
Inner Join table_B
On temptable_A.PriKey
= table_B.PriKey
where table_B.col_2 is not null
September 9, 2005 at 11:14 am
Dude :: 1 select/combo. End of story. It's the third time we say it and the last in my case. Good luck with any other solution you have in mind.
September 9, 2005 at 11:38 am
Gotta go with Remi on this one, (although I don't use the word "Dude", unless maybe in reference to President Bush ). If there is no Relationship Key for your #TempTable, you will be chasing your back end around on this one to populate these combo boxes without NULLs.
If you really, really want to do a one fell swoop single select, try the other two methods I suggested...
I wasn't born stupid - I had to study.
September 9, 2005 at 11:40 am
How the heck would that help?? you still need to populate each combo by filtering that query somehow, that assuming that the cost of building that massive temp table is 0.
September 9, 2005 at 1:11 pm
You have one call for the dataset versus 12 calls.
I wasn't born stupid - I had to study.
September 9, 2005 at 1:16 pm
Still takes 12 queries to populate the dataset (at least one per col) + 1 to call that one, not to mention the inserts in the table...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy