Click here to Skip to main content
15,948,338 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Comments
Rickysay 21-Dec-12 20:40pm    
Anyone?

1 solution

You can do this entirely in the DB, with a temporary table.

SQL
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
 
Share this answer
 
Comments
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?

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