Question on update database

[Squall Leonhart]

Hi, everyone
I am new here. Nice to meet you guys:)

Please take a look at following code.



<%


Dim adoCon 'Holds the Database Connection Object
Dim rsEdit 'Holds the recordset for the new record to be added to the database
Dim strSQL 'Holds the SQL query for the database
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("tech_re.mdb")
Set rsEdit = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM tblRequest"
rsEdit.CursorType = 2
rsEdit.LockType = 3
rsEdit.Open strSQL, adoCon

%>

<form name="validform" method="post" action="updateRequest.asp" onSubmit="return dateDiff(this);">
<table ALIGN=CENTER border="0" cellpadding="3" cellspacing="1" width="100%">
<tr>
<th colspan="6" height="25"><b>Update the topic</b></th>
</tr>
<tr>
<td width=1%>&nbsp</td>
<td align=center><span class="gen"><b>Description</b></span></td>
<td width=2%>&nbsp</td>

</tr>
<% Do while not rsEdit.eof %>
<tr>
<td width=1%><input type="hidden" name="ID" value="<%=rsEdit.fields("ID")%>"></td>

<td align=center><span class="gen"><input type="text" style="width:170px" name="description" value="<%=rsEdit.fields("description")%>"></span></td>
<td width=2%>&nbsp</td>

</tr>
<% rsEdit.Movenext
Loop
%>
<tr><td><br></td></tr>
<tr>
<th colspan="6" height="25"><input type="submit" name="Submit" value="Submit"></th>
</tr>
</table>
</form>

<%
'Reset server objects
rsEdit.Close
Set rsEdit = Nothing
Set adoCon = Nothing
%>


I got no error on this page. As you can see, using Do while loop page generates the textboxes( For example, if there is 10 items on database, page shows 10 textboxes with description in it)

But when I change the content, click submit and move on to update page.


<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsUpdate
Dim strSQL 'Holds the SQL query for the database

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("tech_re.mdb")
Set rsUpdate = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT * FROM tblRequest"
rsUpdate.CursorType = 2
rsUpdate.LockType = 3
rsUpdate.Open strSQL, adoCon
Do while not rsUpdate.eof
rsUpdate.Fields("description") = Request.Form("description")
rsUpdate.Update
rsUpdate.Movenext
Loop
'Reset server objects
rsUpdate.Close
Set rsUpdate = Nothing
Set adoCon = Nothing
Response.Redirect "it_request.asp"

%>


Page doesn't update database and doesn't redirect to the it_request.asp page.
How can I make it work?

[yurib]

First of all I will strongly recommend you to learn "Insert" and "Update" SQL statements. This will save you a lot of time with locking table troubleshooting.

So in your case I would recommend something like that:

adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("tech_re.mdb")

ser rsUpdate=adoCon.execute("Update tblRequest set description='" & Request.Form("description") & "'")

'this will update all records with the same: Request.Form("description")

adoCon.close
response.Redirect("it_request.asp")

Don't forget brackets around file while redirection!!!!