Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-09-14
5
Medium Priority
?
315 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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Integration Management Part 2
Loops Section Overview
Suggested Courses

876 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