Solved

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

Posted on 2004-09-14
5
310 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

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…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

627 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