INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Problem with updating database reords.

Problem with updating database reords.

(OP)
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?

RE: Problem with updating database reords.

Quote:

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


Test to ensure the recordset contains at least one record before attempting operations on it.

CODE --> vbscript

RSobject.count > 0 

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.

RE: Problem with updating database reords.

(OP)
"... at least one record ... " => the table Activities contains > 20 records - the table DayActivities > 200

Other errorsulutions ?

RE: Problem with updating database reords.

Chris recommended testing if the recordset had at least one record, not the tables themselves!

Which line of code gives the error?

RE: Problem with updating database reords.

(OP)
I receive an errormessage in ... line 0 ?

Datum :
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

Could this code be the error generator? or is some previous code wrong? (cfr. my first thead at the top)
<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.

RE: Problem with updating database reords.

CODE

strSQL = "SELECT * FROM activity WHERE ActId=" & lngRecordNo
rsFFFFTST.Open strSQL, adoCon
If rsFFFFTST.EOF Then
   response.write "No records found where ActId=" & lngRecordNo
   response.end
End If 

RE: Problem with updating database reords.

Quote:

I receive an errormessage in ... line 0 ?

The error is at 'line 0' because it is not a coding or syntax error. It is an error condition created by a failure of the programmer to anticipate probable error conditions, such as no records being returned by the query.

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.

RE: Problem with updating database reords.

Chris,

I make such mistakes all the time!! And yet, I am always shown the line number that caused the error, 100% of the time. So while I'm sure there is a logical reason why the OP sees "error in line 0" as opposed to the actual line number, it is not as simple as saying "an error condition created by a failure of the programmer to anticipate probable error conditions..."

RE: Problem with updating database reords.

(OP)
Thanks guitarzan.
Result => Error : No records found where ActId=0

Consequently I tested lngRecordNo - cfr. (cfr. my first thead at the top)
response.write (lngRecordNo) gives 0 => I expect a record number => my selection of a/the record may not be correct(?).

(The total range is:
Select ActID from Activity
Display on screen: the corresponding recorddata from table DayActivities =ModuleA
to change/update this data =ModuleA
and overwrite them =ModuleB)

Selection : I tried to populate the dropdownmenu (from my Access database) to select the record 'lngRecordNo' with FrontPagecode in a previous module as follows :

<form name="List" onSubmit="return validatie(this);" METHOD="POST" ACTION="ModuleA.asp" bgcolor="#FFFFAE">

<tr>
<td align="center" height="20" width="669">

<tr>
<td height="20" align="center" width="669">
<p align="center" style="margin-top: 0; margin-bottom: 0"><font face="Arial" size="2" color="#0000FF">Make your choice from the list</font>
</td></tr>
<tr><td height="30" width="669">
<p align="center" bgcolor="#FFFFDD" style="margin-top: 0; margin-bottom: 0"><font face="Arial" size="2" color="#FF6600">

<!--#include file="_fpclass/fpdblib.inc"-->

<%
fp_sQry="SELECT * FROM ACTIVITY ORDER BY ActDate ASC"
....
%>

<select NAME="ActId" style="font-size: 10px;width:600px;">
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<option style="font-family: Arial, Helvetica, sans-serif;color:#0000FF;" value="<%=FP_FieldHTML(fp_rs,"ActId")%>"><%=FP_FieldHTML(fp_rs,"Actcat")&" "&FP_FieldHTML(fp_rs,"ActDate")%></p>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</select>

It is probably better to make this selection also in (pure) ASP classic, to facilitate the transition to lngRecordNo (in the next moduleA) (?)
My problem: I'm not sure about the correct syntax ;-(
Unless the error is elsewhere.

Thank for help.

RE: Problem with updating database reords.

Quote:

Result => Error : No records found where ActId=0

Therefore your SQL query has returned no data, so before you go any further you NEED to fix that problem.

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.

RE: Problem with updating database reords.

Is FP_FieldHTML working as you expect? Look at the HTML your code is rendering, and see if the <OPTION> tags have correct values, and not all 0's.

RE: Problem with updating database reords.

(OP)
If, after the selection of ActId, [METHOD="POST" ACTION="moduleA"] contains a SELECT QUERY in FrontPage code, as ... WHERE members.memberno>=1 and (ActId=::ActId::) ... => it works => ActId is linked to the expected record.

That's why I used also FrontPage code to select the value (ActId) in this UPDATE-application.
Unfortunately, the 'transition' from the selected database-field ActId to the variable lngRecordNo is not successful in this new update-moduleA.

The question is why ?
Is the use of ASP Classic code in ActId's selection perhaps better (than FrontPage) for a correct transition? Or is the problem elsewhere?
Thanks for helptips.


RE: Problem with updating database reords.

The question is why ?
Sorry, I've never used FrontPage so I have no idea.

RE: Problem with updating database reords.

(OP)
Guitarzan, as I mentioned earlier, I want to replace as much as possible the (outdated) FrontPage code in my applications with ASP (classic).
Consequently, I look for an example code (=learn by example) to select 'ActId', and that can easily link the selected 'ActId' to the variable lngRecordNo in the subsequent moduleA.
Is that possible in ASP classic code? (or in which language code for Windows server would you program my hobby application to work?)

Thanks for helptips.

RE: Problem with updating database reords.

Maybe something like this, not tested

CODE

<%
Dim Conn, rs, sql
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open _
   "Driver={Microsoft Access Driver (*.mdb)};" & _
   "DBQ=" & Server.MapPath("/") & "\..\data\MyDatabaseName.mdb" & "" 
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM ACTIVITY ORDER BY ActDate ASC"
rs.Open sql, Conn
%><select NAME="ActId" style="font-size: 10px;width:600px;"><%
Do While Not rs.EOF
   %><option style="font-family: Arial, Helvetica, sans-serif;color:#0000FF;" value="<%=rs("ActId")%>"><%=rs("Actcat")&" "&rs("ActDate")%></option><%
   rs.MoveNext
Loop
%></select><%
rs.Close
%> 

RE: Problem with updating database reords.

(OP)
Thanks Guitarzan, with your code I can also populate the dropdown box from table Activity,
but there is no link to moduleA => "No records found where ActId=0" (in my test ActId should be 18).

Immediately below your code, I have entered the "input" code as follows => correct ?

<INPUT TYPE="reset" VALUE="Reset">
<INPUT TYPE="submit" VALUE="Press to update">
</TD></TR></form>

The ASP code used for moduleA has not changed and is shown in my first thread dd. 29/07/2017 (as module 2)

I try to eliminate what can go wrong
- ActId = numeric field in Activity = OK
- Link with database and table Activity = OK (box = filled in)
- input code ?
_ ...

Other tips ?
Thanks

RE: Problem with updating database reords.

lngRecordNo = CLng(Request.QueryString("ActId"))

Try:
lngRecordNo = CLng(Request.Form("ActId"))

RE: Problem with updating database reords.

(OP)
Guitarzan, thank you for the magic word 'form'!

I am almost there winky smile
With these select query : strSQL = "SELECT * FROM activity WHERE ActId=" & lngRecordNo
I can now display for update the fields of the table Activity (= testcase).

My final intention is to update a field of the sub-table DayActivity.
This sub-table contains for each player (= MemNo) the field (= Partic) that indicates if the player Yes or No participates in that particular activity (= ActId)
The link between the two tables : ActId (in Activity) = ActDayId (in DayActivity for each MemNo)

Suppose I selected an ActID (from activity) for player number 25 (MemNo = 25 = e.g.).
To display (for UPDATE) the field DayActivity.PART for Activity.ActID, can I replace the select query (mentioned above) with

strSQL = "SELECT * FROM DayActivity Inner JOIN DayActivity ON activity.actID=DayActivity.actDayId) WHERE ActId=" & lngRecordNo and MemNo=25

Thanks for helptips.

RE: Problem with updating database reords.

Not sure I fully understand your question, but it sounds like you want something like this? (I'm just guessing that Partic is a Yes/No field in Access):

CODE

strSQL = "UPDATE DayActivity SET Partic = True WHERE ActId = " & lngRecordNo & " AND MemNo = " & lngMemNo
Conn.Execute strSQL 

RE: Problem with updating database reords.

(OP)
I try to better describe my search.

2 specific tables are used
- Activity = all the matches (ActId= match1,match2,3,4 ....)
- DayActivity = the 'participation' (= field Partic) for all member/players (= field MemNo = Plzayer 1,2,3,4 ...) and for each match (= field ActAnId = link to Activity.ActId)

I want to update the text field Partic from the DayActivity table.

This update-application consists (will consist !) of 3 sub-modules

(1) In Module1 a certain match is selected from the Activity table (= ActId) => OK

(2a) Module2 : should display 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: Yes, No, Ill, Holidays, ...)
(2c) The (changed) data will be sent (via method="post") to the next module

(3) In this final module3, the changed content is restored in the appropriate Partic field (as update)

My problem is now to find the correct SELECT QUERY in ASP classic to show some fields, especially the Partic field, from the DayActivity record, that corresponds with the selected ACTId.
In this query, an example player's identity field can be provisionally determined as MemNo=25
In the previous thread I was making a query with 'JOIN between the 2 tables' without success.

I want to get off of FP, but the correct syntax for that query in ASP is still not easy.
If that query goes on, I hope to continue.

Thanks.

RE: Problem with updating database reords.

(OP)
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
If rsFFFFTST.EOF Then
response.write "No records found where ActAnId=" & lngRecordNo & " and MemNo=25 "
response.end
End If

'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.

RE: Problem with updating database reords.

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

CODE

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

'yellow code
If rsUpdateEntry.EOF Then
response.write "No records found where ActAnId=" & lngRecordNo & " and MemNo=25 "
response.end
End If 

RE: Problem with updating database reords.


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:

CODE

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

RE: Problem with updating database reords.

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?

RE: Problem with updating database reords.

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?

RE: Problem with updating database reords.

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.

RE: Problem with updating database reords.

(OP)
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.

RE: Problem with updating database reords.

Module3 has this line:
lngRecordNo = CLng(Request.Form("ActAnId"))

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.

RE: Problem with updating database reords.

(OP)
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.

RE: Problem with updating database reords.

OK, so now in Module2 you are putting rsFFFFFTST("AnwId") 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.

RE: Problem with updating database reords.

(OP)
'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.

RE: Problem with updating database reords.

(OP)
By trial and error, I found this working syntax :

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

unless it can be further refined?

Thanks.




Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close