missing number

L

le0

Hello guys,

Im really having a hard time doing this, I have a record set with the ItemNo
field with the data type as Text. In the record that I have, I want to find
the missing number in the series

for example:
1
3
4.1
4.2
5
missing number is 2.

How can I display the missing number in the browser or give me a return
value that notifies me that there is a missing number in the series. Is
there any function for that? Or how can i do that using a loop?

btw im using ASP Classic and MS Access Database

Leo :(
 
M

McKirahan

le0 said:
Hello guys,

Im really having a hard time doing this, I have a record set with the ItemNo
field with the data type as Text. In the record that I have, I want to find
the missing number in the series

for example:
1
3
4.1
4.2
5
missing number is 2.

How can I display the missing number in the browser or give me a return
value that notifies me that there is a missing number in the series. Is
there any function for that? Or how can i do that using a loop?

btw im using ASP Classic and MS Access Database
Click to expand...

How do you define "missing"? Wouldn't 4 also be missing?
Also, as a text field, might it contain non-numeric values?

One approach might be to read all the ItemNo values
(SELECT ItemNo FROM {your_table} ORDER BY ItemNo)
determine the lowest and the highest numbers as integers
then loop throught the recordset to identify what's missing.
 
M

McKirahan

McKirahan said:
How do you define "missing"? Wouldn't 4 also be missing?
Also, as a text field, might it contain non-numeric values?
Click to expand...

Also, will the first and lowest number always be 1 and will it exist?

Here's one solution. Watch for word-wrap.

<% @Language="VBScript" %>
<% Option Explicit
'*
'* Declare Constants
'*
Const cASP = "ItemNo.asp"
Const cMDB = "ItemNo.mdb"
Const cDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
'*
'* Declare Variables
'*
Dim intITM
Dim strITM
strITM = "Numbers present: "
Dim intNUM
intNUM = 0
Dim strNUM
strNUM = "Numbers missing: "
Dim strSQL
strSQL = "SELECT ItemNo From ItemNo ORDER BY ItemNo"
'*
'* Declare Objects
'*
Dim objADO
Set objADO = Server.CreateObject("ADODB.Connection")
objADO.Open cDSN & Server.MapPath(cMDB)
Dim objRST
Set objRST = objADO.Execute(strSQL)
'*
'* Read Recordset
'*
Do While Not objRST.EOF
intNUM = intNUM + 1
strITM = strITM & "<br>" & objRST("ItemNo").Value
intITM = Int(objRST("ItemNo").Value)
If intNUM < intITM Then
For intNUM = intNUM To (intITM-1)
strNUM = strNUM & "<br>" & intNUM
Next
End If
intNUM = intITM
objRST.MoveNext
Loop
'*
'* Destroy Objects
'*
Set objRST = Nothing
objADO.Close
Set objADO = Nothing
%>
<html>
<head>
<title><%=cASP%></title>
</head>
<body>
<%=strITM%>
<hr>
<%=strNUM%>
</body>
</html>
 
L

le0

McKirahan said:
Also, will the first and lowest number always be 1 and will it exist?

Here's one solution. Watch for word-wrap.

<% @Language="VBScript" %>
<% Option Explicit
'*
'* Declare Constants
'*
Const cASP = "ItemNo.asp"
Const cMDB = "ItemNo.mdb"
Const cDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
'*
'* Declare Variables
'*
Dim intITM
Dim strITM
strITM = "Numbers present: "
Dim intNUM
intNUM = 0
Dim strNUM
strNUM = "Numbers missing: "
Dim strSQL
strSQL = "SELECT ItemNo From ItemNo ORDER BY ItemNo"
'*
'* Declare Objects
'*
Dim objADO
Set objADO = Server.CreateObject("ADODB.Connection")
objADO.Open cDSN & Server.MapPath(cMDB)
Dim objRST
Set objRST = objADO.Execute(strSQL)
'*
'* Read Recordset
'*
Do While Not objRST.EOF
intNUM = intNUM + 1
strITM = strITM & "<br>" & objRST("ItemNo").Value
intITM = Int(objRST("ItemNo").Value)
If intNUM < intITM Then 'COMMENT: your condition here is always
false
For intNUM = intNUM To (intITM-1)
strNUM = strNUM & "<br>" & intNUM
Next
End If
intNUM = intITM
objRST.MoveNext
Loop
'*
'* Destroy Objects
'*
Set objRST = Nothing
objADO.Close
Set objADO = Nothing
%>
<html>
<head>
<title><%=cASP%></title>
</head>
<body>
<%=strITM%>
<hr>
<%=strNUM%>
</body>
</html>
Click to expand...
 
M

McKirahan

[snip]

It appears that your last post was incomplete;
that is, you didn't include any comments.
 
M

McKirahan

McKirahan said:
[snip]

It appears that your last post was incomplete;
that is, you didn't include any comments.
Click to expand...

On re-re-re-reading it I finally found your buried comment:
'COMMENT: your condition here is always false

Did you try the code? It works for me.
Do While Not objRST.EOF
intNUM = intNUM + 1
strITM = strITM & "<br>" & objRST("ItemNo").Value
intITM = Int(objRST("ItemNo").Value)
If intNUM < intITM Then
For intNUM = intNUM To (intITM-1)
strNUM = strNUM & "<br>" & intNUM
Next
End If
intNUM = intITM
objRST.MoveNext
Loop
Click to expand...

If 1 is found then intNUM=1 and intITM=1.
If 3 is the next number then intNUM=2 and intITM=3.
Thus, 2 < 3 and 2 will be identified as missing.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

The following palindrome number 2
How to get day (number), month (number) and year from a date using month's french name? 3
Drawing missing in bitmap in a pure C win32 program 4
I would like to use awk to calculate the total number of records processed 1
New to VHDL... Trying to convert a 2-bytes number into an decimal 0
Is this right way to convert data attributes values to number in javascipt? Need to get valid numeric value or 0 2
Pycharm says "simplify chained comparison". I dont know how. 11
How to fix ssl.SSLError: [SSL: WRONG_VERSION_NUMBER] wrong version number (_ssl.c:2570) 0

Members online

Total: 29 (members: 1, guests: 28)
Robots: 360

Forum statistics

Threads
473,769
Messages
2,569,582
Members
45,065
Latest member
OrderGreenAcreCBD

Latest Threads

Top