Problem with updating database reords.

[leifoet]

I wish to update some record fields from table DayActivites e.g. the field PLAY (yes or no)
with the following 3 ASP modules
Module 1 : select a record from table Activities
Module 2 : using the selected record from 1 => select the corresponding record in the table DayActivities + make the update(s)
Module 3 : finish update the table DayActivities

However, when I want to display the first (fixed) field (DATE) from the selected record, I does not read the date but the following error :

DATE : ADODB.Field error '80020009'
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/STV_aanw_updA.asp, line 0

In this module 2, I use the following code to open the database
<%
'Dimension variables
Dim adoCon '=> Holds the Database Connection Object
Dim rsFFFFTST '=> Holds the recordset for the record to be updated
Dim strSQL '=> Holds the SQL query for the database
Dim lngRecordNo '=> Holds the record number to be updated

'Read in the record number to be updated
lngRecordNo = CLng(Request.QueryString("ActId"))

'=> In the immediately preceding module 1 : the ACTID field (number) is selected from the table Activities with this FrontPage code : value="<%=FP_FieldHTML(fp_rs,"ActId")%>">.
'=> This field ACTID is linked (should be) to the field ACTANID in the table DayActivities, to change the corresponding record

'=> I tested the variable lngRecordNo => response.write (lngRecordNo) gives 0 => I expect a record number ?

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("_private/FFFF.mdb")

'Create an ADO recordset object
Set rsFFFFTST = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM activity WHERE ActId=" & lngRecordNo

'Open the recordset with the SQL query
rsFFFFTST.Open strSQL, adoCon
%>

The code for DATE : (the error generator ?)
<tr>
<td>
<b>Datum</b></td>

<td width="377"><font color="red"><b><%=rsFFFFTST("DayActivities.ActDat")%><font color="black"></b>
<input type="hidden" name="Club" value="<% = rsFFFFTST("DayActivities.ActDat") %>">
</td>
</tr>

Thanks for help tips.

(As stated, only Module 1 = FrontPage code - I could also change the FrontPage code of module1 into (pure) ASP code to select a (start) field - My problem: what is the correct code ?)
Unless this would not be enough to solve the problem?

 

[leifoet]

With try and error I found a working select query for my module2 :
StrSQL = "SELECT * FROM DayActivity INNER JOIN Activity ON Activity.actID=DayActivity.actAnId WHERE ActId=" & lngRecordNo & " and MemNo=25 "

The screen now shows the existing content of the text field 'Partic' from the DayActivity table, and the ability to change the content of this field.
But the storage (field update) in the last module3 does not work (see code below).
=> my first error was :
ADODB.Recordset error '800a0bcd'
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

=> After insertion of the yellow code (from Guitarzan - thread 3 Aug 17 16:22) - the error is :
Microsoft VBScript runtime error '800a01a8' - Object required: 'rsFFFFTST'

Code used in the Update Module3

'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsUpdateEntry 'Holds the recordset for the record to be updated
Dim strSQL 'Holds the SQL query for the database
Dim lngRecordNo 'Holds the record number to be updated

'Read in the record number to be updated
lngRecordNo = CLng(Request.Form("ActAnId")) '=> the correct field ?

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("_private/FFFF.mdb")

'Create an ADO recordset object
Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT DayActivity.* FROM DayActivity WHERE ActAnId=" & lngRecordNo & " and MemNo=25 "

'StrSQL = "SELECT * FROM DayActivity INNER JOIN Activity ON Activity.actID=DayActivity.actAnId WHERE ActId=" & lngRecordNo & " and MemNo=25 "

'Set the cursor type we are using so we can navigate through the recordset
rsUpdateEntry.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsUpdateEntry.LockType = 3

'Open the tblComments table using the SQL query held in the strSQL varaiable
rsUpdateEntry.Open strSQL, adoCon

'yellow code
[highlight #FCE94F]If rsFFFFTST.EOF Then
response.write "No records found where ActAnId=" & lngRecordNo & " and MemNo=25 "
response.end
End If

[/highlight]'Update the record in the recordset
rsUpdateEntry.Fields("Partic") = Request.Form("Partic")
rsUpdateEntry.Fields("Partic_AB") = Request.Form("Partic_AB")

'Write the updated recordset to the database
rsUpdateEntry.Update

'Reset server objects
rsUpdateEntry.Close
Set rsUpdateEntry = Nothing
Set adoCon = Nothing


Problem: Why does this module3 not work?
 
An error in the syntax / code ?
Is the location of the yellow code sometimes incorrect?
Possibly the update line is not found - is my SELECT Query correct here?
Or is it not the right query at all?

Thanks for helptips.

 

[guitarzan]

You are using a different name for the recordset in this module.

'Open the tblComments table using the SQL query held in the strSQL varaiable
rsUpdateEntry.Open strSQL, adoCon

'yellow code
If [highlight #73D216]rsUpdateEntry[/highlight].EOF Then
response.write "No records found where ActAnId=" & lngRecordNo & " and MemNo=25 "
response.end
End If

 

[MarkSweetland]

Your ADODB recordset opens as a read-only (adLockReadOnly) recordset as default. You need to open with additional parameters in order to make it an updatable recordset:

rsUpdateEntry.Open strSQL, adoCon, adOpenDynamic, adLockOptimistic

Check it out here: W3 Schools


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[guitarzan]

MarkSweetland: I use ASP classic / Access like the OP, and I never set that parameter and I can update. What error that OP gets makes you think that is the issue?

 

[guitarzan]

On second thought, I use SQL "UPDATE table SET field = value" etc... commands to make updates, which is not the same as what the OP is doing... But still I dont see the error that OP is getting that points to a read only recordset causing a problem?

 

[ChrisHirst]

Actually it maybe the 'CursorLocation' rather than the LockType because RecordCount always returns of record count of 0 regardless of how many records there are unless you specify adUseClient for the CursorLocation.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

 

[leifoet]

Guitarzan : after correcting the 'yellow code' I get this message => Norecords found where ActAnId=0 and MemNo=25

In my test sample, I expect ActAnId = 18 because in module1 I selected ActId = 18 from table Activity
(Module2 selects the corresponding record from table DayActivity for the field ActAnId (= ActId) and/where Memno = 25, and displays the Partic field.)

My problem : Module3 should update the changes in the Partic field, in the same record (as selected in Module2).
Why does this not happen in module3 or why ActAnId = 0 ?

Is/are the (test) queries wrong? (or is there a problem with the remaing code - cfr. thread 14 Aug 17 15:46)
'Initialise the strSQL variable with an SQL statement to query the database
'strSQL = "SELECT DayActivity.* FROM DayActivity WHERE ActAnId=" & lngRecordNo & " and MemNo=25"
StrSQL = "SELECT * FROM DayActivity INNER JOIN Activity ON Activity.actID=DayActivity.actAnId WHERE ActId=" & lngRecordNo & " and MemNo=25"

I also tested the query with my sample record as follows
'strSQL = "SELECT DayActivity.* FROM DayActivity WHERE ActAnId=18 and MemNo=25 " (ActAnId and MemNo are both numeric fields)
=> error message : ADODB.Recordset error '800a0bb9' / Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
The cited line in the error message refers to
'Set the lock type so that the record is locked by ADO when it is updated
rsUpdateEntry.LockType = 3 (cfr. thread 14 Aug 17 15:46)

Thanks for help.

 

[guitarzan]

Module3 has this line:
[highlight #FCE94F]lngRecordNo = CLng(Request.Form("ActAnId"))[/highlight]

So, you are trying to get the value of "ActAnId" from the module it was submitted from (containing <form></form> tags I hope?)... But it's empty (zero). So I forget, how did you get to module3? What form gets submitted that has ACTION="module3" ? That's where the problem is, I think.

 

[leifoet]

The 'transition' from Module2 to Module3 :
<form name="form17" method="post" action="Module3.asp">
(The initial name was 'Form' - but I changed it to 'Form17')
Code = correct I think ?

In the meantime, I have tried to run Module3 with the field AnwId (= recno in table DayActivity => ActAnId + MemNo together => better than ActAnId) :

<input type="hidden" name="Rec" value="<% = rsFFFFFTST("AnwId") %>"></td></tr>
<tr><td><input TYPE="reset" NAME="fp_reset" value="Reset"></td>
<td width="377"><input type="submit" name="Submit" value="New data"></td></tr>
​

I thought that I could solve the problem lngRecordNo = CLng(Request.Form("AnWId")) with this input-code - not so - the question is why?

Is the abbreviation 'CLng' for numeric fields (AnWId, ActAnID) OK?

The bit of good news is that the update query works - through your query-suggestions - but without the variables from request.form():
StrSQL = "UPDATE DayActivity SET Partic = '" & Request.Form("Partic") & "' WHERE AnwId=216 " (=> as the recno where ActAnId=18 and MemNo=25)

Thanks for helptips.

 

[guitarzan]

OK, so now in Module2 you are putting [highlight #FCE94F]rsFFFFFTST("AnwId")[/highlight] into a hidden variable named "Rec", so, in Module3 you would have to request that variable name to make it work.
lngRecordNo = CLng(Request.Form("Rec"))

CLng() just converts a value to a long integer.

 

[leifoet]

'Rec' was my missing link between M2 and M3 - thank you Guitarzan for this tip.

May I still ask for a solution to this detail problem.

(2a) Module2 : should display (in a drop-down box) the existing content of the text field 'Partic' from the DayActivity table
(2b) If incorrect, the user must be able to change this text field (as e.g. : Plays, Apologized, Ill, Holidays, ...)

How can I make 'the existing content' conditional, with 'If - Else', in combination with the field content ?
My ASP Classic syntax does not work - is probably not correct

<td>
<SELECT name="Partic" style="....">
<OPTION SELECTED value="<%=rsFFFFTST("Partic")%>" style="...">

<% if value="rsFFFFTST('Partic')='PL'" then
response.write("Plays")
elseif value="rsFFFFTST('Partic')='AP'" then
response.write("Apologized")
elseif value="rsFFFFTST('Partic')='HO'" then
response.write("Holidays")
elseif ...
else
response.write("...")
end if%>

<%
'=rsFFFFTST("Partic")%>
</option>

<option value="PL" style="...">Plays</option>
<option value="AP" style="...">Apologized</option>
<option value="HO" style="...">Holidays</option>
...
</select><br>
</td></tr>

Thanks for helptips.

 

[leifoet]

By trial and error, I found this working syntax :

<% if "PL"=rsFFFFTST("Partic") then ...

unless it can be further refined?

Thanks.