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
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
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)