• Home
 · 
• Active Server Pages
 · 
• 350 (Comments Included) lines of Code that Form a Single Dynamic (Add new Records,Delete Selected R

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

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

• 1
• 2
• 3
• 4
• 5

(3(3 Vote))

<%  ' 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>