populating HTML Table on same page based on user input 1

[AzizKamal]

I am working on a page in classic asp with vbscript for server side processing. On page 1, named criteriaregionbkp.asp, there is a dropdown having list of employees. User selects employees from this dropdown and clicks the add button. Upon clicking the Add button, I need to add Employee Number, Name and Region in an HMTL table on the same page. So for example, when the user selects Name1, HTML table will show:

EmployeeNumber1 Name1 Region1

Now when the user selects Name2, HTML table on the same page will show:

EmployeeNumber1 Name1 Region1
EmployeeNumber2 Name2 Region2

When the user is done adding employees in the table, there is a Submit button at the end of the table. User will click submit, criteriaregionbkp.asp will submit and next page as per action attribute will be inserttoxs.asp where the values entered in HTML table on criteriaregionbkp.asp will be read and inserted in an access file.

The issue that I am facing is that I need to retain values of HTML table, when the user clicks Add after the first time. For example, when the user selects Name2 and clicks Add, page posts back to criteriaregionbkp.asp and I need to retain previous values EmployeeNumber1 Name1 Region1. I tried to accomplish this by assigning recordset to session but I am getting an error in this:

   <TABLE width="100%" border="1" cellspacing="0" cellpadding="0">
   <TR>
   <TD width=20%><b><font color="#660000">Employee Number</font></b></TD>
   <TD width=50%><b><font color="#660000">Name</font></b></TD>
   <TD width=30%><b><font color="#660000">Region</font><b></TD>
   </TR>
   <%call fillgrid()%> 
   </TABLE>

<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0" HEIGHT="53">
<TR>
<td width=20%>&nbsp;</td>
<TD width=50%>
<DIV ALIGN="CENTER"><INPUT TYPE="button" NAME="Submit1" VALUE="Add" style="width: 80px;" onClick="<%="submitFunction(1," & Request.QueryString("cntr")+1 & ")"%>"></DIV>
</TD>
<td width=30%>&nbsp;</td>
</TR>
</TABLE>

sub fillgrid()
dim sql
Const adInteger = 3
Const adChar = 129
if Request.QueryString("cntr")<>"" then
	call connect()
	set rs2=server.CreateObject("ADODB.Recordset")
	set rs3=server.CreateObject("ADODB.Recordset")
	set rs4=server.CreateObject("ADODB.Recordset")
	with rs3.Fields
		.Append "Employee", adInteger
		.Append "Name", adChar, 250
		.Append "Region", adChar, 100
	end with
	rs3.CursorType=3
	rs3.LockType=3
	rs3.Open
	sql="select firstname || ' ' || middlename || ' ' || lastname as empname,region.name from "
	sql=sql & session("bookcode") & ".empprofile," & session("bookcode") & ".units," & session("bookcode")
	sql=sql & ".region where empprofile.unitcode=units.code and units.regioncode=region.code and "
	sql=sql & "empcode=" & Request.Form("empname")
	rs2.Open sql,cn
	rs3.AddNew
	rs3.Fields("Employee").Value=Request.Form("empname")
	rs3.Fields("Name").Value=rs2.Fields("empname")
	rs3.Fields("Region").Value=rs2.Fields("name")
	rs3.Update
    if Request.QueryString("cntr")>1 then		
		set rs4 = Session("myrs")
		do while not rs4.EOF
			rs3.AddNew
			rs3.Fields("Employee").Value=rs4.Fields("Employee")
			rs3.Fields("Name").Value=rs4.Fields("Name")
			rs3.Fields("Region").Value=rs4.Fields("Region")
			rs3.Update		
			rs4.MoveNext
		loop
	end if		
	rs2.Close
	do while not rs3.EOF
		Response.Write "<TR><TD>" & rs3.Fields("Employee") & "</TD><TD>" & rs3.Fields("Name") & "</TD><TD>" & rs3.Fields("Region") & "</TD></TR>"
		rs3.MoveNext
	loop
	set Session("myrs") = rs3
	rs3.Close		
end if	
end sub

Request.QueryString("cntr") will be nil when page first loads. Upon subsequent postbacks, Request.QueryString("cntr") will become 1, then 2 and so on…

When I run this page and select first employee name, it appears in the HTML table correctly. When I select second employee name and click Add, I get the following error:

Operation is not allowed when the object is closed.

This error occurs on this line:

do while not rs4.EOF

 

[foxbox]

is it possible to store the selected employee number in a temp table? What you attempt to do is roughly equal to filling a cart in an average webshop.
If there is a limited max number of employees you could decide to store them into a SESSION array variable?

 

[AzizKamal]

Thanks foxbox.

I updated my code and used session array variable as suggested by you. But I am getting Type mismatch error in this approach. Code for fillgrid subroutine is:

sub fillgrid()
dim sql,i
dim emps(3,500)
if Request.QueryString("cntr")<>"" then
	call connect()
	sql="select firstname || ' ' || middlename || ' ' || lastname as empname,region.name from "
	sql=sql & session("bookcode") & ".empprofile," & session("bookcode") & ".units," & session("bookcode")
	sql=sql & ".region where empprofile.unitcode=units.code and units.regioncode=region.code and "
	sql=sql & "empcode=" & Request.Form("empname")
	rs2.Open sql,cn
	if Request.QueryString("cntr")>1 then
		emps=session("arremps")
	end if		
	emps(0,Request.QueryString("cntr")-1)=Request.Form("empname")
	emps(1,Request.QueryString("cntr")-1)=rs2.Fields("empname")
	emps(2,Request.QueryString("cntr")-1)=rs2.Fields("name")
	rs2.Close
	for i=0 to UBound(emps)-1
		Response.Write "<TR><TD>" & emps(0,i) & "</TD>"
		Response.Write "<TD>" & emps(1,i) & "</TD>"
		Response.Write "<TD>" & emps(2,i) & "</TD></TR>"
	next
	session("arremps")=emps
end if	
end sub

When I select first employee name and click Add, the record is displayed correctly in HTML table. When I select second employee and click Add, I get Type mismatch error. Error is generated on the following line:

emps=session("arremps")

 

[foxbox]

maybe this helps in order to get the idea:

<%
if session("init") = "" then
 'Make sure session("sArray") is an array!
 dim aName(1)
 aName(0) = -1   ' by this we can test if the array has content
 session("sArray") = aName
 session("init") = false
end if

' We must store the session in a variable
dim aNames
aNames = session("sArray")


if request.ServerVariables("REQUEST_METHOD") = "POST" then
  'Add an array element
  dim aLen
  aLen = ubound(aNames)
  ReDim preserve aNames( aLen + 1 ) 
  aNames( aLen + 1) = request.Form("fName")
  session("sArray") = aNames
end if

%>
<form method="post" action="tt1621690.asp">
Pick a name:
<select onchange="submit()" name="fName">
<option>Kirk</option>
<option>Spock</option>
<option>Scotty</option>
<option>O'Hara</option>
</select>

<%
aNames = session("sArray")
for i = 0 to ubound( aNames )
 response.Write  aNames(i) & "<br>"
next
%>

</form>

Personally i would do it with a database table solution.

 

[AzizKamal]

Thanks foxbox.

Your sample code is very useful and I wrote the following in my scenario:

sub fillgrid()

dim i,sql

if session("init") = "" then
 'Make sure session("sArray") is an array!
 dim aName(0)
 aName(0) = -1   ' by this we can test if the array has content
 session("sArray") = aName
 session("init") = false
end if

' We must store the session in a variable
dim aNames
aNames = session("sArray")

if request.ServerVariables("REQUEST_METHOD") = "POST" then
  'Add an array element
  dim aLen
  aLen = ubound(aNames)
  ReDim preserve aNames( aLen + 1 ) 
  aNames( aLen + 1) = Request.Form("empname")
  session("sArray") = aNames
end if

aNames = session("sArray")
call connect()
for i = 1 to ubound( aNames )
 response.Write  "<TR><TD>" & aNames(i) & "</TD>"
 sql="select firstname || ' ' || middlename || ' ' || lastname as empname,region.name from "
 sql=sql & session("bookcode") & ".empprofile," & session("bookcode") & ".units," & session("bookcode")
 sql=sql & ".region where empprofile.unitcode=units.code and units.regioncode=region.code and "
 sql=sql & "empcode=" & aNames(i)
 rs2.Open sql,cn
 Response.Write "<TD>" & rs2.Fields("empname") & "</TD>"
 Response.Write "<TD>" & rs2.Fields("name") & "</TD></TR>"
 rs2.Close
next

end sub

Now when I select first employee, it appears correctly in the HTML table. When I select second, third.. they also appear correctly in the HTML table.

I have two buttons on this page. One is the Add button that updates the HTML table. Second one is the Submit button that submits the form to next page inserttoxs.asp. For this, I have called submitFunction on onClick event of two buttons with values 1 and 2; submitFunction(1) for Add Button,submitFunction(2) for Submit Button

Javascript
function submitFunction(i) {
if (i==1) document.form1.action="criteriaregionbkp.asp";
if (i==2) document.form1.action="inserttoxs.asp";
document.form1.submit()
}

Now suppose I add two employee names and click Submit, then I have written the following in inserttoxs.asp to get employee values separated by commas in an memplist variable.

dim memplist
dim aNames
aNames = session("sArray")
for i = 1 to ubound( aNames )
 memplist=memplist & aNames(i) & ","
next
memplist=left(memplist,len(memplist)-1)

This fulfills my requirement but since sArray is in session, so when the user re-visits the page criteriaregionbkp.asp, he again sees employee names displayed in the HTML table. Is it possible to clean the session("sArray") after this line on inserttoxs.asp page:

memplist=left(memplist,len(memplist)-1)

I am not sure if it can simply be achieved by:

session("sArray")=""

because I also need to make sure that session("sArray") is an array.

Personally i would do it with a database table solution.

What I understand from this is you are suggesting to create a table in oracle (my scenario) to temporarily store employee codes, names because as far as I know; in-memory database like DataSet, DataTable and DataView are available in ASP.NET but not in classic asp. Please confirm.

 

[foxbox]

clean the session": do it the way i started my sample:

dim aName(0) 
aName(0) = -1  
session("sArray") = aName

Because you are already making a rountrip to the server (doing an SQL Select in order to retrieve name/ bookcode info) i would put the code in a temp table.
In the script i do the SELECT with JOIN in order to retrieve all relavant info.
And the pulldown with codes now only consists of codes that are not already in the temp table (i presume you can enter them 1 time only)

 

[AzizKamal]

Thanks foxbox.

session cleaned successfully after adding the following lines in insertoxs.asp page:

dim aName(0) 
aName(0) = -1  
session("sArray") = aName

database table solution is also a good solution and I will try to test this too.

To accomplish:
the pulldown with codes only consisting of codes that are not already in the HTML table

Can you please mention a way to achieve this...

The main issue is that Employee dropdown is being displayed above and Add Button and HTML tables are below the drop down.

So I have this HTML first:

<TABLE>
    <TR><TD>Employee Name</TD>
        <TD><SELECT id="empname" NAME="empname"><%call fillempname()%> </SELECT>
        </TD>
    </TR>
  </TABLE>

and then this HTML:

  <TABLE>
   <TR>
<TD><INPUT TYPE="button" NAME="Submit1" VALUE="Add" onClick="submitFunction(1)">
      </TD>
   </TR>
   </TABLE>
   <TABLE width="100%" border="1" cellspacing="0" cellpadding="0">
   <TR>
   <TD width=20%><b><font color="#660000">Employee Number</font></b></TD>
   <TD width=50%><b><font color="#660000">Name</font></b></TD>
   <TD width=30%><b><font color="#660000">Region</font><b></TD>
   </TR>
   <%call fillgrid()%> 
   </TABLE>

and then the fillempname and fillgrid subroutines are:

sub fillempname()
on error resume next
dim rs
dim sql
sql="select upper(firstname) || ' ' || upper(middlename) || ' ' || upper(lastname) || ' (' || empcode || ')' as empname,empcode from " & session("bookcode") & ".empprofile order by upper(firstname) || ' ' || upper(middlename) || ' ' || upper(lastname) || ' (' || empcode || ')'"
set rs=server.CreateObject("ADODB.Recordset")
call connect()
rs.Open sql,cn
if err.number<>0 then
	call HandleError(err.number,err.description,err.source) 	
else
	do while not rs.EOF
		Response.Write "<OPTION VALUE=" & rs.Fields("empcode") & ">" & rs.Fields("empname") & "</OPTION>"
		rs.MoveNext
	loop
end if
rs.Close
set rs=nothing
cn.close
set cn=nothing
end sub

sub fillgrid()
dim i,sql
dim rs2
set rs2=server.CreateObject("ADODB.Recordset")
if session("init") = "" then
 'Make sure session("sArray") is an array!
 dim aName(0)
 aName(0) = -1   ' by this we can test if the array has content
 session("sArray") = aName
 session("init") = false
end if
' We must store the session in a variable
dim aNames
aNames = session("sArray")
if request.ServerVariables("REQUEST_METHOD") = "POST" then
  'Add an array element
  dim aLen
  aLen = ubound(aNames)
  ReDim preserve aNames( aLen + 1 ) 
  aNames( aLen + 1) = Request.Form("empname")
  session("sArray") = aNames
end if
aNames = session("sArray")
call connect()
for i = 1 to ubound( aNames )
 response.Write  "<TR><TD>" & aNames(i) & "</TD>"
 sql="select firstname || ' ' || middlename || ' ' || lastname as empname,region.name from "
 sql=sql & session("bookcode") & ".empprofile," & session("bookcode") & ".units," & session("bookcode")
 sql=sql & ".region where empprofile.unitcode=units.code and units.regioncode=region.code and "
 sql=sql & "empcode=" & aNames(i)
 rs2.Open sql,cn
 Response.Write "<TD>" & rs2.Fields("empname") & "</TD>"
 Response.Write "<TD>" & rs2.Fields("name") & "</TD></TR>"
 rs2.Close
next
end sub

So, if let's say employee code 1 is entered, it is added to aNames array and session("sArray") AFTER execution of fillempname subroutine and I cannot eliminate 1 from dropdown since I do not have this info at that time...

 

[AzizKamal]

I could not prevent, pulldown with codes, to display only those codes that are not already in the HTML table, using the session array variable solution.

However, I was able to prevent addition of duplicate entries in HTML Table:

sub fillgrid()
dim i,sql
dim rs2
set rs2=server.CreateObject("ADODB.Recordset")
if session("init") = "" then
 'Make sure session("sArray") is an array!
 dim aName(0)
 aName(0) = -1   ' by this we can test if the array has content
 session("sArray") = aName
 session("init") = false
end if
' We must store the session in a variable
dim aNames
aNames = session("sArray")
if request.ServerVariables("REQUEST_METHOD") = "POST" then
  'Add an array element
  dim aLen,[blue]flag
  flag=0
  for i = 1 to ubound(aNames)
	if aNames(i)=Request.Form("empname") then
		flag=1
	end if
  next
  if flag=0 then[/blue]
	aLen = ubound(aNames)
	ReDim preserve aNames( aLen + 1 ) 
	aNames( aLen + 1) = Request.Form("empname")
	session("sArray") = aNames
  [blue]end if[/blue]
end if
aNames = session("sArray")
call connect()
for i = 1 to ubound( aNames )
 response.Write  "<TR><TD>" & aNames(i) & "</TD>"
 sql="select firstname || ' ' || middlename || ' ' || lastname as empname,region.name from "
 sql=sql & session("bookcode") & ".empprofile," & session("bookcode") & ".units," & session("bookcode")
 sql=sql & ".region where empprofile.unitcode=units.code and units.regioncode=region.code and "
 sql=sql & "empcode=" & aNames(i)
 rs2.Open sql,cn
 Response.Write "<TD>" & rs2.Fields("empname") & "</TD>"
 Response.Write "<TD>" & rs2.Fields("name") & "</TD></TR>"
 rs2.Close
next
end sub