Updating SQL database with classic ASP

Question

I have to modify an old ASP page to allow users to update their listed phone numbers, stored in a SQL 2005 database. The code for the page looks incredibly heavy, so I apologise for its density. Users enter a name into a form and are directed to the following result page:

     Phone Directory Results Detail
         
            


<%
MM_Telephone_STRING = "dsn=Telephone;uid=sa;pwd=sapw;"
%>
<%
Dim Telephone__varName
Telephone__varName = "%"
if (Request("FullName")  <> "") then Telephone__varName = Request("FullName") 
%>
<%
Dim Telephone__varJob
Telephone__varJob = "%"
if (Request("JobTitle")   <> "") then Telephone__varJob = Request("JobTitle")  
%>
<%
set Telephone = Server.CreateObject("ADODB.Recordset")
Telephone.ActiveConnection = MM_Telephone_STRING
Telephone.Source = "SELECT Ext, FullName, JobTitle, EMailAddress, Photo, Extras,       Keywords, dbo.SearchDetailScreen.Photo, siteadd, department2, sitefax, Mobile  FROM dbo.SearchDetailScreen  WHERE FullName LIKE '" + Replace(Telephone__varName, "'", "''") + "' AND JobTitle LIKE '" + Replace(Telephone__varJob, "'", "''") + "'    OR FullName LIKE '" + Replace(Telephone__varName, "'", "''") + "' AND JobTitle is null"
Telephone.CursorType = 0
Telephone.CursorLocation = 2
Telephone.LockType = 3
Telephone.Open()
Telephone_numRows = 0
%>
<%
'  *** Recordset Stats, Move To Record, and Go To Record: declare stats variables
' set the record count
Telephone_total = Telephone.RecordCount
' set the number of rows displayed on this page
If (Telephone_numRows < 0) Then
  Telephone_numRows = Telephone_total
Elseif (Telephone_numRows = 0) Then
  Telephone_numRows = 1
End If
' set the first and last displayed record
Telephone_first = 1
Telephone_last  = Telephone_first + Telephone_numRows - 1
' if we have the correct record count, check the other stats
If (Telephone_total <> -1) Then
  If (Telephone_first > Telephone_total) Then Telephone_first = Telephone_total
  If (Telephone_last > Telephone_total) Then Telephone_last = Telephone_total
  If (Telephone_numRows > Telephone_total) Then Telephone_numRows = Telephone_total
End If
%>
<%
' *** Move To Record and Go To Record: declare variables
Set MM_rs    = Telephone
MM_rsCount   = Telephone_total
MM_size      = Telephone_numRows
MM_uniqueCol = ""
MM_paramName = ""
MM_offset = 0
MM_atTotal = false
MM_paramIsDefined = false
If (MM_paramName <> "") Then
  MM_paramIsDefined = (Request.QueryString(MM_paramName) <> "")
End If
%>
<%
' *** Move To Specific Record: handle detail parameter
If (MM_paramIsDefined And MM_rsCount <> 0) Then
  ' get the value of the parameter
  param = Request.QueryString(MM_paramName)
  ' find the record with the unique column value equal to the parameter value
  MM_offset = 0
  Do While (Not MM_rs.EOF)
    If (Cstr(MM_rs.Fields.Item(MM_uniqueCol).Value) = param) Then
      Exit Do
    End If
    MM_offset = MM_offset + 1
    MM_rs.MoveNext
  Loop
  ' if not found, set the snumber of records and reset the cursor
  If (MM_rs.EOF) Then
    If (MM_rsCount < 0) Then MM_rsCount = MM_offset
    If (MM_size < 0 Or MM_size > MM_offset) Then MM_size = MM_offset
    MM_offset = 0
    ' reset the cursor to the beginning
    If (MM_rs.CursorType > 0) Then
  MM_rs.MoveFirst
    Else
      MM_rs.Close
      MM_rs.Open
    End If
  End If
End If
%>
<%
' *** Move To Record: handle 'index' or 'offset' parameter
if (Not MM_paramIsDefined And MM_rsCount <> 0) then
  ' use index parameter if defined, otherwise use offset parameter
  r = Request.QueryString("index")
  If r = "" Then r = Request.QueryString("offset")
  If r <> "" Then MM_offset = Int(r)
  ' if we have a record count, check if we are past the end of the recordset
  If (MM_rsCount <> -1) Then
    If (MM_offset >= MM_rsCount Or MM_offset = -1) Then  ' past end or move last
      If ((MM_rsCount Mod MM_size) > 0) Then         ' last page not a full repeat region
        MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
      Else
        MM_offset = MM_rsCount - MM_size
      End If
    End If
  End If
  ' move the cursor to the selected record
  i = 0
  While ((Not MM_rs.EOF) And (i < MM_offset Or MM_offset = -1))
    MM_rs.MoveNext
    i = i + 1
  Wend
  If (MM_rs.EOF) Then MM_offset = i  ' set MM_offset to the last possible record
End If
%>
<%
' *** Move To Record: if we dont know the record count, check the display range
If (MM_rsCount = -1) Then
  ' walk to the end of the display range for this page
  i = MM_offset
  While (Not MM_rs.EOF And (MM_size < 0 Or i < MM_offset + MM_size))
    MM_rs.MoveNext
    i = i + 1
  Wend
  ' if we walked off the end of the recordset, set MM_rsCount and MM_size
  If (MM_rs.EOF) Then
    MM_rsCount = i
    If (MM_size < 0 Or MM_size > MM_rsCount) Then MM_size = MM_rsCount
  End If
  ' if we walked off the end, set the offset based on page size
  If (MM_rs.EOF And Not MM_paramIsDefined) Then
    If (MM_offset > MM_rsCount - MM_size Or MM_offset = -1) Then
      If ((MM_rsCount Mod MM_size) > 0) Then
        MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
      Else
        MM_offset = MM_rsCount - MM_size
      End If
    End If
  End If
  ' reset the cursor to the beginning
  If (MM_rs.CursorType > 0) Then
    MM_rs.MoveFirst
  Else
    MM_rs.Requery
  End If
  ' move the cursor to the selected record
  i = 0
  While (Not MM_rs.EOF And i < MM_offset)
    MM_rs.MoveNext
    i = i + 1
  Wend
End If
%>
<%
' *** Move To Record: update recordset stats
' set the first and last displayed record
Telephone_first = MM_offset + 1
Telephone_last  = MM_offset + MM_size
If (MM_rsCount <> -1) Then
  If (Telephone_first > MM_rsCount) Then Telephone_first = MM_rsCount
  If (Telephone_last > MM_rsCount) Then Telephone_last = MM_rsCount
End If
' set the boolean used by hide region to check if we are on the last record
MM_atTotal = (MM_rsCount <> -1 And MM_offset + MM_size >= MM_rsCount)
%>
<%
' *** Go To Record and Move To Record: create strings for maintaining URL and Form    parameters
' create the list of parameters which should not be maintained
MM_removeList = "&index="
If (MM_paramName <> "") Then MM_removeList = MM_removeList & "&" & MM_paramName & "="
MM_keepURL="":MM_keepForm="":MM_keepBoth="":MM_keepNone=""
' add the URL parameters to the MM_keepURL string
For Each Item In Request.QueryString
  NextItem = "&" & Item & "="
  If (InStr(1,MM_removeList,NextItem,1) = 0) Then
    MM_keepURL = MM_keepURL & NextItem & Server.URLencode(Request.QueryString(Item))
  End If
Next
' add the Form variables to the MM_keepForm string
For Each Item In Request.Form
  NextItem = "&" & Item & "="
  If (InStr(1,MM_removeList,NextItem,1) = 0) Then
    MM_keepForm = MM_keepForm & NextItem & Server.URLencode(Request.Form(Item))
  End If
Next
' create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm
if (MM_keepBoth <> "") Then MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
if (MM_keepURL <> "")  Then MM_keepURL  = Right(MM_keepURL, Len(MM_keepURL) - 1)
if (MM_keepForm <> "") Then MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)
' a utility function used for adding additional parameters to these strings
Function MM_joinChar(firstItem)
  If (firstItem <> "") Then
    MM_joinChar = "&"
  Else
    MM_joinChar = ""
  End If
End Function
%>
<%
' *** Move To Record: set the strings for the first, last, next, and previous links
MM_keepMove = MM_keepBoth
MM_moveParam = "index"
' if the page has a repeated region, remove 'offset' from the maintained parameters
If (MM_size > 0) Then
  MM_moveParam = "offset"
  If (MM_keepMove <> "") Then
    params = Split(MM_keepMove, "&")
    MM_keepMove = ""
    For i = 0 To UBound(params)
  nextItem = Left(params(i), InStr(params(i),"=") - 1)
      If (StrComp(nextItem,MM_moveParam,1) <> 0) Then
        MM_keepMove = MM_keepMove & "&" & params(i)
      End If
    Next
    If (MM_keepMove <> "") Then
      MM_keepMove = Right(MM_keepMove, Len(MM_keepMove) - 1)
    End If
  End If
End If
' set the strings for the move to links
If (MM_keepMove <> "") Then MM_keepMove = MM_keepMove & "&"
urlStr = Request.ServerVariables("URL") & "?" & MM_keepMove & MM_moveParam & "="
MM_moveFirst = urlStr & "0"
MM_moveLast  = urlStr & "-1"
MM_moveNext  = urlStr & Cstr(MM_offset + MM_size)
prev = MM_offset - MM_size
If (prev < 0) Then prev = 0
MM_movePrev  = urlStr & Cstr(prev)
%>

        
                    
                    Need to change your extension? Click here
                    
           
            Full Name 
            <%=(Telephone.Fields.Item("FullName").Value)%>
               " alt="<%=(Telephone.Fields.Item("FullName").Value)%>" />   
          
           
            Ext 
            <%=(Telephone.Fields.Item("Ext").Value)%> <%=(Telephone.Fields.Item("Extras").Value)%>
          
            
            Mobile 
            <%=(Telephone.Fields.Item("Mobile").Value)%>
            
           
             Email Address 
            
              <% strMail = "mailto:" & Telephone.Fields.Item("EMailAddress").Value %>
              <%=(Telephone.Fields.Item("EMailAddress").Value)%>
          
           
            Job Title 
            <%=(Telephone.Fields.Item("JobTitle").Value)%>
          
           
            Department 
            <%=(Telephone.Fields.Item("department2").Value)%>
          
           
            Site 
            <%=(Telephone.Fields.Item("siteadd").Value)%>
          
           
            Fax 
            <%=(Telephone.Fields.Item("sitefax").Value)%>
          
           
            New Search
          

        
        <%
Telephone.Close()
%>

How would I go about allowing users to update the field 'Ext'?

Originally, I made a modal window with a simple two-field form. I can't seem to paste the code into here without losing most of it. Its fields were given the labels and IDs 'FullName' and 'Ext'.

The form used the method Get to send the captured info to the next page, action=phoneresults.asp, which is essentially the same page. On that page I had the following update query:

<%
Dim FullName, Ext
Dim sConnString, connection, sSQL
FullName = Request.Form("FullName")
Ext = Request.Form("Ext")
sSQL = "INSERT into dbo.telephone.staffdetails (FullName, Ext) values ('" & FullName & "', '" & Ext & "')" 
%>

There's actually no necessity for the modal window. It was just the first way of updating it that popped (up?) into mind. Having looked around online for quite some time, I've begun to wonder if it's really the simplest way to do it; is it over-complicating things?

My update query is presumably monumentally off, and I'm guessing so, too, is the way I'm trying to give it the information it needs. I apologise for it. Searching around online, it also seems like the code I'm working on is incredibly old.

Updating SQL database with classic ASP

Answers (1)

Related Questions