I know that this is not a good thing to do and it is merely a temp solution to satisfy the moment. However, I am wanting to be ale to extract data from an excel file and stick it into a webpage where certain items can be edited depending on who is viewing the page. I am restricting the persons view with a login and password, which are stored in an access db.
After they login I check their login ID with a column in the excel sheet that contains the same item and I display everything in that row and insert txtfields into certain items where the data needs to be updated. All this is shown in a html table. The table is inside a form.
After entering any updates the user hits the submit button and a new page loads extracting the data from the form and here is where I have trouble, inserts the data into the excel sheet.
This is done with asp pages an ADO DB connection to a range in the excel sheet.
I've included the code below. The first bit of code is from the form page and the second bit is from the page where the data should be updated and reprinted to the browser.
If anyone has any ideas, I would certainly be happy to hear them. If you've got a better way of doing this, then I would love to hear that too. Just keep in mind that the method needs to be abe to restrict the persons viewing of the data and what data they can edit inside of that.
thanks
Here is the code that seems to be giving me problems, the second line anyways.
default_forms.asp:
testing.asp:
After they login I check their login ID with a column in the excel sheet that contains the same item and I display everything in that row and insert txtfields into certain items where the data needs to be updated. All this is shown in a html table. The table is inside a form.
After entering any updates the user hits the submit button and a new page loads extracting the data from the form and here is where I have trouble, inserts the data into the excel sheet.
This is done with asp pages an ADO DB connection to a range in the excel sheet.
I've included the code below. The first bit of code is from the form page and the second bit is from the page where the data should be updated and reprinted to the browser.
If anyone has any ideas, I would certainly be happy to hear them. If you've got a better way of doing this, then I would love to hear that too. Just keep in mind that the method needs to be abe to restrict the persons viewing of the data and what data they can edit inside of that.
thanks
Here is the code that seems to be giving me problems, the second line anyways.
Rich (BB code):
ValueX = CStr(Request.Form("txtVendor_"& X & "_" & Counter2))
objRS2(X).Value = ValueXdefault_forms.asp:
Rich (BB code):
<% @language="vbscript" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Access Data and Update with Forms</title>
</head>
<body>
You are logged on as:
<%
Response.Write "" & Session("UID") & ""
Dim strCompanyName
Dim strShipDate
Dim intCount
strCompanyName = CStr(Session("UID"))
Dim Counter
%>
</p>
<%
Set objConn2 = Server.CreateObject("ADODB.Connection")
objConn2.Open "ADOExcel"
Set objRS2 = Server.CreateObject("ADODB.Recordset")
objRS2.ActiveConnection = objConn2
objRS2.CursorType = 3 'Static cursor.
objRS2.LockType = 2 'Pessimistic Lock.
objRS2.Source = "Select * from myRange2"
objRS2.Open
'Printing out original spreadsheet headings and values.
'Note that the first recordset does not have a "value" property
'just a "name" property. This will spit out the column headings.
%>
<form method="POST" ACTION="testing.asp">
<%
Response.Write("<TABLE BORDER=1 bordercolor = #000000 width=350%\ ><TR bordercolor = #000000>")
For X = 0 To objRS2.Fields.Count - 1
If X = 0 Or X = 2 Or X = 3 Or (X >= 5 And X <= 8) Or (X >= 10 And X <=23) Then
Response.Write("<TD bordercolor = #FF0000>" & objRS2.Fields.Item(X).Name & "</TD>")
End If
Next
Response.Write("</TR>")
objRS2.MoveFirst
Counter = 0
While Not objRS2.EOF
If objRS2.Fields(1).Value = strCompanyName Then
Response.Write("<TR bordercolor = #000000>")
For X = 0 To objRS2.Fields.Count - 1
If X = 0 Or X = 2 Or X = 3 Or (X >= 5 And X <= 8) Or (X >= 10 And X <=15) Or X = 23 Then
Response.write("<TD bordercolor = #000000>" & objRS2.Fields.Item(X).Value & "</TD>")
End If
If (X >= 16 And X <=22) Then
Response.write("<TD bordercolor = #000000>" & objRS2.Fields.Item(X).Value & "
<input type=text name=txtVendor_" & X & "_" & Counter & "size=20></TD>")
End If
Next
Counter = Counter + 1
End If
objRS2.MoveNext
Response.Write("</TR>")
Wend
Response.Write("</TABLE>")
Response.Write("
" & Counter)
'ADO Object clean up.
objRS2.Close
Set objRS2 = Nothing
objConn2.Close
Set objConn2 = Nothing
%>
</p>
<input type="submit" value="Submit" name="Submit">
<input type="reset" value="Reset" name="B2"></p>
</form>
</body>
</html>testing.asp:
Rich (BB code):
<html><body>
<%@ Language=VBScript%> <%
Set objConn2 = Server.CreateObject("ADODB.Connection")
objConn2.Open "ADOExcel"
Set objRS2 = Server.CreateObject("ADODB.Recordset")
objRS2.ActiveConnection = objConn2
objRS2.CursorType = 3 'Static cursor.
objRS2.LockType = 2 'Pessimistic Lock.
objRS2.Source = "Select * from myRange2"
objRS2.Open
Dim strCompanyName
Dim strShipDate
Dim Counter
Dim Counter2
Dim X
Dim ValueX
strCompanyName = CStr(Session("UID"))
objRS2.MoveFirst
'count the number of lines again
While Not objRS2.EOF
If objRS2.Fields(1).Value = strCompanyName Then
Counter = Counter + 1
End If
objRS2.MoveNext
Wend
For Counter2 = 0 To Counter
For X = 16 To 22
ValueX = CStr(Request.Form("txtVendor_"& X & "_" & Counter2))
Response.Write(ValueX & " - ")
Response.Write("txtVendor_"& X & "_" & Counter2)
objRS2(X).Value = ValueX
Next
Response.write("
")
objRS2.MoveNext
Next
objRS2.Update
Response.Write("
Counter2 = " & Counter2)
'ADO Object clean up.
objRS2.Close
Set objRS2 = Nothing
objConn2.Close
Set objConn2 = Nothing
%>
</body></html>
