Data from excel then to access database via asp pages

I have an ASP page that connect to 3 different excel workbooks and pulls specific data from each and then dispays the results by user location. what I need to do now it make it so if there is something that has been labled as "open", which is one of the fields I pull from one of the spreadsheets I need the user to be able to click a link that will carry all the data from that particular row over to another page that will allow them to add a comment as to why the action is still open. once they enter a comment it would then take the row of information including the newly added comment and write it to an access database . The writing to the database part is easy, I can handle that but the getting the data from the first .asp page to the other is the part I am lost on.

This is the code from the header part of the page
<html>
<%


		vXlsFile = "/virtual/data/AU.xlsm"

		vXlsFile = Server.Mappath(vXlsFile)
		
		vXlsFile1 = "/virtual/documents/TechReport/ScholasticRecord.xlsm"
		vXlsFile1 = Server.Mappath(vXlsFile1)

		vXlsFile2 = "/virtual/documents/TechReport/PersonalInfo.xlsx"
		vXlsFile2 = Server.Mappath(vXlsFile2)
		
		
		
		
		ExcelConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
		"Data Source=" & chr(34) & vXlsFile & chr(34) & ";" & _
		"Extended Properties=" & chr(34) & "Excel 12.0;" & chr(34)
		
		'Response.write ExcelConnString
		'Open Database Connection
		Set Conn = Server.CreateObject("ADODB.Connection")
		Conn.Open ExcelConnString
		
		
		XlsConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
		"Data Source=" & chr(34) & vXlsFile1 & chr(34) & ";" & _
		"Extended Properties=" & chr(34) & "Excel 12.0;" & chr(34)
		
		'Response.write ExcelConnString
		'Open Database Connection
		Set xConn = Server.CreateObject("ADODB.Connection")
		xConn.Open XlsConnString

	XlsxConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
		"Data Source=" & chr(34) & vXlsFile2 & chr(34) & ";" & _
		"Extended Properties=" & chr(34) & "Excel 12.0;" & chr(34)
		
		'Response.write ExcelConnString
		'Open Database Connection
		Set xlConn = Server.CreateObject("ADODB.Connection")
		xlConn.Open XlsxConnString

%>

Open in new window


code down in the body example

<%
        	
		sql = "SELECT * FROM tblData where Site Like trim('DTotal') ;" 

			
			set RS = server.createobject("adodb.recordset")
			RS.open sql,Conn,adOpenForwardOnly,adLockReadOnly

<%
If FormatNumber(rs.Fields("%AC_HrBasis ").Value * 100) < 85 Then
	Response.Write "<th style='width: 266px; height: 21px; border-color:black; color: Red; background-color:silver' class='auto-style3'><strong>Red - "
Elseif FormatNumber(rs.Fields("%AC_HrBasis ").Value * 100) < 90 AND FormatNumber(rs.Fields("%AC_HrBasis ").Value * 100) > 85 Then
	Response.Write "<th style='width: 266px; height: 21px; border-color:black; color: Yellow; background-color:silver' class='auto-style3'><strong>Yellow - "
Elseif FormatNumber(rs.Fields("%AC_HrBasis ").Value * 100) > 90 Then
	Response.Write "<th style='width: 266px; height: 21px; border-color:black; color: green; background-color:silver' class='auto-style3'><strong>Green - "
End If
%><%=FormatNumber(rs.Fields("%AC_HrBasis ").Value * 100)%>%


</strong></th>
<%
If FormatNumber(rs.Fields("%PSR_HrBasis ").Value * 100) < 92 Then
	Response.Write "<th style='width: 266px; height: 21px; border-color:black; color: Red; background-color:silver' class='auto-style3'><strong>Red - "
Elseif FormatNumber(rs.Fields("%PSR_HrBasis ").Value * 100) < 96 AND FormatNumber(rs.Fields("%PSR_HrBasis ").Value * 100) > 92 Then
	Response.Write "<th style='width: 266px; height: 21px; border-color:black; color: Yellow; background-color:silver' class='auto-style3'><strong>Yellow - "
Elseif FormatNumber(rs.Fields("%PSR_HrBasis ").Value * 100) > 96 Then
	Response.Write "<th style='width: 266px; height: 21px; border-color:black; color: green; background-color:silver' class='auto-style3'><strong>Green - "
End If
%><%=FormatNumber(rs.Fields("%PSR_HrBasis ").Value * 100)%>%
</strong></th>



<%
	rs.movenext
loop
rs.close
set rs = nothing
%>

<%


		sql = "SELECT * FROM tblData where CampusName Like trim('DPEast%') ;" 
			
		
			set RS = server.createobject("adodb.recordset")
			RS.open sql,xConn,adOpenForwardOnly,adLockReadOnly
%>
<%
do while not rs.eof

%>

</strong></th>
<th style="background-color:silver; width: 301px; height: 21px;" class="auto-style3"><strong>
<%
If rs.Fields("SiteOutAvg") = 1 Then
	Response.Write "Green - <img src=../../images/dotgreen.gif>"
Elseif rs.Fields("SiteOutAvg") = 2 Then
	Response.Write "Red - <img src=../../images/dotred.gif>"
Elseif rs.Fields("SiteOutAvg") = 3 Then
	Response.Write "Red - <img src=../../images/dotred.gif>"
End If
%>
</strong></th>


<%
	rs.movenext
loop
rs.close
set rs = nothing
%>
<%'**********This is For Nx*************%>
<%

		sql = "SELECT * FROM tblData where title Like trim('Nx%') ;" 

			
			set RS = server.createobject("adodb.recordset")
			RS.open sql,xlConn,adOpenForwardOnly,adLockReadOnly


%>
<%
do while not rs.eof

%>

						

<th style="background-color:silver; width: 301px; height: 21px;" class="auto-style3"><strong><%'=FormatNumber(rs.Fields("Poc").Value)%>N/A</strong></th>

<%
	rs.movenext
loop
rs.close
set rs = nothing
%>

</tr>

Open in new window

jlcannonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

R_HarrisonCommented:
Depending on what type of data you want to pass to the next page just send the data as pairs in the URL, so the hyperlink would look something like...  http://mysite.com/page2.asp?Field1=somedate&Field2=somethingelse

Therefore your code would be something like....  
<a href="page2.asp?Field1=<%=Server.URLEncode(rs.Fields(myfield).value)%>&Field2=<%=Server.URLEncode(rs.Fields(myfield).value)%>>LINK TEXT</a>

To get the data back on Page2.asp - just use...   Field1=request("Field1")

Alternatively, you could just pass a reference like the excel line number and then have page2.asp look up all the data from the excel files...  This would help prevent anybody tampering with the URL - but is more work and will take more server resources - it really depends on your requirements.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jlcannonAuthor Commented:
thank you, this worked with a little manipulation.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.