Solved

Dynamically Update Records

Posted on 2006-11-02
11
322 Views
Last Modified: 2008-02-26
Hey All,

I am trying to build an addon to an existing ASP Web system with a SQL DB on the backend, so while I can customize my own code I cannot alter the existing code.  Here is my problem.  The system has a set of rosters for each class an instructor teaches.  We want to add the ability to allow the instructors to submit final grades.  So I copied the code for the rosters and modified it to not only list the students, but also a select box with the option to choose a grade for each one.

That was the easy part.  The part I am struggling with is how to write the code for the page that will input the grades into their records.  Thankfully it is only editing an existing record in a table that holds all of the grades for every student.  But I am not sure how to essentially write code to update records when I don't know how many records and which ones until the first page is displayed.

I know I need to do some kind of Loop, but I am completely unsure of how to proceed.  I hope this makes sense.  Please ask any questions and I will do my best to answer them.  Thanks!!
0
Comment
Question by:NUHSAdmin
  • 6
  • 5
11 Comments
 
LVL 22

Expert Comment

by:WMIF
ID: 17862606
this should be no problem to do at all, but i would like to clarify the setup first.  are the grades going into the same table as the roster of students?  maybe it is just an additional column?
0
 
LVL 22

Expert Comment

by:WMIF
ID: 17862624
also, please share your code that you are using to make the roster list page.
0
 

Author Comment

by:NUHSAdmin
ID: 17867931
Yes the grades are going into an additional column in a table that lists the students and the class they are in.  I am posting the code below, but it might not be very clear.  It is the code from the existing system with a small modification I made to add a drop down for grade entry.  I believe it is building the query and using a View in the SQL database, not directly pulling from a table.  Thanks again!! Here is the code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Option Explicit

%>
<!--#Include File="TRSSession.asp"-->
<%
    if CLng(TRSSession("FacultyID") ) > 0then
    else
       Response.Redirect ("login.asp")
    end if

    Response.Buffer = True  
   
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
      <head>
            <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
            <title>Faculty Portal</title>
            <link href="Portal.css" rel="stylesheet" type="text/css">
                  <script language="javascript" src="common.js"></script>
                  <script language="javascript">
                  var LastOfferDIV      = 0;
                  var ActiveOfferDIV      = -1;

                  function showSchedule( studentUID, idname, srofferid )
                  {
                        var strRequest;
                        strRequest = "ceFacultySchedule.asp?studentuid=" + studentUID + "&stidname=" + idname;
                        strRequest = strRequest + "&srofferid=" + srofferid + "&activeofferdiv=" + ActiveOfferDIV;
                        
                        document.NoName.ActiveOfferDIV.value = ActiveOfferDIV;
                        
                        window.location.href = strRequest;
                  }

                  function onBodyLoad()
                  {
                        OnChangeCourse();
                  }

                  function showOfferDIV( divIndex )
                  {
                        var divName;
                        
                        divName = "SROfferDIV" + divIndex.toString();
                        showDIV( divName );      
                  }

                  function hideOfferDIV( divIndex )
                  {
                        var divName;
                        
                        divName = "SROfferDIV" + divIndex.toString();
                        hideDIV(divName);
                  }

                  function OnChangeCourse()
                  {
                        var divIndex;
                        var optionObjectName;
                        if ( LastOfferDIV > 0)
                        {
                              for( index=0; index<=LastOfferDIV; index++ )
                              {
                                    optionObjectName = "CourseNameOption" + index.toString();
                                    elem = document.getElementById(optionObjectName);
                                    if( elem.selected == true )
                                    {
                                          divIndex = index;
                                          showOfferDIV(divIndex);
                                    }
                                    else
                                    {
                                          divIndex = index;
                                          hideOfferDIV(divIndex);
                                    }
                              }
                        }
                  }
            </script>
      </head>
      <body onLoad="javascript:onBodyLoad();">
            <table class="Portal_Container_Main" ID="Table1">
                  <!--TopBanner Row-->
                  <tr>
                        <td class="Portal_Container_TopBanner">
                              <table class="Portal_Default_Table" ID="Table2">
                                    <tr>
                                          <td class="Portal_Container_ClientLogo"></td>
                                          <td class="Portal_Container_PortalLogo"></td>
                                    </tr>
                              </table>
                        </td>
                  </tr> <!--/TopBanner Row-->
                  <!--Top Menu Row-->
                  <tr>
                        <td class="Portal_Container_TopMenu">
                              <table class="Portal_Default_Table" ID="Table3">
                                    <tr>
                                          <td class="Portal_Container_TopMenu_Text">
                                                <!--#Include File="Topmenu.asp"-->
                                          </td>
                                          <td align="right" class="Portal_Container_TopMenu_Text"><%= FormatDateTime(Date, 1) %></td>
                                    </tr>
                              </table>
                        </td>
                  </tr>
                  <!--/TopMenu Row-->
                  <!--Body Row-->
                  <tr>
                        <td class="Portal_Container_Body" valign="top">
                              <table class="Portal_Default_Table" ID="Table4">
                                    <tr>
                                          <td class="Portal_Container_LeftMenu" valign="top">
                                                <table class="Portal_Default_Table" ID="Table5">
                                                      <tr>
                                                            <td valign="top">
                                                                  <table cellpadding="0" cellspacing="" border="0" ID="Table6">
                                                                        <tr>
                                                                              <td valign="top">
                                                                                    <TABLE id="Table7" cellSpacing="0" cellPadding="2" border="0" class="Portal_Group_Border">
                                                                                          <TR>
                                                                                                <TD class="Portal_Group_Body" height="40">
                                                                                                      <%if len(TRSSession("FacultyName")) > 0 then %>
                                                                                                      <%=TRSSession("FacultyName")%>
                                                                                                      <br>
                                                                                                      Current term:
                                                                                                      <%= TRSSession("textTerm")%>
                                                                                                      <a href="cePortalChgTerm.asp">Change Term</a>
                                                                                                      <% end if %>
                                                                                                </TD>
                                                                                          </TR>
                                                                                    </TABLE>
                                                                              </td>
                                                                        </tr>
                                                                        <tr>
                                                                              <td valign="top"><br>
                                                                                    <!--Menu-->
                                                                                    <!--#Include File = "FacultyPortalMenu.html"-->
                                                                                    <!--/Menu-->
                                                                              </td>
                                                                        </tr>
                                                                  </table>
                                                            </td>
                                                      </tr>
                                                      <tr>
                                                            <td valign="bottom"><a href="http://www.threerivers-cams.com" target="_blank"><img src="Images/poweredbycams.gif" border="0" alt="Powered By CAMS" /></a></td>
                                                      </tr>
                                                </table>
                                          </td> <!--Body-->
                                          <td valign="top">
                                                <table class="Portal_Container_Content" ID="Table8">
                                                      <tr>
                                                            <td valign="top">
                                                                  <table cellpadding="2" cellspacing="0" class="Portal_Content_Table" border="0" ID="Table9">
                                                                        <tr>
                                                                              <td><img src="images/logo_FinalGrades.jpg" alt="Page Logo">
                                                                              </td>
                                                                              <td align =right ></td>
                                                                        </tr>
                                                                        <tr>
                                                                              <td class="Portal_Content_Table_Body" colspan = "2">
                                                                                    <!-- Actual Content Goes Here -->
                                                                                    <%
            Dim FacultyID
            Dim TextTerm
            Dim rsFaculty
            Dim strUserName
            Dim strSvrName
            Dim strDBName
            Dim strError
            Dim TermID
            Dim SROfferID      
            Dim busPortal
            Dim result
            Dim IDName
            Dim strLastStudentName
            Dim pageCount
            Dim strUserPass
            Dim Count
            
            Dim picpath
          
            Dim nRowColor
            Dim rowColor
            Dim xBGColor
          
            'nRowColor = 2
            'rowColor(1) = "#e5e5e5"
            'rowColor(2) = "#c4c4c4"
            
            FacultyID = TRSSession("FacultyID")      
            TextTerm = TRSSession("TextTerm")
            strSvrName = Application.Value("strSvrName")
            strDBName = Application.Value("strDBName")
            strUserName = Application.Value("strUserName")
            TermID = TRSSession("TermID")
            strUserPass = request.form("password")

            Set busPortal = Server.CreateObject("CAMSPortal.busPortal")
            result = busPortal.ReadFacultySchedule(FacultyID, TextTerm, rsFaculty, strUserName, strSvrName, strDBName, strError )
            
            rsFaculty.Sort = "Department, Course, CourseType, Section, SROfferID"
          
            If rsFaculty.RecordCount = 0 Then
                  %>
                        <table width="70%" border="0" ID="Table10">
                              <tr>
                                    <td>
                                          <font face="verdana" size="4" color="red">
                                                <b>
                                                      You do not have any courses assigned to you.
                                                </b>
                                          </font>
                                    </td>
                              </tr>
                        </table>
                  <%
            Else
                        
                  SROfferID = -1
                  if not isNull(request.QueryString("srofferid") ) then
                        SROfferID = request.QueryString("srofferid")
                  end if
                  
                  Dim busRegReports
                  Dim rsCriteria
                  Dim rsCourses
                  Dim rsStudents
                  Dim rsAttendance
                  Dim rsSchedule
                  Dim rsStudentAddresses
                  Dim rsReportParams
                  Dim strLastCourse
                  Dim strDisplayCourseID
                  Dim strToday
                  Dim strLastName
                  Dim stuCount
               
                  Set busRegReports = Server.CreateObject("RegReports.busRegReports")
                  result = busRegReports.GetCriteriaRecord(rsCriteria, strSvrName, strDBName, strError)
                  rsCriteria("ReportType") = 3
                  rsCriteria("count") = 2
                  rsCriteria("Name1") = 30
                  rsCriteria("value1") = TRSSession("FacultyID")
                  rsCriteria("Name2") = 1
                  rsCriteria("value2") = TRSSession("TermID")
                  rsCriteria("Username") = TRSSession("FacultyID")
                  rsCriteria.Update
                  rsCriteria.movefirst

                  strUserName = TRSSession("FacultyID")
               
                  result = busRegReports.PrintOfferingRoster(rsCriteria, rsCourses, rsStudents, rsAttendance, _
                                    rsSchedule, rsStudentAddresses, rsReportParams, strUserName, strSvrName, strDBName, strError)    
                  rsStudents.sort = "Term, Department, CourseID, CourseType, Section, SROfferID, StudentName"
                  
                  Dim strFilter
                  pageCount = 0
                  %>
                        <table width="70%" bgcolor="#efefef" border="0" ID="Table11">
                              <tr>
                                    <td width="20%">
                                          <font face="verdana" size="2"><strong>Select</strong></font>
                                    </td>
                                    <td>
                                          <select size="1" name="CourseName" id="CourseName" onChange="javascript:OnChangeCourse()">
                                                <option id="CourseNameOption0" value="All">SELECT ALL</option>
                                                <%pageCount=0%>
                                                <%
                                                      do while not rsFaculty.EOF
                                                            pageCount=PageCount+1
                                                        strDisplayCourseID = rsFaculty("Department").value & rsFaculty("Course").value & rsFaculty("CourseType").value & rsFaculty("Section").value
                                              %>
                                                <option id=CourseNameOption<%=pageCount%> value=<%=pageCount%>>
                                                      <%=strDisplayCourseID & " - " & rsFaculty("CourseName").value%>
                                                </option>
                                                <%
                                                          rsFaculty.MoveNext
                                                    loop
                                              %>
                                          </select>
                                    </td>
                              </tr>
                        </table>
                        <table width="70%" border="0" ID="Table12" class = "Portal_Group_Table">
                              <tr bgcolor="#071c59">
                                    <th width="20%" align="center" class = "Portal_Group_SubHeader1">
                                          Student ID
                                    </th>
                                    <th width="25%" align="center" class = "Portal_Group_SubHeader1">
                                          Name
                                    </th>
                                    <th width="10%" align="center" class = "Portal_Group_SubHeader1">
                                          Grade
                                    </th>
                              </tr>
                        </table>
                        <%
                  
                  pageCount=0
                  If isobject(rsFaculty) then
                        rsFaculty.movefirst
                  End If
                  'rsFaculty.save "c:\rsFaculty.xml", 1
                  'rsStudents.save "c:\rsStudents.xml", 1
                  
                  Do While Not rsFaculty.EOF
                        pageCount=pageCount+1
                        rowColor = False
                        
                        rsStudents.filter = 0
                        rsStudents.filter = "SROfferID = " & rsFaculty("SROfferID").Value
                        rsStudents.sort = "StudentName asc"
                        
                        if rsStudents.BOF and rsStudents.EOF then
                        %>
                        <div id="SROfferDIV<%=pageCount%>" style="Visible:False">
                              <table width="70%" border="0" ID="Table13">
                                    <tr>
                                          <td><font face="verdana" size="4" color="red"><b> There are zero students enrolled for this
                                                            course for this term.</b></font>
                                          </td>
                                    </tr>
                              </table>
                        </div>
                        <%
                        Else
                              %>
                        <div id="SROfferDIV<%=pageCount%>" style="Visible:False">
                              <table width="70%" border="0" ID="Table14"  cellpadding="2" cellspacing="1">
                                    <%
                                    stuCount = 0
                                    Do While Not rsStudents.EOF
                                          stuCount = stuCount + 1
                                          idname = Server.URLEncode("ID: " & rsStudents("StudentID").value & " " & rsStudents("StudentName").value)

                                          If rowColor then
                                                xBGColor="#C4C4C4"
                                                rowColor = false
                                          Else
                                                xBGColor="#E5E5E5"
                                                rowColor = true
                                          End if
                                          %>
                                    <tr bgcolor=<%=xBGColor%>>
                                          <td width="20%" align="center">
                                                <%
                                                            if not isNull(rsStudents("StudentID").value) then
                                                                  %>
                                                <font face="verdana">
                                                <input type="hidden" name="StudentID<%=stuCount%>" value="<%=rsStudents("StudentID").value%>">
                                                      <%=rsStudents("StudentID").value%>
                                                </font>
                                                <%
                                                            else
                                                                  %>
                                                &nbsp;
                                                <%
                                                            end if
                                                            %>
                                          </td>
                                          <td width="25%">
                                                &nbsp;<%=stuCount%>.&nbsp;<%
                                                            if not isNull(rsStudents("StudentName").value) then
                                                                  %>
                                                
                                                      <font face="verdana">
                                                            <%=rsStudents("StudentName").value%>
                                                      </font>
                                                <%
                                                            else
                                                                  %>
                                                &nbsp;
                                                <%
                                                            end if
                                                            %>
                                          </td>
                                          <td width="10%" align="center">
                                                <%
                                                            if rsStudents("Grade").value <> "" then
                                                            %>
                                                <font face="verdana">
                                                      <%=rsStudents("Grade").value%>
                                                </font>
                                                <%
                                                            else
                                                            %>
                                                <select name="Grade" size="1">
  <option selected="selected"> - </option>
  <option value="A">A</option>
  <option value="B">B</option>
  <option value="C">C</option>
  <option value="D">D</option>
  <option value="F">F</option>
  <option value="S">S</option>
  <option value="U">U</option>
  <option value="I">I</option>
</select>
                                                <%
                                                            end if
                                                            %>
                                          </td>
                                          
                                          
                                    </tr>
                                    <%
                                                rsStudents.MoveNext      
                                          Loop
                                                %>
                              </table>
                        </div>
                        <%
                                                End If
                                                rsFaculty.MoveNext      
                                          Loop
                                    %>
                        
                        <script language="javascript">
                              LastOfferDIV=<%=pageCount%>
                        </script>
                        <%
                  'Page 0 - All Students....
                  rsStudents.Filter = 0
                  rsStudents.Sort ="StudentName asc"
                  Count = 1
                  pageCount=0
                  rowColor = False
                  %>
                        <div id="SROfferDIV0">
                              <table width="70%" border="0" ID="Table15"  cellpadding="2" cellspacing="1">
                                    <%
                        do while not rsStudents.EOF
                                 if strLastName <> rsStudents("StudentName").value then
                                      strLastName = rsStudents("StudentName").value
                                    idname = Server.URLEncode("ID: " & rsStudents("StudentID").value & " " & rsStudents("StudentName").value)
                              
                                          If rowColor then
                                                xBGColor="#C4C4C4"
                                                rowColor = false
                                          Else
                                                xBGColor="#E5E5E5"
                                                rowColor = true
                                          End if
                                          %>
                                    <tr bgcolor=<%=xBGColor%>>
                                          <td width="20%" align="center">
                                                <%
                                          if not isNull(rsStudents("StudentID").value) then
                                                %>
                                                <font face="verdana">
                                                      <%=rsStudents("StudentID").value%>
                                                </font>
                                                <%
                                          else
                                                %>
                                                &nbsp;
                                                <%
                                          end if
                                          %>
                                          <td width="25%">
                                                &nbsp;<%=Count & ". "%><%
                                          if not isNull(rsStudents("StudentName").value) then
                                                      %>
                                                
                                                      <font face="verdana">
                                                            <%=rsStudents("StudentName").value%>
                                                      </font>
                                                <%
                                          else
                                                      %>
                                                &nbsp;
                                                <%
                                          end if
                                                      %>
                                          </td>
                                          <td width="10%" align="center">
                                                <%
                                          if rsStudents("Grade").value <> "" then
                                                %>
                                                <font face="verdana">
                                                      <%=rsStudents("Grade").value%>
                                                </font>
                                                <%
                                          else
                                                %>
                                                <select name="Grade" size="1">
  <option selected="selected"> - </option>
  <option value="A">A</option>
  <option value="B">B</option>
  <option value="C">C</option>
  <option value="D">D</option>
  <option value="F">F</option>
  <option value="S">S</option>
  <option value="U">U</option>
  <option value="I">I</option>
</select>
                                                <%
                                          end if
                                          %>
                                          </td>
                                          
                                    </tr>
                                    <%
                                    Count=Count+1
                              end if
                              rsStudents.MoveNext
                        loop
                        %>
                              </table>
                        </div>
                        <%
            End if
            %>
                                                                                    
                                                                                    <!-- End Actual Content Goes Here -->
                                                                              </td>
                                                                        </tr>
                                                                  </table>
                                                            </td>
                                                      </tr>
                                                </table>
                                          </td> <!--/Body-->
                                    </tr>
                              </table>
                        </td>
                  </tr>
                  <!--Bottom Row-->
                  <tr>
                        <td align="center" class="Portal_Container_Bottom">CAMS Enterprise - &#169; 1985 -
                              2006 - All rights reserved <a href="http://www.threerivers-cams.com" target="_blank">
                                    Three Rivers Systems, Inc.</a></td>
                  </tr>
                  <!--/Bottom Row-->
            </table> <!-- TemplateEndEditable -->
      </body>
</html>
0
 
LVL 22

Expert Comment

by:WMIF
ID: 17869016
you will need to give each grade drop down a unique id so that they dont combine values and so you know what student gets what grade.  

<select name="Grade<%=rsStudents("StudentID")%>" size="1">
  <option selected="selected"> - </option>
  <option value="A">A</option>
  <option value="B">B</option>
  <option value="C">C</option>
  <option value="D">D</option>
  <option value="F">F</option>
  <option value="S">S</option>
  <option value="U">U</option>
  <option value="I">I</option>
</select>


i dont see any form tags though to submit this information.  do you have some that i am missing somewhere?
0
 

Author Comment

by:NUHSAdmin
ID: 17869652
I know about changing the selects so that each one has an unique name, and no I haven't added in the rest of the form tags.  I didn't want to get too far until I knew how to do the other side of it.  Thank you for pointing it out though!
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 22

Accepted Solution

by:
WMIF earned 500 total points
ID: 17870197
alright, just taking one step at a time. :)

on the page that processes this form, you just have to loop through the form objects and get the values if its a grade field.

for each item in request.form
  if left(item,5) = "grade" then
    studentid = mid(item,6)
    query = "update table set gradecol = '" & request.form(item) & "' where studentidcol = '" & studentid & "'"
    conn.execute query
  end if
next
0
 

Author Comment

by:NUHSAdmin
ID: 17870328
it's really that easy?  Awesome!!  I will try this later and as long as it works, I will award you the full points.  Thanks!!!
0
 
LVL 22

Expert Comment

by:WMIF
ID: 17870352
it really is. :)  you dont even need any additional form fields, just the form tag to submit.  let me know how it goes.
0
 

Author Comment

by:NUHSAdmin
ID: 17883930
So this isn't working.  I know I am missing something.  Here is what my code for the page that processes the form looks like.  Do I need to change the name of anything or a variable?  All that seems to happen is that it goes through and gets redirected to the appropriate page, but nothing has been written to the database.  I couldn't even get it to show me the Response.Write query before it executed each time.  Any help is greatly appreciated!




<!-- #INCLUDE FILE="include/dbconnectNUHS.asp" -->

<%

Department = Request.Form("Department")
Course = Request.Form("Course")
CourseType = Request.Form("CourseType")
Section = Request.Form("Section")

%>

<%
for each item in request.form
  if left(item,5) = "grade" then
    studentid = mid(item,6)
      query = "UPDATE CAMS_SRAcademic_View SET Grade = '" & Request.Form(item) & "' WHERE StudentID = '" & studentid & "' AND Course = '" & Course & "' AND CourseType = '" & CourseType & "' AND Section = '" & Section & "'"
      Response.Write query
    ObjDbConn2.Execute query
  end if
next
%>

<%
Response.Redirect "ceFinalGrades.asp"
%>

0
 

Author Comment

by:NUHSAdmin
ID: 17883987
Never Mind, I missed something that I have now fixed.  Thanks again!!!!
0
 
LVL 22

Expert Comment

by:WMIF
ID: 17884781
glad you got it figured out.  feel free to post back in you have any further problems with this script.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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 information …
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 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

20 Experts available now in Live!

Get 1:1 Help Now