Click here to Skip to main content
12,296,307 members (59,643 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
                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 14:25pm
Comments
Rickysay 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 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
Web01 | 2.8.160525.2 | Last Updated 19 Dec 2012
Copyright © CodeProject, 1999-2016
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