Click here to Skip to main content
12,828,367 members (45,313 online)
Rate this:
Please Sign up or sign in to vote.
See more: VB SQL-Server SQL-Server-2008 , +
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
                        Exit Do
                    End If
        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
Rickysay 21-Dec-12 20:40pm

1 solution

Rate this: bad
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

-- or you can use a loop if the values are a sequence

FROM #temp t
LEFT OUTER JOIN DBReport dr on = dr.ExReportPolicyNo 

drop table #temp
Rickysay 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 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 19-Dec-12 20:47pm
Comparison methods? How does it work? Can it be used to read from the database from MSSQL?
Christian Graus 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 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 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)

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170326.1 | Last Updated 19 Dec 2012
Copyright © CodeProject, 1999-2017
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