Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

retrieving and inserting next number

Status
Not open for further replies.
chinedu

chinedu

Technical User
Mar 7, 2002
241
US
Hi everyone,
I am not sure if this is possible but please allow me to ask anyway.
There are 3 specifications I am required to implement, all on the same page.
I need to create a screen for the user to store records into an access database.
On this screen, the user needs to know the total number of records stored on the database so far; the useer needs to know the last item number stored, and last but not least, the user needs to have me display the next item number to be stored.
It should look like this:

The total number of items inserted into db: the total | Last Item Inserted into db: the Number


Then a dropdown will display the next number to be inserted.
If for instance the last number inserted is 527, then the next number to be inserted will be 528.
These numbers are NOT auto numbers. They are inserted manually.
I believe I have implemented the first 2, though it could be more efficient but that should be ok.
I am, however, having a hard time implementing the last spec, which is showing the next number to be stored.
I was wondering if anyone could lend me a help.
I am not even sure it is possible.
Thanks and here is the code I currently have.


<html>
<head>
<title>Storing Scanned Files</title>
<style>
body, input { font-family:verdana,arial; font-size:10pt; }
</style>

<TITLE>Accident Report</TITLE>

</head>
<body >
<%
dim safetyDB,sql,rs,rs1,rs2
set safetyDB = Server.CreateObject(&quot;ADODB.Connection&quot;)
safetyDB.Open &quot;dsn=itemStore&quot;
sql = &quot;SELECT count(Orgid) as Total FROM ORG &quot;
set rs1 = safetyDB.Execute(sql)
%>

<form method=&quot;POST&quot; enctype=&quot;multipart/form-data&quot; action=&quot;testEquipSpec.asp&quot;>
<table border=&quot;1&quot; bordercolor=black align=&quot;center&quot; bgcolor=&quot;ffffff&quot;>
<tr>
<td><font color=&quot;IndianRED&quot;><b>Total Item Spec stored in our database so far is:</b></font></td>
<td><input size=&quot;5&quot; type=&quot;text&quot; value=&quot;<%=rs1(&quot;total&quot;)%>&quot;></td>
<td><font color=&quot;steelBLUE&quot;><b>Last Inserted Number :</b></font></td>
<td>
<select name=&quot;LastInserted&quot; disabled>
<%
sql = &quot;SELECT ORG.Orgid, ORG.ORGCODE FROM ORG ORDER BY ORG.ORGCODE DESC&quot;
set rs = safetyDB.Execute(sql)
While not rs.EOF
%>
<OPTION value=&quot;<%=rs(0)%>&quot;><%=rs(1)%></OPTION>
<%
rs.MoveNext
wend

rs.close

set rs=nothing

%>

</select>
</td>
</tr>
</table>
<table>
<tr>
<td><p></td>
</tr>
</table>
<table>
<tr>
<td><p></td>
</tr>
</table>
<table>
<tr>
<td><p></td>
</tr>
</table>
<table>
<tr>
<td><p></td>
</tr>
</table>
<table>
<tr>
<td><p></td>
</tr>
</table>
<table>
<tr>
<td><p></td>
</tr>
</table>
<table border=&quot;1&quot; bordercolor=black align=&quot;center&quot; bgcolor=&quot;ffffff&quot;>
<tr>
<td><p></td></tr>
<tr>
<td><font color=&quot;steelBLUE&quot;><b>Next Number :</b></font></td>
<td>
<select name=&quot;ItemNumber&quot;>
<%
sql = &quot;SELECT ORG.Orgid, ORG.ORGCODE FROM ORG ORDER BY ORG.ORGCODE DESC&quot;
set rs2 = safetyDB.Execute(sql)
While not rs2.EOF
%>
<OPTION value=&quot;<%=rs2(0)%>&quot;><%=rs2(1)%></OPTION>
<%
rs2.MoveNext
wend

rs2.close

set rs2=nothing

%>

</select>
</td>
</tr>
<tr>
<td>Scanned File :</td><td>
<input type=&quot;file&quot; name=&quot;file&quot; size=&quot;40&quot;></td>
</tr>
<tr>
<td>&nbsp;</td><td><input type=&quot;submit&quot; value=&quot;Submit&quot;>
<input type=&quot;button&quot; value=&quot;Item Listings&quot; onclick=&quot;location='listItems.asp'&quot;>
</tr>
</table>

</form>
<%
safetyDB.close
set safetyDB = nothing
%>
</body>
</html>
 
Sort by date Sort by votes
hi chinedu,
this can be possible upto i understand ur problem.
for last inserted record u can query with max function :
SELECT max(ORG.ORGCODE) FROM ORG
and display the max+1 for next insertion.
same total can be shown by count functionin SQL
u need to more clarify ur prob.
 
Upvote 0 Downvote
The last Select as Bhoge told should be, if the Orgid is manually added
Code: 
    <select name=&quot;ItemNumber&quot;>
     <%
      sql = &quot;SELECT Max(ORG.Orgid)+1 as nextID FROM ORG&quot;
      set rs2 = safetyDB.Execute(sql)
          While not rs2.EOF
       %>
         <OPTION value=&quot;<%=rs2(0)%>&quot;><%=rs2(0)%></OPTION>
       <%
         rs2.MoveNext
        wend

        rs2.close

        set rs2=nothing

       %>

     </select>

________
George, M
 
Upvote 0 Downvote
hi bhoge and Shaddow:

sql = &quot;SELECT Max(ORG.Orgid)+1 as nextID FROM ORG&quot;

I tried above method even before I posted the thread but it did not work only because the orgCode that is being added is a varchar that looks like this: DW-527.
OrgID is an auto generated number.
I tried incrementing instead of the orgCode just like Shaddow's code but it gave an error saying converting to an integer.
 
Upvote 0 Downvote
Ok now since you explained howw the things are this should work
Code: 
  <select name=&quot;ItemNumber&quot;>
     <%
      sql = &quot;SELECT top 1 ORG.Orgid, ORG.ORGCODE FROM ORG ORDER BY ORG.Orgid DESC&quot;
      set rs2 = safetyDB.Execute(sql)
          While not rs2.EOF
           code=CLng(replace(rs2(1),&quot;DW-&quot;,&quot;&quot;))+1
       %>
         <OPTION value=&quot;DW-<%=code%>&quot;>DW-<%=code%></OPTION>
       <%
         rs2.MoveNext
        wend

        rs2.close

        set rs2=nothing

       %>

     </select>

________
George, M
 
Upvote 0 Downvote
hi Shaddow,
sorry, I still did not explain it well.
there are two IDs, one orgID is an auto number.
The database increments this automatically.
Then there is another ID which is manually entered. This id is called orgCode. This id starts with DW then dash(-) then a 3 digit (always 3 digit) number.
This number started at 100 and is now 527.
Next time we enter this number, we will enter DW-527, next time DW-528 and so on.
So the orgID and orgCode don't tally up to be same total.
Because of the steps involved in trying to make sure no dupes are allowed, and somethings they try to enter a dupe (although) the db is set up not to allow dupes but we decided to show them the number of ids entered so far, and most recent one, and the the nextid to be inserted.
So your code will insert orgID, not orgCode, unless I read it incorrectly.
 
Upvote 0 Downvote
I am sorry Shaddow, your code is great, so are you.
It works real well.
You rule; thank you!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
275
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
302
penguinspeaks
penguinspeaks
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
326
JScannell
JScannell
Swi
  • Locked
  • Question
JQUERY AJAX POST and how to receive data via Classic ASP
Replies
0
Views
449
Swi
Swi
GriffMG
  • Locked
  • Question
Classic ASP - Comm object sometimes doesn't return data and I have to requery
Replies
1
Views
179
GriffMG
GriffMG

Part and Inventory Search

Sponsor

Back
Top