updating access table through website 1

[tziviak2]

I want to update 1 field in a record on the website-here is the code-but it's not doing a/t. I'm fairly new to this so I'd appreciate help. (do I have to list all fields-even though I'm just working with one?)
heres the code:
Select Case a
Case "I": ' Get a record to display


tkey=key
strsql = "SELECT * FROM [sessions] WHERE [recNum]=" & tkey

set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql, conn
If rs.EOF Then
Response.Clear
Response.Redirect "results.asp?ReportType=SR"
Else
rs.MoveFirst
End If

' Get the field contents

x_SessionNote = rs("SpecifyAmountOfProgressOrLackOfProgress")

rs.Close
Set rs = Nothing

Case "U": ' Update

' Open record
tkey=key
strsql = "SELECT * FROM [Sessions] WHERE [recNum]=" & tkey

set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql, conn, 1, 2


If rs.EOF Then
Response.Clear
Response.Redirect "results.asp?ReportType=SR"
End If

tmpFld = x_SessionNote


rs("SpecifyAmountOfProgressOrLackOfProgress") = tmpFld


rs.Update
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
Response.Clear
Response.Redirect "results.asp?ReportType=SR"
End Select
%>

<!--#include file="header.asp"-->





<form action="SessionNoteEdit.asp" method="post">
<table border="0" cellspacing="1" cellpadding="5" bgcolor="#CCCCCC" width="569">
<input type="hidden" name="x_SessionNote" value="<%= x_SessionNote %>">
<input type="hidden" name="key" value="<%= key %>">
<input type="hidden" name="a" value="U">

<tr>
<td bgcolor="#9966ff" width="74">&nbsp;</td>
<td bgcolor="#F5F5F5" width="472">
<p align="center"><b><font face="Arial" size="4">Use this page to edit&nbsp;Session
Note. </font></b></p>
<p><font face="Arial">When finished editing&nbsp; click on the save button to
save the Session note, or Click the cancel to disregard the Changes and return to the
Session notes page</font></td>
</tr>

<tr>
<td bgcolor="#9966ff" width="74"><font color="#FFFFFF">Session Note&nbsp;</font></td>
<td bgcolor="#F5F5F5" width="472"><font size="-1">
<textarea name="x_SessionNote" cols="50" rows="3"><%= x_SessionNote %></textarea>
</tr>


</table>
<p>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type="button" value="Cancel" onclick="history.go(-1);">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<input type="submit" name="Action" value="SAVE"></form>

 

[MoLaker]

The following link has some good basic lessons in database operation via the web.

http://www.aspfriends.com/learn/D.asp

Also, this link should help you advance further.

http://www.w3schools.com/default.asp

But, to address your problem immediately, rs.UPDATE requires an rs.EDIT first. But, going further, you should not use EDIT/UPDATE to update an open recordset over the web. Instead, look into using an SQL UPDATE query.

Case "U": ' Update
        tkey=key
        strsql = "SELECT * FROM [Sessions] WHERE [recNum]=" & tkey
        set rs = Server.CreateObject("ADODB.Recordset")
        rs.Open strsql, conn, 1, 2
        If rs.EOF Then
            Response.Clear
            Response.Redirect "results.asp?ReportType=SR"
        End If
        tmpFld = x_SessionNote 
[b]'close the recordset since you now have the data required stored in variables[/b][red]
        rs.close  
        set rs = nothing  
        strSQL = "UPDATE Sessions SET [SpecifyAmountOfProgressOrLackOfProgress] = '" & tmpFld & "' WHERE recNum = " & tkey
[/red][b]'execute an UPDATE SQL to update your record[/b][red]
        Set rs=conn.Execute(sql)[/red]
        Set rs = Nothing
        conn.Close
        Set conn = Nothing
        Response.Clear
        Response.Redirect "results.asp?ReportType=SR"

My example may not be totally correct as I'm a little lazy this morning. But, I hope it gives you some ideas. Also, look into how to handle double-quotes, single quotes and apostrophes in your data. A textarea input box invites the use of such characters and if users enter such, your update will bomb.