SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to retrive last record from access db, using classic ASP

    hi,

    i have dev tape log system with different type of tape categories (dvd, cd, voice over,etc) my challenge is i want to auto generate tape ID number when ever i try to add new tape log.

    for example:

    the last Tape log ID for Voice over category is VO5533

    Now, how do i tell the application that i want to retrieve the last Tape log ID for the Voice over category?

    currently in my Add new Tape log page, i have to insert the Tape log ID manually, which is very hard as i need to search for the last record manually then enter the new record, is there anyway i can write a script to auto generate the tape ID based on the category selected and add + 1 to the tape log ID?

    im kind of new to ASP i would appreciate any sample codes or tuts you guys can provide me with

  2. #2
    SitePoint Zealot gidday's Avatar
    Join Date
    Jan 2007
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi ozalkl

    Here is a start.

    This is possible as long as you follow one simple rule:
    You need to have on the ADD NEW page a method to choose a category eg dropdown list, radio button, etc.

    From there the SAVE page will know what to do. Lets say you enter a Voice Log. I will call it 1 (numbers keep things easy).

    Get last record
    SELECT Top 1 Record_ID WHERE Tape_Category = " & Request("cat") & " ORDER BY Record_ID Desc "

    This will get the last record that you entered. Now since you are using alphanumeric as your Record_ID you will need to extract the number part and add 1 to it to get the next record (I'll leave this to you but consider Mid) and then put it back together.

    You then save all your data from the ADD NEW page with your new Record_ID.
    Joe
    "All that is necessary for the triumph of evil
    is that good men do nothing."

  3. #3
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here is my code for the Add New Tape Record page:

    Code ASP:
    <table  >
    <form id="level2_edit_ecProduct" name="level2_edit_ecProduct" action="admin_products.asp" method="post" >
     
    		<input type=hidden name="ModifiedBy" value="<% =ModifiedBy %>">
     
     
    	<td class='labelTD'></td>
     
    	<td class='labelTD'></td>
     
    		<input type=hidden name="dtInserted" value="<% =dtInserted %>">
     
     
    		<input type=hidden name="dtModified" value="<% =dtModified %>">
     
     
    		<input type=hidden name="InsertedBy" value="<% =InsertedBy %>">
     
     
    		<input type=hidden name="Id" value="<% =Id %>">
     
    <tr>
    	<td class='labelTD'><B>Log ID</B></td>
    	<td class='dataTD' >
    		<input type=text  name="ProdId" size="40" maxlength="100" value="<% =ProdId%>"></td>
    	<td bgcolor="#EFEFEF">enter a unique Number (no spaces) you may use the Tape Number (ie; SP 2801)</td>
    </tr>
    <tr>
    	<td class='labelTD'><B>Tape Category</B></td>
    	<td class='dataTD' >
     
    		<select name="CatId"  "0">
     
    		<%
    		 =get_options ("SELECT Cats3.CatId, Cats.Cat, Cats1.Cat, Cats2.Cat, Cats3.Cat FROM (((Cats As Cats3 left JOIN Cats As Cats2 ON Cats3.ParentId = Cats2.CatId) left JOIN Cats As Cats1 ON Cats2.ParentId = Cats1.CatId) LEFT JOIN Cats As Cats ON Cats1.ParentId = Cats.CatId) WHERE Cats3.CatTypeId = 6 AND Cats.ParentId is NULL order by (Cats.Cat " & cc & " Cats1.Cat " & cc & " Cats2.Cat " & cc & " Cats3.Cat)", CatId) 
    		%>
    		</select>
     
    	</td>
    	<td bgcolor="#EFEFEF">select a Tape Category for this Tape Log</td>
    </tr>
     
    <tr>
    	<td class='labelTD'><B>Tape Number</B></td>
    	<td class='dataTD' >
    		<input type=text  name="TapeNumber" size="40" maxlength="100" value="<% =TapeNumber%>">
    	</td>
    	<td bgcolor="#EFEFEF">enter the Tape Number in this field for this Tape Log (ie; SP 2801)</td>
    </tr>
    <tr>
      <td class='labelTD'>Project Name</td>
      <td class='dataTD' ><input type=text  name="ProjName" size="40" maxlength="100" value="<% =ProjName%>"></td>
      <td bgcolor="#EFEFEF">enter the Project Name in this field for this Tape Log</td>
    </tr>
    <tr>
    	<td class='labelTD'><B>Client</B></td>
    	<td class='dataTD' >
    		<input type=text  name="ProdName" size="40" maxlength="100" value="<% =ProdName%>">
    	</td>
    	<td bgcolor="#EFEFEF">enter the Client/Project Name in this field for this Tape Log</td>
    </tr>
     
    <tr>
    	<td class='labelTD'>Project Number</td>
    	<td class='dataTD' >
    		<input type=text  name="ProjNumber" size="40" maxlength="100" value="<% =ProjNumber%>">
    	</td>
    	<td bgcolor="#EFEFEF">enter the Project Number in this field for this Tape Log</td>
    </tr>
    <tr>
    	<td class='labelTD'><B>Description</B></td>
    	<td class='dataTD' >
     
    		<textarea  name="ProdDesc" rows='4' cols='100'><% =ProdDesc%></textarea>
     
    	</td>
    	<td bgcolor="#EFEFEF">enter a brief description in this field for this Tape Log</td>
    </tr>
    <tr>
      <td class='labelTD'>Tape FrameRate</td>
      <td class='dataTD' ><input type=text  name="TapeFr" size="40" maxlength="100" value="<% =TapeFr%>"></td>
      <td bgcolor="#EFEFEF">enter the FrameRate for this Tape Log</td>
    </tr>
    <tr>
    	<td class='labelTD'>Logs</td>
    	<td class='dataTD' >
     
    		<textarea wrap=off name="Logs" rows='12' cols='100'><% =to_html(logs) %></textarea>
     
    	</td>
    	<td bgcolor="#EFEFEF">enter Logs sessions for this Tape Log</td>
    </tr>
    <tr>
    	<td class='labelTD'>Additonal Notes</td>
    	<td class='dataTD' >
     
    		<textarea wrap=off name="LongDesc" rows='8' cols='100'><% =to_html(LongDesc) %></textarea>
     
    	</td>
    	<td bgcolor="#EFEFEF">enter any additional notes / info in this field for this Tape Log</td>
    </tr>
    <tr>
    	<td class='labelTD'>Date</td>
    	<td class='dataTD' >
    		<% if Display = "" then Display = false %>
    		<input type=hidden  <% if Display then %>checked<% end if %> name="Display" value="1"><input type=text name=dtStart value='<% =dtStart %>' size=18> &nbsp; 
    	</td>
    	<td bgcolor="#EFEFEF">enter added date in (mm/dd/yyyy) format in this field for this Tape Log</td>
    </tr>
     
    		<input type=hidden name="Layout" value="<% =Layout %>">
     
    <tr>
    	<td class=labelTD align=right>&nbsp;
    	<% if ProdId <> "" then %><input type=button class=cmd name=new_button value='NEW...' onClick="window.location = '<% =request.servervariables("script_name") %>?action=new_ecProduct&CatID=<% =CatID %>'"><% end if %>
    	</td>
    	<td class=dataTD>
    	<% if ProdId = "" then %><input type=submit class=cmd name=insert_button value='ADD TAPE'><% end if %>
    	<% if ProdId <> "" then %><input type=submit class=cmd name=update_button value='UPDATE'><% end if %>
    	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<% if ProdId <> "" then %><input type=submit class=cmd name=delete_button value='DELETE' onClick="document.level2_edit_ecProduct.action.value = 'delete_level2_edit_ecProduct'"><% end if %>
    	</td>
    	<td bgcolor="#EFEFEF">&nbsp;
    	</td>
    <input type=hidden name="action" value="<% if ProdId <> "" then %>update<% else %>insert<%end if %>_level2_edit_ecProduct"></tr>
     
    </form>
    </table>

    can you guide me from here what shall i do next? or alter in my code

  4. #4
    SitePoint Zealot gidday's Avatar
    Join Date
    Jan 2007
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi ozalkl

    Without seeing the routine get_options() I'll make a guess that it outputs a dropdown list from which you can select a category.

    Based on what I wrote above you should be able to use that user selection to choose the last record of that type. I hope this makes sense.
    Joe
    "All that is necessary for the triumph of evil
    is that good men do nothing."

  5. #5
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by gidday View Post
    Hi ozalkl

    Without seeing the routine get_options() I'll make a guess that it outputs a dropdown list from which you can select a category.

    Based on what I wrote above you should be able to use that user selection to choose the last record of that type. I hope this makes sense.
    thanks for your input, im just in a maze now lol, here is my SQL Select query as follow, where shall i insert the qry you explained earlier?

    sub db_select_level2_edit_ecProduct
    sql = "SELECT " & _
    "Id, " & _
    "ProdId, " & _
    "CatId, " & _
    "TapeNumber, " & _
    "LongDesc, " & _
    "AttrLabel, " & _
    "Display, " & _
    "Layout FROM ecProduct" & _
    " WHERE " & _
    "ecProduct.ProdId = " & to_sql(ProdId,"text") & ""

    "TapeNumber" is the one i need to retrieve the last record entered
    "ProdId," currently in the DB its AutoNum, how do i call it show as autoNum in the add Tape Log page

    i hope i did make some sense here

  6. #6
    SitePoint Zealot gidday's Avatar
    Join Date
    Jan 2007
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi ozalkl

    Let's start with baby steps. Can you show me the routine get_options(). Then we will get to the sql.

    Joe
    Joe
    "All that is necessary for the triumph of evil
    is that good men do nothing."

  7. #7
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by gidday View Post
    Hi ozalkl

    Let's start with baby steps. Can you show me the routine get_options(). Then we will get to the sql.

    Joe
    hi Joe, here is the query for the Select Categories:

    Code ASP:
    <select name="CatId"  "0">
     
    		<&#37;
    		 =get_options ("SELECT Cats3.CatId, Cats.Cat, Cats1.Cat, Cats2.Cat, Cats3.Cat FROM (((Cats As Cats3 left JOIN Cats As Cats2 ON Cats3.ParentId = Cats2.CatId) left JOIN Cats As Cats1 ON Cats2.ParentId = Cats1.CatId) LEFT JOIN Cats As Cats ON Cats1.ParentId = Cats.CatId) WHERE Cats3.CatTypeId = 6 AND Cats.ParentId is NULL order by (Cats.Cat " & cc & " Cats1.Cat " & cc & " Cats2.Cat " & cc & " Cats3.Cat)", CatId) 
    		%>
    		</select>
    i have also PM u the complete add tap log page code, for your reference

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2007
    Location
    Almere, The Netherlands
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ozalkl View Post
    hi,

    Now, how do i tell the application that i want to retrieve the last Tape log ID for the Voice over category?
    After inserting this record, use the same connection without closing it and run this:

    SELECT @@IDENTITY

    This will return the autogenrated ID of the record you just inserted

  9. #9
    SitePoint Zealot
    Join Date
    Jan 2007
    Location
    Almere, The Netherlands
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by gidday View Post
    Hi ozalkl

    Get last record
    SELECT Top 1 Record_ID WHERE Tape_Category = " & Request("cat") & " ORDER BY Record_ID Desc "

    This will get the last record that you entered.
    No, this will return the last record anybody has just entered. If 2 (or more) users will insert records at the same time, this method will sometimes return the record of someone else!

  10. #10
    SitePoint Zealot gidday's Avatar
    Join Date
    Jan 2007
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi verschha

    Please read the original question and then you will see that my answer is correct.
    Joe
    "All that is necessary for the triumph of evil
    is that good men do nothing."

  11. #11
    SitePoint Zealot gidday's Avatar
    Join Date
    Jan 2007
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi ozalkl

    Got the code. Here we go. You are passing the option variable named CatId to the page where the data is saved. This is the variable you need to get the last record as I detailed above.

    The process is thus.

    Fill out your add tape log page and process.
    Get CatId.
    Do sql using SELECT Top 1 Record_ID WHERE Tape_Category = " & Request("cat") & " ORDER BY Record_ID Desc ". Make sure you lock the database during this time so that you get the last record.

    Next sql string uses Recordset.ADDNEW and TapeNum + 1 (remember the bit from above where you need to extract the numeric part of TapeNum) plus all the rest of the add tape log variables.

    That should get you across the line. Feel free to PM again if you need.
    Joe
    "All that is necessary for the triumph of evil
    is that good men do nothing."

  12. #12
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by gidday View Post
    Hi ozalkl

    Got the code. Here we go. You are passing the option variable named CatId to the page where the data is saved. This is the variable you need to get the last record as I detailed above.

    The process is thus.

    Fill out your add tape log page and process.
    Get CatId.
    Do sql using SELECT Top 1 Record_ID WHERE Tape_Category = " & Request("cat") & " ORDER BY Record_ID Desc ". Make sure you lock the database during this time so that you get the last record.

    Next sql string uses Recordset.ADDNEW and TapeNum + 1 (remember the bit from above where you need to extract the numeric part of TapeNum) plus all the rest of the add tape log variables.

    That should get you across the line. Feel free to PM again if you need.
    thanks for your input, but i still didn't manage to get it to work

  13. #13
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mcramsey View Post
    Classic ASP compatible?
    interaction with databases is *always* ASP compatible

    this whole thread is an SQL problem - not an ASP problem

    the person should be :
    --using a better database design and
    --using stored queries or stored procedures to:
    ----look up the last record
    ----create the next incremental value
    ----add the new record
    ----update the record

    trying to work with a bad design is always going to be problematic.

    In any case, this is a question for the database forum

  14. #14
    SitePoint Zealot gidday's Avatar
    Join Date
    Jan 2007
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi ozalkl

    Are you getting an error?
    Joe
    "All that is necessary for the triumph of evil
    is that good men do nothing."

  15. #15
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by gidday View Post
    Hi ozalkl

    Are you getting an error?
    yes still getting the error, will you be able to insert the code into the main asp code for the tape log page, perhaps im missing something that preventing the code to work

  16. #16
    SitePoint Zealot gidday's Avatar
    Join Date
    Jan 2007
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi ozalkl

    What is the error? What exactly is your browser outputting when you run the code?
    Joe
    "All that is necessary for the triumph of evil
    is that good men do nothing."


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •