vbCoders Guest



Don't have an account yet? Register
 


Forgot Password?



350 (Comments Included) lines of Code that Form a Single Dynamic (Add new Records,Delete Selected R

by Adi Katz (2 Submissions)
Category: Active Server Pages
Compatability: ASP (Active Server Pages)
Difficulty: Unknown Difficulty
Originally Published: Sat 30th March 2002
Date Added: Mon 8th February 2021
Rating: (1 Votes)

350 (Comments Included) lines of Code that Form a Single Dynamic (Add new Records,Delete Selected Records,BatchUpdate a Whole Table,Display

Rate 350 (Comments Included) lines of Code that Form a Single Dynamic (Add new Records,Delete Selected R



<%  ' Look For the Starting Point 
private sub ImplementAddNew()
Rs.AddNew
  For i = 0 to Rs.Fields.Count - 1
   if Rs.Fields(i).Type = 3 then i = i + 1
   if Rs.Fields(i).Type <> 3 then
     IF Request.Form("Fld" & i) <> ""  then 
        Rs.Fields(i).Value = Request.Form("Fld" & i)
     Else
Rs.Fields(i).Value = "<Null>" ' Nice Trick 
    ' To Ensure Acceptence Of Null Even If
    ' DB Doesn't Allow It. 
     End if
   End if
  Next
Rs.Update
Rs.MoveFirst
End sub  
      
    private sub ImplementUpdate()
        Rs.ActiveConnection = nothing
        'Disconnected Recordset
        'Allow You To Change(Update) Multiple Fields
        'And Update as a Whole
        Dim FldCounter 
        Do until Rs.EOF
        For i = 0 to Rs.Fields.Count - 1
   if Rs.Fields(i).Type <> 3 then
      FldCounter = FldCounter + 1
     IF Request.Form("Fld" & FldCounter) <> "<Null>"  then
        Rs.Fields(i).Value = Request.Form("Fld" & FldCounter)
     Else
        Rs.Fields(i).Value = "<Null>"
     End if
   End if
  Next
 Rs.MoveNext
 Loop 
'ReConnect 
Rs.ActiveConnection = Cn
Rs.UpdateBatch()
'Back To First Row Before Building The Table
'Not Using This Method Will Result In A Single ROW Table 
Rs.MoveFirst()
    End sub 
    
    private sub ImplementDelete()
   
        ColumValue = Request.Form("colValue")
        if ColumValue = "" then Exit sub 
        ' If any of the CheckBoxes Are Not Checked, Exit 
        ColumName = Request.Form("colName")
        Sql = _
          "delete from [Order Details] Where "& _
           ColumName &" in(" & ColumValue & ")"
           'Don't Use * It Couses Error With T - Sql
    
    '----For Debugging...----'       
    'Response.Write Sql
    'Response.End
    '------------------------'
    
    Cn.Execute Sql
    '----------------------------
    'It Would Be Wise To Build a Component
    'That Copmacts The Jet DB Through JRO.Engine
    'Or an Api 
    '----------------------------
    Rs.Requery()
            'A Must After Deleting Records 
    End sub
    
    Private sub PageInit()
StrCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\DataBases\myNWIND1.MDB" 
        'I Copied The Shippers/Order Details Table Into a new DB

'Works With MSSQL Server AsWell
        
        'StrCn = "Provider=SQLOLEDB;Data Source=YourServer;" & _
        '       "Initial Catalog=NorthWind;User Id=sa;Password=;"         
        
Sql = "SELECT Top 8 * FROM [Order Details]" ' A Small Table to  Work With
LockValue = Request.Form("Locks") ' 1 or 2 or 3 or 4
If Request.Form("Locks") = "" Then 
'  When You Press Delete ,update, Addnew Buttons 
'  Display Last Table Mode
   LockValue = Request.Form("LastLock")
End if
CursorType = 0
LockType = 1
    End sub
    
    private sub OpenCn()
      set Cn = Server.CreateObject("ADODB.Connection")
          Cn.Open strCn
    End sub 
    
    Private Sub SetRs()
      Set Rs = Server.CreateObject("ADODB.Recordset")
          Rs.ActiveConnection = Cn
    End Sub
    
    Private Sub MainMenu()
      ' A PreDefined Thet Displays The Menu
      StrMenu = _
                "<th style=Border-Color:Green;Border-Width:2>" & _
                "<Form Method=post>" & _
                "<SELECT name=Locks onChange" & _
                "='javascript:document.forms(0).submit();'>" & _
                "<OPTION Value=0 selected>Select Mode</OPTION>" & _
    "<OPTION Value=1>ReadOnly Mode</OPTION>" & _
"<OPTION Value=2>Update Mode</OPTION>" & _
                "<OPTION Value=3>Add New Record</OPTION>" & _
                "<OPTION Value=4>Delete Mode</OPTION>" & _
"</SELECT></Form></th>"
    End Sub
    
    Private Sub SetLockInfo()
     ' Set Restrictions For Rs.open() Method
 Select Case Cint(LockValue)
  Case 1
    CursorType = 0
    LockType = 1
  Case 2
    CursorType = 2
    LockType = 4
  Case 3
    CursorType = 3
    LockType = 3
  Case 4
    CursorType = 0
    LockType = 1
  Case Else
 End select
    End Sub
    
    Private Sub OpenRs()
     'Use Disconnected recordset For BatchUpdate
     If Request.Form("proccess") = "update" then Rs.CursorLocation = 3
     Rs.Open Sql,,CursorType,LockType ' Dynamicly Change Restrictions
     
     If Request.Form("proccess") = "update" then 
        Call ImplementUpdate()
     elseIf Request.Form("proccess") = "addnew" Then 
        Call ImplementAddNew()
     elseif Request.Form("proccess") = "delete" then
        
        Call ImplementDelete()
     end if   
     
     Call BuildTable() ' always Build Table After an Operation
    End Sub
    
    Private sub CloseAllRes()
 If IsObject(rs) then
       If Rs.State then
          Rs.Close()
          Set Rs = Nothing
       End if
     End if
     If Cn.State then  
        Cn.Close()
        set Cn = Nothing
     End if
    End sub
    
    private sub BReadOnlyTable()
     Do While Not (Rs.EOF)
         strReadTable = strReadTable & "<tr valign=top>"
         For Each Tdata In Rs.Fields
             strReadTable = strReadTable & "<td>" & _
                            Tdata.value & "</td>"
         Next
         strReadTable = strReadTable & "</tr>"
     Rs.MoveNext
     loop
    End sub
    
    private sub BAddNewTable()
     'prepare AddNewBtn For Concat...
     AddNewBtn = _
                 "<tr><td><INPUT Type=hidden name=LastLock value=" & LockValue & ">" & _
                 "<INPUT Type=hidden name=Proccess Value=addnew>" & _
                 "<INPUT type=button value='Add New' name=AddNewBtn " & _
                 "onClick='javascript:document.addnew.submit();'</td></tr>"
     
     Dim RowCounter 
         RowCounter = 0
     Do While Not (Rs.EOF)  
         strAddTable = strAddTable & "<tr valign=top>"
         For j = 0 to Rs.Fields.Count - 1
             strAddTable = strAddTable & "<td>" & _
                           Rs.Fields(j).Value & "</td>"
         Next
         strAddTable = strAddTable & "</tr>"
         RowCounter = RowCounter + 1
     Rs.MoveNext
     loop
     
     strAddTable = strAddTable & "<tr><Form name=addnew method=post>"
     'Add new Row With Form Elements
     For i = 0 to Rs.Fields.Count - 1
       
         If Rs.Fields(i).Type = 3 then
          strAddTable = strAddTable & _
          "<td>" & RowCounter + 1 & "</td>"
         Else
          strAddTable = strAddTable & _
          "<td>" & _
          "<INPUT type=text id=text2 name=Fld" & i & " Size=15>" & _
          "</td>"
         End if
     Next
 strAddTable = strAddTable & AddNewBtn & "<Input Type=hidden></form></tr>"
    end sub

private sub BUpdateTable()
 UpdateBtn = _
                 "<INPUT Type=hidden name=LastLock value=" & LockValue & ">" & _
                 "<INPUT Type=hidden name=Proccess Value=update>" & _
                 "<INPUT type=button value=Update name=updateBtn " & _
                 "onClick='javascript:document.update.submit();'" 
                 
     strUpdateTable = "<Form Method=post name=update>"            
     Dim RowCounter,FldCounter
         RowCounter = 0 : FldCounter = 0
 Do While Not (Rs.EOF)
         strUpdateTable = strUpdateTable & "<tr valign=top>"
         
         For i = 0 To Rs.Fields.Count - 1
         If Rs.Fields(i).Type = 3 then
         strUpdateTable = strUpdateTable & _
                          "<td>" & Rs.Fields(i).Value & "</td>"
         Else
         FldCounter = FldCounter + 1
         strUpdateTable = strUpdateTable & _
                          "<td><INPUT type=text " & _
                          "name=Fld" & FldCounter & " Value='" & Rs.Fields(i).Value & "' Size="
               IF len(Rs.Fields(i).Value) < 20 then
                  strUpdateTable = strUpdateTable & _
                                   len(Rs.Fields(i).Value)
               Else 
                  strUpdateTable = strUpdateTable & 20
               End If
         strUpdateTable = strUpdateTable & "></td>"
         end if   
         Next
         strUpdateTable = strUpdateTable & "</tr>"
         
 Rs.MoveNext
     loop
     strUpdateTable = strUpdateTable & "<tr><td>" & UpdateBtn & _
                                       "</td><Input Type=hidden></Form></tr>"
End sub    

Private sub BDeleteTable()
 DeleteBtn = _
                 "<INPUT Type=hidden name=LastLock value=" & LockValue & ">" & _
                 "<INPUT Type=hidden name=Proccess Value=delete>" & _
                 "<INPUT type=button value=Delete name=deleteBtn " & _
                 "onClick='javascript:document.Delete.submit();'"        
     
         strDeleteTable = strDeleteTable & "<tr valign=top>"
         strDeleteTable = strDeleteTable & "<Form Name=Delete Method=post>"
         strDeleteTable = strDeleteTable & "<INPUT Type=hidden name=ColName Value=" & Rs.Fields(0).Name & "><tr>"
 Do While Not (Rs.EOF)
         For i = 0 To Rs.Fields.Count - 1
             strDeleteTable = strDeleteTable & "<td>" 
             If i = 0 then
             strDeleteTable = strDeleteTable & "<INPUT type=checkbox name=colValue Value=" & _
             Cint(Rs.Fields(i).Value) & ">" 
             End if
             strDeleteTable = strDeleteTable & Rs.Fields(i).Value & "</td>"
         Next
         strDeleteTable = strDeleteTable & "</tr>"
     Rs.MoveNext
     loop
         strDeleteTable = strDeleteTable & "<tr><td>" & DeleteBtn & "</td>"
         strDeleteTable = strDeleteTable & "</Form></tr></tr>"
End sub

    '--- Starting Point---'
    PageInit
    OpenCn
    SetRs
    SetLockInfo
    OpenRs
    CloseAllRes
    '---------------------'
%>
<HTML>
<HEAD>
<Title>Dynamic Table By Adi Katz</Title>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<style>
td{Border-Color:Red}
</style>
<BaseFont size=3>
</HEAD>
<BODY>
<%Private sub BuildTable()
  'Main Table Calls Sub's To Build Table Body
  Call MainMenu()
%>
  <Table Width=100% Height=100%><tr><td Align=center>
  <table border=4 >
    <tr><%=StrMenu%></tr>
  <%
  If Not Rs.EOF then
  For Each H In Rs.Fields%>

      <th style="
       Border-Color:Green;Border-Width:2
       ;Color:White;BackGround:Navy"><%=H.name%></th>  
      
  <%
  Next
    
  Select Case cint(LockValue)
Case 2
    Call BUpdateTable
         Response.Write strUpdateTable
Case 3
    Call BAddNewTable()
         Response.Write strAddTable
Case 4
        Call BDeleteTable()
             Response.Write strDeleteTable
Case Else
Call BReadOnlyTable()
 Response.Write strReadTable
End Select

  End If%>
  </td></tr></table>
</table>
<%End sub%>

</BODY>
</HTML>

Download Snippet Download this snippet   Add to My Save List Add to My Saved Code

350 (Comments Included) lines of Code that Form a Single Dynamic (Add new Records,Delete Selected R Comments

No comments have been posted about 350 (Comments Included) lines of Code that Form a Single Dynamic (Add new Records,Delete Selected R. Why not be the first to post a comment about 350 (Comments Included) lines of Code that Form a Single Dynamic (Add new Records,Delete Selected R.

Post your comment

Subject:
Message:
0/1000 characters