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?