create table #MissingIDS 
(
 Id char(4) PRIMARY KEY
 ) 
declare @maxid int, @currentid int 
 
SELECT @maxid=max(cast(Id as int)) FROM dbo.UserConfig 
 
select @currentid=1 
 
while @currentid<@maxid
begin 
 if not exists(select Id from dbo.UserConfig where 
   Id=right('0000'+cast(@currentid as varchar(4)),4)) 
   insert #MissingIDS 
   select right('0000'+cast(@currentid as varchar(4)),4) 
   select @currentid=@currentid+1 
 end 
select * from #MissingIDS 
drop table #MissingIDS

Dim connStr As String
Dim strSqlServer As String = ""
Dim strDatabase As String = ""
Dim arlIds As New ArrayList
 
'SQL connection string bauen
connStr = "Server=" & strSqlServer & ";Database=" & strDatabase & ";Connect" & _
  "Timeout=30;Trusted_Connection=True;"
 
Dim strSQL As String = "create table #MissingIDS (Id char(4) PRIMARY KEY)" & _
  "declare @maxid int, @currentid int SELECT @maxid=max(cast(Id as int)) FROM" & _
  "dbo.UserConfig select @currentid=1 while @currentid<@maxid begin if not" & _
  "exists(select Id from dbo.UserConfig where Id=right('0000'+cast(@currentid" & _
  "as varchar(4)),4)) insert #MissingIDS select right('0000'+cast(@currentid as" & _
  "varchar(4)),4) select @currentid=@currentid+1 end select * from #MissingIDS" & _
  "drop table #MissingIDS"
 
Dim con As New SqlClient.SqlConnection(connStr)
Dim cmd As New SqlClient.SqlCommand(strSQL, con)
Dim reader As SqlClient.SqlDataReader
 
con.Open()
 
'execute the command
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
 
'datensätze in ein array speichern zur weiterverarbeitung
Dim check As Boolean = False 'zur kontrolle, falls keine sqldaten gefunden 
' werden
While reader.Read()
  check = True
  Dim objDatensatz As New ClassIds
  objDatensatz.Id = reader!Id.ToString.Trim
  arlIds.Add(objDatensatz)
End While
For i As Long = 0 To arlIds.Count - 1
  Console.Write(arlIds(i).id & " ")
Next
 
reader.Close()
con.Close()

Klaus