Here is a code for ASP inputs being updated in Excel. However, when I
click to submit the user, the excel does not get updated!
<% @Language="VBScript" %>
<%
Dim con
Dim rst
Dim strCon
Dim strSQL
Dim name
Dim phone
Dim key
Dim goAhead
Dim myStr
Const adOpenKeyset = 1
Const adLockPessimistic = 2
On Error Resume Next
name=Request("txtPatientName")
phone=Request("txtPhone")
For Each key In Request.Form
If Request.Form(key)= "" Then
If key = "txtPatientName" Then
Response.Write "<FONT Color = 'Blue'>"
Response.Write "Please enter the Patient name.</Font>"
Else
Response.Write "<FONT Color = 'Red'>"
Response.Write "Please enter the Phone number.</Font>"
End If
goAhead = False
Exit For
End If
GoAhead=True
Next
If goAhead = True Then
name=Replace(Request("txtPatientName"),"'","''")
If Len(name)<0 Or _
Len(phone)<>0 Then
Set con = Server.CreateObject("ADODB.Connection")
strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
strCon=strCon & server.MapPath("ExcelDb.xls") & ";"
strCon=strCon & "Extended Properties=Excel 8.0"
If Request("cmdSubmit")="Enter Data in Excel" Then
strSQL = "INSERT INTO [Sheet1$] (Patient, Phone)"
strSQL = strSQL & " VALUES ('" & name & "'"
strSQL = strSQL & ",'" & Phone & "')"
End If
With con
.Open strCon
If Request("cmdDelete")<>"Delete Data" Then
.Execute(strSQL)
Else
set rst = Server.CreateObject("ADODB.Recordset")
rst.Open "Select * from [Sheet1$] Where Patient='" & _
name & "'" & _
" AND phone ='"& phone &"'", _
con, adOpenKeyset, adLockPessimistic
rst.fields(0).value = ""
rst.fields(1).value = ""
rst.Update
rst.Close
End If
End With
If err.Number =3021 Then
Response.Write "The information you entered "
Response.Write "cannot be deleted." & "<BR>"
Response.Write "Either name or phone number "
Response.Write "is incorrect. " & "<P>"
Else
name = ""
phone = ""
set rst = Server.CreateObject("ADODB.Recordset")
rst.Open "Select * from [Sheet1$]", con
Response.Write "<TABLE Border=""1"">"
For Each fld in rst.Fields
Response.Write "<TH>" & fld.Name & "</TH>"
Next
rst.MoveFirst
Do While Not rst.EOF
Response.Write "<TR>"
For Each fld in rst.Fields
Response.Write "<TD>" & fld.Value & "</TD>"
Next
Response.Write "</TR>"
rst.MoveNext
Loop
Response.Write "</TABLE>"
rst.Close
Set rst=Nothing
con.Close
Set con=Nothing
End If
End If
End If
%>
<HR>
<HTML>
<HEAD>
<TITLE>Patient Data Entry Screen</TITLE>
</HEAD>
<BODY>
<FORM Action="ExcelEntry.asp" Method = "POST" Name="form1">
<P>
<TABLE BORDER="1" CELLPADDING="2" CELLSPACING="4">
<TR>
<TD>
<TABLE BORDER="1" CELLPADDING="2" CELLSPACING="3">
<TR>
<TD>Patient Name: </TD>
<TD>
<INPUT Type="text1" Name="txtPatientName"
Value="<%=name%>" Size= "30">
</TD>
</TR>
<TR>
<TD>Phone: </TD>
<TD>
<INPUT Type="text2" Name="txtPhone"
Value="<%=phone %>">
</TD>
</TR>
<INPUT Type="Submit" Name="cmdSubmit"
Value="Enter Data in Excel">
<INPUT Type="Submit" Name="cmdDelete"
Value="Delete Data">
</TABLE>
</TD>
<TD>
<%
If err.number = 0 Then
If (Request("cmdSubmit")="Enter Data in Excel" or _
Request("cmdDelete") = "Delete Data") and _
Request.Form(key) <>"" Then
myStr = "The following data has been successfully "
If Request("cmdSubmit")="Enter Data in Excel" Then
Response.Write "<I><FONT Color = 'Green'>" & _
myStr & "added:</I></FONT><HR>"
ElseIf Request("cmdDelete") = "Delete Data" Then
Response.Write "<I><FONT Color = 'Green'>" & _
myStr & "deleted:</I></FONT><HR>"
End If
End If
If Request("txtPatientName") <>"" or _
Request("txtPhone") <>"" Then
Response.Write "Patient Name: <B>" & _
Request("txtPatientName") & "</B></P>"
Response.Write "Phone Number: <B>" & _
Request("txtPhone") & "</B>"
End If
End If
%>
</TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>
