Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
So i have a problem with my codings and was wondering if anyone can help me.
 
    Function FindingMissingNumber() As String
 
        Dim intX As Integer = Nothing
        Dim intY As Integer = Nothing
        Dim strSting As String = Nothing
        Dim strSqlQUery As String = Nothing
        Dim cmdSqlCommand As Data.SqlClient.SqlCommand = Nothing
        Dim rdrDataReader As Data.SqlClient.SqlDataReader = Nothing
 
        '------------------------------------------------------------------------------------------------------------------------
        '-> Process
        '------------------------------------------------------------------------------------------------------------------------
        strSqlQUery = "Select ExReportPolicyNo From DBReport Order by ExReportPolicyNo"
        Dim msSqlConnection As New Data.SqlClient.SqlConnection()
        'NOTE - You may need to CHECK your connection string!!! in the line below
        msSqlConnection.ConnectionString = "Data Source=SISBSQL\SISBSQL;Initial Catalog=ExceptionReport;User ID=sa;Password=123;"
        cmdSqlCommand = New Data.SqlClient.SqlCommand(strSqlQUery, msSqlConnection)
        If cmdSqlCommand.Connection.State = Data.ConnectionState.Closed Then cmdSqlCommand.Connection.Open()
        rdrDataReader = cmdSqlCommand.ExecuteReader()
        If rdrDataReader.HasRows Then
            Do While rdrDataReader.Read()
                intX = txtRangeLeft.Text
                intY = txtRangeRight.Text
                'intY = rdrDataReader.GetValue(rdrDataReader.GetOrdinal("ExReportPolicyNo"))

                Do While intX <> intY
                    intX = intX + 1
                    If intX <> intY Then
                        strSting = strSting & intX & ", "    'if it is not, then record the non sequential number into the string
                    Else
                        Exit Do
                    End If
                Loop
            Loop
        End If
        If cmdSqlCommand.Connection.State = Data.ConnectionState.Open Then cmdSqlCommand.Connection.Close()
        'return string
        Return strSting
        'tidy up
        intX = Nothing
        intY = Nothing
        strSting = Nothing
        strSqlQUery = Nothing
        cmdSqlCommand = Nothing
        rdrDataReader = Nothing
 
    End Function
Basically i'm using VB.NET and MSSQL to make a program on finding missing numbers in between a given range set by the user. The program will read from the table and give the output on a textbox. And the above codes are so far what i can come up with. But the problem is, i get wrong output and not what i want. Here's an Image[^] of the output.
 
As you can see the program loops it multiple times, and give out the wrong output. The output should read only "286118, 286120, 286121". Question is where did i went wrong?
Posted 19-Dec-12 15:25pm
Comments
Rickysay at 21-Dec-12 20:40pm
   
Anyone?

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You can do this entirely in the DB, with a temporary table.
 
create table #temp (id int)
insert into #temp values
1,2,6,41,63
 
-- or you can use a loop if the values are a sequence

SELECT t.id
FROM #temp t
LEFT OUTER JOIN DBReport dr on t.id = dr.ExReportPolicyNo 
WHERE dr.id IS NULL
 
drop table #temp
  Permalink  
Comments
Rickysay at 19-Dec-12 20:37pm
   
The thing is i don't want to. I want to generate the output straight to a textbox. But thanks anyways.
Christian Graus at 19-Dec-12 20:39pm
   
Why do you want to do it the hard way ? Your code can't work. You need a list of all the values, then you need to compare each value you expect to find, to that whole list. You can use comparison methods that exist without writing nested loops.
Rickysay at 19-Dec-12 20:47pm
   
Comparison methods? How does it work? Can it be used to read from the database from MSSQL?
Christian Graus at 19-Dec-12 20:49pm
   
The list and array classes have methods you can use to do the iterating for you. You would still need to read your data from the DB. You know, the thing that's optimised to work out this sort of data, except you don't want to use the best tool for the job.
Rickysay at 19-Dec-12 21:05pm
   
Let's say if i want to use the SQL query your way, but not from the MSSQL but from VB.NET windows apps forms. how do i implement it onto my codings above? Is it even possible?
Christian Graus at 19-Dec-12 21:10pm
   
Again, why won't you do it the right way ? I don't know if you can create a temp table in SQL run from VB.NET. I've never tried.

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



Advertise | Privacy | Mobile
Web01 | 2.8.150302.1 | Last Updated 19 Dec 2012
Copyright © CodeProject, 1999-2015
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