📄 excelentry.asp
字号:
<%@ Language=VBScript %>
<%
'变量声明
Dim con ' ADODB链接对象
Dim rst ' ADODB记录集对象
Dim strCon '存储Excel数据库连接字符串的变量
Dim strSQL ' 存储执行插入的SQL查询字符串的变量
Dim name '存储患者姓名的变量
Dim phone ' 存储患者电话的变量
Dim key ' For Each循环里的迭代（虚拟变量）
Dim GoAhead '指明是否继续的标志
Dim myStr '存储显示在右手边表格里消息的变量
' ADODB 常量
'---- CursorTypeEnum Value ----
Const adOpenKeyset = 1
'---- LockTypeEnum Value ---- 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'>Please enter the Patient name.</Font>"
Else
Response.Write "<FONT Color = 'Red'>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("WriteToExcel.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") '64
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 cannot be deleted." _
& "<BR>"
Response.Write "Either name or phone number 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
%>
<TH>
<% Response.Write fld.Name %>
</TH>
<%
Next
rst.MoveFirst
Do While Not rst.EOF
%>
<TR>
<% For Each fld in rst.Fields %>
<TD>
<% Response.Write fld.Value %>
</TD>
<% Next %>
</TR>
<% rst.MoveNext
Loop
%>
</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>
<%
on error resume next
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>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -