Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to 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 10-Dec-12 20:14pm
Edited 10-Dec-12 21:26pm
v5
Comments
Rickysay at 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: bad
good
Please Sign up or sign in to vote.

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!
Smile | :)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
v2
Comments
Rickysay at 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__ at 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 at 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__ at 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__ at 11-Dec-12 5:01am
   
An explanation on why the solution was downvoted would be appreciated.
Rickysay at 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 at 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__ at 14-Dec-12 3:12am
   
You need to take the data in a datatable and bind it to the list box.
Refer these links
Bind Listview from dataset in vb.net[^]
How to loop through Dataset and Add items to listbox control[^]
Rickysay at 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__ at 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: bad
good
Please Sign up or sign in to vote.

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

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



Advertise | Privacy | Mobile
Web03 | 2.8.141015.1 | Last Updated 11 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100