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