Terminal Update problems.....

The following code steadfastly refuses to UPDATE:
<%ENABLESESSIONSTATE=False %>
<%



'Connect To Database
Dim DB
Set DB = Server.CreateObject ("ADODB.Connection")
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\Inetpub\wwwroot\test\test2.2.mdb")
sid = Request.Form("SprogNo")
'Dim rs
Set rs = Server.CreateObject ("ADODB.Recordset")
rs.Open "SELECT * FROM SprogTbl", DB
rs.movefirst
do until rs.EOF
for each sid in rs.fields
sql="UPDATE SprogTbl SET "
sql=sql & "Report='" & Request.Form("Report" & sid) & "',"
sql=sql & "rep2='" & Request.Form("rep2" & sid) & "',"
sql=sql & " WHERE SprogNo='" & sid & "',"

on error resume next
DB.execute sql
RS.movenext
next
loop
rs.movefirst
set rs= nothing
set DB = nothing

%>


The info successfully sent by the form is:

POST Data:
SprogNo1=1&Report1=g&rep21=g&SprogNo2=2&Report2=b&rep22=b&SprogNo3=3&Report3=u&rep23=u

I need to be able to UPDATE multiple, non sequential records in MS access. So far the above code has no effect whatsoever.

any heklp gratefully recieved....

Chris Hall

Also, in this line - Request.Form("Report" & sid) , I'm not sure why you have the &sid inside the parentheses.
Finally, I don't think this is causing the problem, but typically you would do
To aid in your trouble shooting, you could try adding some code to display the results of the SELECT * query, to be sure you're getting the data in the first place.
HTH
Dave

Dave,

Thx. I modified the code to

DB.execute (sql)

the update still refuses to work. thx 4 the close code, as I'm a total beginer at this I don't have a clue how to make the query display the results.

Any further ideas/ help greatly appreciated.

Chris

Hm. Try this. I'm just guessing though

Thank for your time. Still no luck. Concise statement of the problem is below. All and any help greatly appreciated.


Using the old for x = 1 to 3 front- end which posts the correct data (see below). It doesn’t UPDATE. Neither of the WHERE statements work.


Code is:

Dim DB
Set DB = Server.CreateObject ("ADODB.Connection")
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\Inetpub\wwwroot\test\test2.2.mdb")
dim sid
sid = Request.Form("SprogNo")
Dim rs
Set rs = Server.CreateObject ("ADODB.Recordset")
rs.Open "SELECT * FROM SprogTbl", DB
rs.movefirst
do until rs.EOF=TRUE
for each sid in rs.Fields
sql="UPDATE SprogTbl SET "
sql=sql & "Report='" & Request.Form("Report") & "',"
sql=sql & "rep2='" & Request.Form("rep2") & "',"
'sql=sql & " WHERE SprogNo='" & sid & "',"
sql=sql & " WHERE SprogNo='" & Request.Form("SprogNo") & "',"

on error resume next
DB.execute (sql)
RS.movenext
next
loop

==========================================
Interestingly, nothing’s happening here either UPDATE format (report amend 2.42) Additionally, the last UPDATE option would have been better as it didn’t invoke sid. All interchanges of sid with ‘”SprogNo”’ in all combinations tried.- and do not update:

==========================================

rs.Open "SELECT * FROM SprogTbl", DB
rs.movefirst
for each sid in rs.fields
rs.Fields("SprogNo") = Request.Form("SprogNo" & sid)
rs.Fields("Report") = Request.Form("Report" & sid)
rs.Fields("rep2") = Request.Form("rep2" & sid)
rs.update
next
'wend
'Dim strSQL
' strSQL = "UPDATE SprogTbl SET Report = '" & Report & _
' "', rep2 = '" & rep2 & _
' "' WHERE ((SprogTbl.SprogNo)='" & SprogNo & "');"
'DB.execute(strSQL)


RS.movenext
'Next

==============================================

• POST Data:
SprogNo1=1&Report1=a&rep21=d&SprogNo2=2&Report2=b&rep22=e&SprogNo3=3&Report3=c&rep23=f&SprogNo4=&Report4=&rep24=

Well, I'm not sure I quite get the setup of your db, but have you tried updating with the recordset instead of using sql?
This is untested code, you might need to add logic to make it loop through the database, or other modifications, but I think it should work, since you're only updating the 2 fields with form values. Just an idea.
HTH
Dave

The Database is a prototype assessment tracking system. I am a school teacher. "Sprog" is uk slang for a kid. hence SprogNo. All the report data is displayed in inout boxes so that colleagues can continually update their assessment data. The table is as follows:

SprogTbl
SprogNo, Report, rep2, UPN,
1, a,d,
2 ,b, e,
3, c, f,
4 ,


I tried the rs update . It still didn't do the update. I was wondering does it need some kind of loop for each record to be updated? If so, do you have any ideas how this might be done?

I also tried:

If rs.fields("SprogNo") = request.form ("SprogNo" & sid)

no joy. Again - any and all thoughts appreciated.

Regards,

Chris Hall

You might try using a 2nd connection object to execute your sql. I have never tried executing sql on the same connection that holds an open recordset, perhaps there is some conflict.

Doug, I really do appreciate the time you have spent on this but I have absolutely no idea how to do what you suggest.

Something like this:

If I set up the two connections as you suggest I get the "file already in use" error for the second connection line. I think the problem is the sid variable (it reads and then posts the first value from the first row of the first field. It then reads the second rows value of the second field, and then the third row value of the third field, etc.).


code below:

<html>
<body>
<%ENABLESESSIONSTATE=False %>
<%

Dim DB
Set DB = Server.CreateObject ("ADODB.Connection")
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\Inetpub\wwwroot\test\test2.2.mdb")
Dim RS
'
Set RS = Server.CreateObject ("ADODB.Recordset")
RS.Open "SELECT * FROM SprogTbl", DB




%>
<form action="report-ammend2.42.asp" method="post"name="form">
<table border=1>
<tr><td>Sprog No.</td>
<td>1st report</td>
<td> 2nd report</td></tr>
<%

dim sid
Response.Write RS.Fields("SprogNo") = sid
rs.movefirst

do until rs.EOF=true

for each sid in RS.Fields
'x = sid + 0

%>
<tr><td><input type="hidden" name="SprogNo<% = sid %>" value="<%Response.Write RS.Fields("SprogNo")%>"><% response.write RS.Fields("SprogNo")%></td>
<td><input type="text" name="Report<%= sid %>" value="<%Response.Write RS.Fields("Report")%>"></td>
<td><input type="text" name="rep2<%= sid %>" value="<%Response.Write RS.Fields("rep2")%>"></td></tr>

<%

rs.movenext
'RS.movefirst

'exit for
'exit do
next
loop
'wend
'exit for
%>
</table>
<br>
<input type="submit" value="submit">
<input type="button" value="Cancel" >

</form>

</body>
</html>

It was just a guess, sorry it didn't work.

I'm still not sure if the conflict is with the connection being open, and I don't recall where the default cursor location is for a recordset if you don't specify it.

Maybe try modifying your recordset open code like

Thx Doug. Tried that and got the following error message:

ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/test/report-ammend2.45.asp, line 25

Line 25 being the line:-

RS.CursorLocation = adUseClient

pls could I run a thought by you? In my first working (but useless model), a value of x is appended to the end of the input box name="". If I try to append the number value from the SprogNo field instaed using sid as a variable the POST data is wrong, and what is appended to the end of the names is the first value from the the first field, the next set of names has the whatever is in the second row of the second field. The third set then has the whatever is in the third row of the third field. For whatever reason the sid variable is picking up data from one row after another, but it's moving across a column (i.e. field) each time.

Incorrect Post data:

SprogNo1=1&Report1=a&rep21=d&SprogNob=2&Reportb=b&rep2b=e&SprogNof=3&Reportf=c&rep2f=f&SprogNo=4&Report=&rep2=

Correct (required) POST data:

SprogNo1=1&Report1=a&rep21=d&SprogNo2=2&Report2=b&rep22=e&SprogNo3=3&Report3=c&rep23=f&SprogNo4=&Report4=&rep24=

Table:

SprogTbl
SprogNo Report rep2
1 a d
2 b e
3 c f
4

strangely, if I reduce the number of records to one I get:

Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/test/report2.2.asp



code below:

<html>
<body>
<%ENABLESESSIONSTATE=False %>
<%

Dim DB
Set DB = Server.CreateObject ("ADODB.Connection")
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\Inetpub\wwwroot\test\test2.2.mdb")
Dim RS
'
Set RS = Server.CreateObject ("ADODB.Recordset")
RS.Open "SELECT * FROM SprogTbl", DB




%>
<form action="report-ammend2.42.asp" method="post"name="form">
<table border=1>
<tr><td>Sprog No.</td>
<td>1st report</td>
<td> 2nd report</td></tr>
<%

dim sid
Response.Write RS.Fields("SprogNo") = sid
rs.movefirst

do until rs.EOF=true

for each sid in RS.Fields


%>
<tr><td><input type="hidden" name="SprogNo<% = sid %>" value="<%Response.Write RS.Fields("SprogNo")%>"><% response.write RS.Fields("SprogNo")%></td>
<td><input type="text" name="Report<%= sid %>" value="<%Response.Write RS.Fields("Report")%>"></td>
<td><input type="text" name="rep2<%= sid %>" value="<%Response.Write RS.Fields("rep2")%>"></td></tr>

<%

rs.movenext
'RS.movefirst

'exit for
'exit do
next
loop
'wend
'exit for
%>
</table>
<br>
<input type="submit" value="submit">
<input type="button" value="Cancel" >
<br/>
<br/>
</form>

</body>
</html>