Excel not updating
am 21.03.2007 17:01:47 von anon1m0usHere 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 ""
Response.Write "Please enter the Patient name."
Else
Response.Write ""
Response.Write "Please enter the Phone number."
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." & "
"
Response.Write "Either name or phone number "
Response.Write "is incorrect. " & "
"
Else
name = ""
phone = ""
set rst = Server.CreateObject("ADODB.Recordset")
rst.Open "Select * from [Sheet1$]", con
Response.Write "
" & fld.Name & " | "
---|
" & fld.Value & " | "
rst.Close
Set rst=Nothing
con.Close
Set con=Nothing
End If
End If
End If
%>