Search Tools Links Login

All_Form.ASP


Multi-function form for basic navigation, table editing, and recordset paging. This example includes code to dynamically build an SQL UPDATE command based on changed items on the current record.
http://adozone.cnw.com/default.htm

Original Author: Found on the World Wide Web

Code

<% Option Explicit %>
<% Response.Expires=0 %>




<%
Dim Page ' Local var for page #
Dim cn ' Connection object
Dim rs ' Recordset object
Dim Action ' Button pressed
Dim PageSize ' How far to page
Dim UpdSQL, MySQL ' String to hold SQL
Dim i ' Loop counter
Dim item, value ' Used to retrieve changed fields
Dim issueUpdate ' After Save button press, any changes to make?
Action = Request.Form("NavAction")
If Request.Form("Page") <> "" Then
Page = Request.Form("Page")
Else
Page = 1
End If
If Request.Form("PageSize") <> "" Then
PageSize = Request.Form("PageSize")
Else
PageSize = 5
End If
   Set cn = Server.CreateObject("ADODB.Connection")
   cn.Open Application("guestDSN")
  
   ' Get initial recordset
   Set rs = Server.CreateObject("ADODB.Recordset")
   MySQL = "SELECT * FROM AUTHORS"
rs.PageSize = PageSize
rs.Open MySQL, cn, adOpenKeyset, adLockOptimistic
   Select Case Action
   Case "Begin"
  Page = 1
   Case "Back"
   If (Page > 1) Then
   Page = Page - 1
   Else
   Page = 1
     End If
   rs.AbsolutePage = Page
   Case "Forward"
   If (CInt(Page) < rs.PageCount) Then
   Page = Page + 1
   Else
   Page = rs.PageCount
  End If
   rs.AbsolutePage = Page
   Case "End"
   rs.AbsolutePage = rs.PageCount
Case "Save"
   ' Grab the proper record, then update
   ' This routine is hard coded for AU_ID as the key field.
   ' To alter this to work with another DB Table you will need to
   ' Use the proper primary key instead of AU_ID.
   rs.Close
   MySQL = "SELECT * FROM AUTHORS WHERE au_id = '" & Request.Form("Au_id") & "'"
   rs.MaxRecords = 1
   rs.Open MySQL, cn, adOpenStatic, adLockOptimistic
   UpdSQL = "UPDATE AUTHORS "
   issueUpdate = False
   For i = 0 To (rs.Fields.Count - 1)
   item = rs.Fields(i).Name
   value = Request.Form(item)
   ' Only update items that have changed
   If (rs(i) <> value) Then
     If issueUpdate = False Then
     UpdSQL = UpdSQL & "SET "
     Else
     UpdSQL = UpdSQL & ","
     End If
     issueUpdate = True
     Select Case VarType(rs.Fields(i))
     ' Determine datatype for proper SQL UPDATE syntax
     ' NOTE: Not all data types covered
     Case vbString, vbDate
       UpdSQL = UpdSQL & item & "='" & value & "'"
     Case vbNull
     Case vbInteger
       UpdSQL = UpdSQL & item & "=" & value
     Case vbBoolean
       If value Then
       UpdSQL = UpdSQL & item & "= 1"
       Else
       UpdSQL = UpdSQL & item & "= 0"
       End If
     End Select
   End If
   Next
   UpdSQL = UpdSQL & " WHERE au_id = '" & Request.Form("Au_id") & "'"
   If issueUpdate Then
   cn.Execute UpdSQL
   Set rs = cn.Execute(MySQL)
     End If
   Case "New"
   ' response.write "New"
     rs.AddNew
   Case "Bookmark"
     Session("myBookMark") = rs.BookMark
   Case "Goto"
     If Not IsNull(Session("myBookMark")) Then
     rs.BookMark = Session("myBookMark")
     End If
   Case Else
     rs.MoveFirst
   End Select
%>




  BorderColorDark=Navy BorderColorLight=Aqua BorderColor=Blue>







  
Navigating Example






<%
For i = 0 To rs.Fields.Count - 1
   %>
  
  

   <%
Next
%>
<%= rs.Fields(i).Name %>



  
  
  

  

  

  

  


  

  


  

  











About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 118 times

Categories

ASP/ HTML

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.