Solved

ASP insert, delete update in same page but be able to move through records

Posted on 2004-09-14
5
304 Views
Last Modified: 2006-11-17
Hi....

I know how to build a form that has say three buttons on it

Update, Insert, Delete.

For say new record.

For example in  VB you can click on the data control and get to the next record within the same page....

Is there a way to do that part in ASP.

Say this is my page

  Next Record

        Category
        Part

                      Insert            Update            Delete


I can't figure out to control the page with one record....and the next record goes to next page and fills in the data on the page





0
Comment
Question by:TRACEYMARY
  • 3
  • 2
5 Comments
 
LVL 10

Accepted Solution

by:
ADSaunders earned 500 total points
ID: 12055526
Hi TRACEYMARY,
The basic principle is to set your ADO pagesize to 1 (one), and pass the next (previous) page (record) as part of your querystring.
Here is some code from a (working) FAQ editor I wrote:
(pick the bones out of this)
<html>

<head>
<meta http-equiv="Content-Language" content="en-gb">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>FAQ Manager</title>
<link rel="stylesheet" type="text/css" href="../Site.css">
<script language="javascript">

function yell(msg){
    alert (msg);
}

function CanI(msg){
    return(confirm(msg));
}                                        

</script>
<script language="vbscript">

function CheckID()
    if document.FManager.TID.value="0" then yell("Please select an FAQ in which to add a question")
end function

function getFAQ(FAQ)
    location = "FAQ_Manager.asp?FAQ=" & FAQ
end function

function GetPage(FAQ, page)
    location = "FAQ_Manager.asp?FAQ=" & FAQ & "&page=" & page
end function

sub KickOut()
    location = "unauthorised.htm"
end sub

</script>
</head>

<body class="iPage">
<br/>
<br/>
<br/>
<br/>
<!--#INCLUDE FILE="FAQ_Config.asp"-->
<%if  iFAQ_Maint > 0 then
        if iFAQ_Maint = 1 then%>
<h2 align=center><font color="red">You are not authorised to maintain this FAQ</font></h2>
        <% else %>
<h2 align=center><font color="red">You are not authorised to maintain FAQs</font></h2>
        <% end if %>
<% else

dim errmsg, rsconn, rsselect, strSQL, msg, modestr, sQuestion, sAnswer, strFAQlist, RecCount, RecPage, iCount
dim ThisFAQ, ThisPage, ThisRec, rsSort, strSort
sQuestion = ""
sAnswer = ""
if request.form("BSubmit") <>  "" then
       'Submit can't seem to handle spaces, see hidden input in form below. decode '|!|' string as space.
       ThisFAQ = replace(request.form ("FAQ"),"|!|"," ")
       ThisPage = Request.form("page")
       ThisRec = request.form("FNum")
else
       ThisFAQ = request.querystring ("FAQ")
       ThisPage = Request.querystring("page")
end if

if request.querystring("action") = "new" then
    modestr = "Create"
else
    modestr = "Amend"
end if
if request.form("BSubmit") <> "" then
    errmsg = ""      
    if request.form("TID") = "0" then
        errmsg = errmsg & "Please select a FAQ in which to add a question<br/>"
    end if
    if request.form("TQuest") = "" or request.form("TAns") = "" then
        errmsg = errmsg & "You must supply both question and answer<br/>"
    end if
    if errmsg <> "" and request.form("BSubmit") <> "Delete" then
%>
        <table align="center" border="0" cellspacing="0" cellpadding="0">
            <tr><td>____________________________________________________________________</td></tr>
            <tr><td> The question has not been created because there are problem(s) with the form.</td></tr>
            <tr><td> Please correct the problem(s) and re-submit the form.</td></tr>
            <tr><td>____________________________________________________________________ </td></tr>
            <tr><td>The following field(s) need to be corrected: -<br/><br/></td></tr>                  
            <tr><td><font color="red"><%= errmsg%></font></td></tr>
            <tr><td><br/></td></tr>
            <tr><td>Please click the 'back' button on your browser and correct your data</td></tr>
    </table>
<%
    else
        Set rsconn = Server.CreateObject("ADODB.Recordset")
        rsconn.CursorLocation = adUseClient    
        rsconn.CursorType = adOpenStatic
        rsconn.LockType = adLockOptimistic
        if request.form("BSubmit") = "Create" then
            strSQL = "SELECT * FROM faq_data WHERE FAQ_ID = '" & request.form("TID") & "' ;"
        else
            strSQL = "SELECT * FROM faq_data WHERE FAQNum = " & request.form("FNum") & ";"
        end if
        rsconn.Open strSQL, sFAQCon
        RecCount = rsconn.RecordCount    
        if request.form("BSubmit") = "Create" then  
            ThisFAQ = Request.Form("TID")
            ThisPage = cstr(RecCount + 1)
            if cint(request.form("TORD")) <> cint(ThisPage) then
                ' Specified ordinal, so shift records down by one ordinal point
                Set rsSort = Server.CreateObject("ADODB.Recordset")
                rsSort.CursorLocation = adUseClient
                rsSort.CursorType = adOpenStatic
                rsSort.LockType = adLockOptimistic
                strSort = "SELECT * FROM faq_data WHERE FAQ_ID = '" & request.form("TID")
                strSort = strSort & "' AND FAQ_Sort >= " & cstr(request.form("TORD")) & " ;"
                 
                rsSort.open strSort, sFAQCon
                Do                                            
                    rsSort("FAQ_Sort") = cint(rsSort("FAQ_Sort")) + 1
                    rsSort.Update
                    rsSort.MoveNext
                    if rsSort.EOF then exit Do
                Loop
                rsSort.Close
                set rsSort = Nothing
                ThisPage = cint(request.form("TORD"))
            end if
            rsconn.AddNew
            rsconn.Fields("FAQ_ID") = ThisFaq
               rsconn.Fields("FAQ_Sort") = cint(request.form("TORD"))
        end if
        if request.form("BSubmit") = "Delete" then
               If NOT rsconn.EOF Then rsconn.Delete
        else
            rsconn.Fields("FAQ_QUEST") = request.form("TQuest")
            rsconn.Fields("FAQ_ANS") = request.form("TAns")
            rsconn.update
        end if
        rsconn.close
        set rsconn = nothing
        response.redirect("FAQ_Manager.asp?FAQ=" & ThisFAQ & "&page=" & ThisPage)
   end if   'Form Handling
else
    if request.form("FAQ") <> "" then
        ThisFAQ = request.form ("FAQ")
        ThisPage = Request.form("page")
        ThisRec = request.form("FNum")
    else
        ThisFAQ = request.querystring("FAQ")
        ThisPage = Request.querystring("page")
    end if
    if ThisFAQ <> "" then
        Set rsconn = Server.CreateObject("ADODB.Recordset")
        rsconn.CursorLocation = adUseClient    
        rsconn.CursorType = adOpenStatic
        strSQL = "SELECT * FROM faq_data WHERE FAQ_ID = '" & ThisFAQ & "' ORDER BY FAQ_Sort;"
        rsconn.Open strSQL, sFAQCon    
        RecCount = rsconn.RecordCount  
        if RecCount = 0 then modestr = "Create"  
        rsconn.PageSize = 1
        if not rsconn.EOF then
               if ThisPage = "" then
                   rsconn.AbsolutePage = 1
               else
                   if int(ThisPage) > RecCount then
                          rsconn.AbsolutePage = RecCount
                   else
                       rsconn.AbsolutePage = int(ThisPage)
                   end if
            end if    
            ThisFAQ = rsconn("FAQ_ID")
            ThisPage = rsconn.AbsolutePage
            ThisRec = rsconn("FAQNum")
            if modestr <> "Create" then
                sQuestion = rsconn("FAQ_QUEST")      
                sAnswer = rsconn("FAQ_ANS")
            end if
        end if    
    end if 'FAQ
%>
<table style="position: absolute; right: 0; top: 4.5em; border: 0;">
<tr>                                                                            
<% if modestr <> "Create" then %>
<td class=TableButton><a href="FAQ_Manager.asp?FAQ=<%=ThisFAQ%>&action=new"><b>New Question</b></a></td>
<td class=TableButton><a href="FAQ_Editor.asp?FAQ=<%=ThisFAQ%>"><b>FAQ Editor</b></a></td>
<td class=TableButton><a href="ReOrder_FAQ.asp?FAQ=<%=ThisFAQ%>"><b>Re-Order FAQ</b></a></td>
<td  class=TableButton><a href="View_FAQ.asp?FAQ=<%=Request.QueryString("FAQ")%>"><b>View as FAQ</b></a></td>
<td class=TableButton><a href="View_Rules.asp?FAQ=<%=Request.QueryString("FAQ")%>"><b>View as RuleSet</b></a></td>
<% end if %>
</tr>
</table>

<form class="IPage" name="FManager" method="post" action="FAQ_Manager.asp" >
<% if request.querystring("FAQ") <> "" then  
       'Submit can't seem to handle spaces, see request. form above. encode space as  '|!|' string.
%>
    <input type="hidden" name="FAQ" value=<%=replace(ThisFAQ," ","|!|")%>>
    <input type="hidden" name="page" value=<%=ThisPage%>>
    <input type="hidden" name="FNum" value=<%=ThisRec%>>
<% end if%>
<table align="center" border="0" width="476" height="179" cellspacing="0" cellpadding="0">
  <tr valign="middle">
    <td width="84" align="right">FAQ ID:</td>
    <td width="162">
        <select name="TID"
            <% if request.querystring("action") <> "new" then %>
            onchange="getFAQ(options[selectedIndex].text)"
            <%end if %>
            >
            <%if request.querystring("FAQ") = "" then%>
               <option value="0" selected>Pull down to select FAQ</option>
               <%else%>
               <option value="<%=request.querystring("FAQ")%>" selected><%=request.querystring("FAQ")%></option>
<%  
    end if            
    Set rsselect = Server.CreateObject("ADODB.Recordset")
    rsselect.CursorLocation = adUseClient    
    strFAQList  = "SELECT FAQ_ID FROM faq_hdr "    
    if bFAQ_Admin <> true then
        strFAQList = strFAQList & " WHERE FAQ_Username = '" & sFAQ_Username & "'"
    end if
    strFAQList = strFAQList & ";"
    rsselect.Open strFAQList, sFAQCon    
    Do While NOT rsselect.EOF
        response.write("<option>" &  rsselect("FAQ_ID") & "</option>" & vbcrlf)
        rsselect.MoveNext
     Loop
    rsselect.close
    set rsselect = nothing
%>      
     </select>
    </td>
  <td width="312" style="vertical-align: middle;">
  <%if  modestr = "Create" then %>
      Ordinal:&nbsp;
      <input type="text" name="TORD" size="3"Value="<%=RecCount + 1%>" style="text-align: right;">
      <font size="1">&nbsp;<i>(sort order)</i></font>      
      <% end if %>
  </td>
  </tr>
  <tr valign="middle">
    <td width="84" align="right">Question:</td>
    <td width="475" colspan="5">
<%
Set oFCKeditor = New FCKeditor
oFCKeditor.ToolbarSet = "Basic"
oFCKeditor.Value = sQuestion
oFCKeditor.CreateFCKeditor "TQuest", "100%", 150
%>
    </td>
  </tr>
  <tr valign="middle">
    <td width="84"align="right">Answer:</td>
    <td width="475" colspan="5">
<%
Set oFCKeditor = New FCKeditor
oFCKeditor.ToolbarSet = "Basic"
oFCKeditor.Value = sAnswer
oFCKeditor.CreateFCKeditor "TAns", "100%", 150
%>
    </td>
  </tr>
  <tr  valign="middle">
      <td width="84"></td>
   <td>    <table><tr>
    <td>
        <input type="submit" name="BSubmit" value=<%=modestr%>>
    </td>
    <td>
        <%if modestr <> "Create" then %>
        <input type="submit" value="Delete" name="BSubmit" onclick="return confirm('Are you sure you want to delete this question?');">
        <%end if%>
    </td>
    <td>
        <input type="reset" value="Reset " name="B2">
    </td>
    </tr></table></td>
          <td width="219px">&nbsp;
          <% if request.querystring("FAQ") <> "" and modestr <> "Create" then
              if rsconn.AbsolutePage > 1 then %>
              <a href="FAQ_Manager.asp?FAQ=<%=request.querystring("FAQ")%>&page=1">        
              <IMG SRC="../images/firstrec.gif" WIDTH=13 HEIGHT=13 ALT="" BORDER=0></a>
              <a href="FAQ_Manager.asp?FAQ=<%=request.querystring("FAQ")%>&page=<%=cstr(rsconn.AbsolutePage - 1)%>">        
              <IMG SRC="../images/prevrec.gif" WIDTH=13 HEIGHT=13 ALT="" BORDER=0></a>  
          <%else%>
              <IMG SRC="../images/nofirstrec.gif" WIDTH=13 HEIGHT=13 ALT="" BORDER=0>
              <IMG SRC="../images/noprevrec.gif" WIDTH=13 HEIGHT=13 ALT="" BORDER=0>
          <% end if %>
            <select name="TPage" id="TPage"    onchange="getPage('<%=Request.QueryString("FAQ")%>', options[selectedIndex].text)">
           <%  for iCount = 1 to rsconn.RecordCount
                response.write("<option")
                   if iCount = rsconn.AbsolutePage then response.write (" selected")
                response.write (">" &  cstr(iCount) & "</option>" & vbcrlf)
            next %>
            </select>
          <% if rsconn.AbsolutePage < rsconn.RecordCount then %>
              <a href="FAQ_Manager.asp?FAQ=<%=request.querystring("FAQ")%>&page=<%=cstr(rsconn.AbsolutePage + 1)%>">        
              <IMG SRC="../images/nextrec.gif" WIDTH=13 HEIGHT=13 ALT="" BORDER=0></a>
              <a href="FAQ_Manager.asp?FAQ=<%=request.querystring("FAQ")%>&page=<%=rsconn.RecordCount%>">        
              <IMG SRC="../images/lastrec.gif" WIDTH=13 HEIGHT=13 ALT="" BORDER=0></a>
          <%else%>
              <IMG SRC="../images/nonextrec.gif" WIDTH=13 HEIGHT=13 ALT="" BORDER=0>
              <IMG SRC="../images/nolastrec.gif" WIDTH=13 HEIGHT=13 ALT="" BORDER=0>
          <% end if %>
      <% end if %>
      </td>
  </tr>
</table>
</form>
</table>
</form>                                                            
<%
        if request.querystring("FAQ") <> "" then
            rsconn.close
            set rsconn = nothing
        end if
    end if
end if ' permitted to maintain
sPageTitle = "FAQ Manager"%>
<!--#INCLUDE FILE="../SiteHeader.inc"-->
</body>

</html>

Regards .. Alan
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 12055768
Can you give me the structure of your table faq_data
so that i can get it working on here ...

Cheers
0
 
LVL 10

Expert Comment

by:ADSaunders
ID: 12055914
faq_data  CREATE TABLE `faq_data` (  
`FAQNum` int(4) unsigned NOT NULL auto_increment,
`FAQ_ID` varchar(16) NOT NULL default '',
`FAQ_Sort` int(20) unsigned zerofill NOT NULL default '00000000000000000000',
`FAQ_QUEST` text NOT NULL,
`FAQ_ANS` text NOT NULL,
PRIMARY KEY  (`FAQNum`),
UNIQUE KEY `FAQNum` (`FAQNum`),
KEY `FAQ_ID`(`FAQ_ID`)
TYPE=MyISAM COMMENT='Contains The Question and answer for an FAQ'

syntax may not be correct, c&p from an SQLYog report.

.. Alan
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 12056361
Oh i got it to work without the header part table as this populates the drop down box

Kind of looking the link controls the next record.
not selecting from the list.

0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 12056494
Ok kind of got a example going..................

You pointed me in right direction
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
migrate from classic ASP to C# .NET 5 57
Classic ASP + JS 4 68
Autosum input type=text when checkbox is true 28 71
Update field in order 21 109
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now