12,634,101 members (26,668 online)
Rate this:
See more: , +
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 10-Dec-12 21:14pm
Updated 10-Dec-12 22:26pm
v5
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.

Rate this:

## Solution 2

```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!
:)
Rate this:

## Solution 3

Here is a sample approach.

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

```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.
v2
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.
Rickysay 11-Dec-12 6:39am

I didn't downvote, must be some trolls. Anyways, i'll get back to you later. I'm off to bed.
Rickysay 11-Dec-12 20:35pm

I'm still having problems as to how i am able to push this into VB Windows form application, can you help?
Here is an example of my VB codings atm:
http://pastebin.com/6D5CJkW7

And here is how i planned to generate the output:
http://i1.minus.com/jce6CnnKVc7cJ.jpg
__TR__ 14-Dec-12 3:12am

You need to take the data in a datatable and bind it to the list box.
Bind Listview from dataset in vb.net[^]
How to loop through Dataset and Add items to listbox control[^]
Rickysay 14-Dec-12 21:58pm

the link you gave only specified how to add items onto listview from database. It doesn't show user how to add values in textboxes and user click buttons to read from certain records of the database and generate the list of values in the listbox.
__TR__ 15-Dec-12 8:46am

I am not sure if i understood your comment. The user enters the values in the textbox and clicks on submit button. You need to use button click event handler[^] to retrieve the numbers missing in the given range and populate it in the list box.
Rate this:

## Solution 4

```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```
v3

Top Experts
Last 24hrsThis month
 OriginalGriff 320 Peter Leow 180 CHill60 105 CPallini 100 Jochen Arndt 80
 OriginalGriff 2,324 ppolymorphe 1,266 Peter Leow 1,004 John Simmons / outlaw programmer 725 CPallini 681