Click here to Skip to main content
15,937,602 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
Hi,

So i'm trying to do a program(run on VB2008 Windows Application Forms) on how to make the program find missing numbers from a given range of numbers by the users where it is connected to an MSSQL Database. So basically the user type in the range number # from two textboxes like let's say "1" and "10". But on the MSSQL database, there are only numbers 1 and 2. So the program will read the database table's column and will generate an output of numbers 3, 4, 5, 6, 7, 8, 9, and 10 on a textbox. My question is it possible and what methods or ways can i do that?

An example of what i'm trying to do:
http://www.get-digital-help.com/2009/06/13/identify-missing-values-in-a-column-using-excel-formula/[^]
Posted
Updated 10-Dec-12 21:26pm
v5
Comments
Rickysay 11-Dec-12 20:23pm    
Hey AnkitGoel.com, why did u delete your solution, i was in the middle of testing your codings and suddenly u deleted it.

SQL
select Nos from tbl
where Nos >=1 and a<=10  --filter range of nos
and Nos not in (1,2)     --here add nos you have

--o/p
--3,4,5...10


Happy Coding!
:)
 
Share this answer
 
Here is a sample approach.

The 2 variables @LowerLimit and @UpperLimit should be supplied by users.

SQL
DECLARE @LowerLimit INT
DECLARE @UpperLimit INT
DECLARE @MissingNumberList VARCHAR(MAX)

SET @LowerLimit = 20
SET @UpperLimit = 25;

CREATE TABLE #Sample
(
    Number INT
)

INSERT INTO #Sample
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 16 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 21 UNION ALL
SELECT 22 UNION ALL
SELECT 24 UNION ALL
SELECT 25 UNION ALL
SELECT 29 UNION ALL
SELECT 30 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 10;




WITH CTE AS
(
    SELECT @LowerLimit AS N
    UNION ALL
    SELECT N + 1 AS N
    FROM CTE WHERE N < @UpperLimit
)

--SELECT N AS MissingNumbers FROM CTE
--LEFT JOIN #Sample S ON CTE.N = S.Number
--WHERE S.Number IS NULL

SELECT @MissingNumberList = ISNULL(@MissingNumberList,'') + CAST(N AS VARCHAR) + ', ' FROM CTE
LEFT JOIN #Sample S ON CTE.N = S.Number
WHERE S.Number IS NULL

IF @MissingNumberList <> ''
SET @MissingNumberList = SUBSTRING(@MissingNumberList, 0, LEN(@MissingNumberList)-1)
SELECT @MissingNumberList AS MissingNumberList


DROP TABLE #Sample


Let me know if you have any questions.
 
Share this answer
 
v2
Comments
Rickysay 11-Dec-12 2:59am    
oh, i'm sorry if i didnt mention im doing this on VB2008 Windows Forms Application, clearly there'll be textboxes. Are the above methods still be viable? How do i get the value on the textbox to be on the query?
__TR__ 11-Dec-12 3:12am    
The code I have posted works on MSSQL server. So it will not matter if its a windows forms application or web application. Your application needs to pass the values for @upperlimit and @lowerlimit variables and the query will return the missing numbers from your table.
EDIT: I have modified my answer to get the missing numbers as a csv in a single row.
You can refer this link to get an idea on how to pass values from your application to database
VB.NET passing parameters to Stored Procedure[^]
Rickysay 11-Dec-12 3:18am    
ok. But the thing is this form application i did happens to have the functionality to add, update and delete records from the database. How do i know the above method you gave only works for certain amount of records?
__TR__ 11-Dec-12 3:23am    
You can test it by changing the lower Limit and upper limit values and check if the values are displayed correctly.
__TR__ 11-Dec-12 5:01am    
An explanation on why the solution was downvoted would be appreciated.
SQL
create table test(numbers int)
 
insert into test values(1)
insert into test values(2)
insert into test values(3)
insert into test values(5)
insert into test values(6)
insert into test values(10)
insert into test values(11)

declare @num int
set @num=1
while @num <=(select max (numbers)from test)
begin
	if(@num = (select numbers from test where numbers=@num))
		begin
			print @num	
		end
	else
		begin 
			select @num 'Num'
		end
	set @num=@num +1
	
end
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900