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?

 

[ChrisHirst]

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.

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.

 

[leifoet]

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

Other errorsulutions ?

 

[guitarzan]

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

Which line of code gives the error?

 

[leifoet]

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.

 

[guitarzan]

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

 

[ChrisHirst]

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.

 

[guitarzan]

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

 

[leifoet]

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.

 

[ChrisHirst]

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.

 

[guitarzan]

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.

 

[leifoet]

If, after the selection of ActId, [METHOD="POST" ACTION="[b]moduleA[/b]"] 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.

 

[guitarzan]

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

 

[leifoet]

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.

 

[guitarzan]

Maybe something like this, not tested

<%
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
%>

 

[leifoet]

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

 

[guitarzan]

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

Try:
lngRecordNo = CLng(Request.[highlight #FCE94F]Form[/highlight]("ActId"))

 

[leifoet]

Guitarzan, thank you for the magic word 'form'!

I am almost there ;-)
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.

 

[guitarzan]

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

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

 

[leifoet]

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.