Writing back to a ADO recordset that was created with a UNION

To All

I have a SQL query that creates a UNION between two tables .. when I
try to write back to the recordset I get :

Error ADODB. Recordset:FIELDS /0 S_OK: _VALUE

Here is the recordset query

cSQL := "SELECT proj.PROJECTEID, proj.PROJNAME, proj.DESCRIP,
proj.START_DATE, proj.END_DATE, "
cSQL += "proj.EST_BUDGET, proj.FUNDED, proj.USEREID,
proj.PROJMGR, proj.REPORTING, "
cSQL += "proj.FREQUENCYEID, proj.FREQUENCY, proj.ROW_COLOR,
proj.EMAIL, "
cSQL += "proj.ACTIVE, proj.CONTRACT, proj.ENTRYBY,
proj.ENTRYDATE, proj.CREATEDBY, "
cSQL += "proj.CREATEDATE, proj.UPDATED "
cSQL += "FROM PROJECT proj INNER JOIN P_SPONSOR spon ON
proj.PROJECTEID = spon.PROJECTEID "
cSQL += "WHERE (((spon.SPONSORID)='"+xLOGIN+"')) "
cSQL += "UNION "
cSQL += "SELECT proj.PROJECTEID, proj.PROJNAME,
proj.DESCRIP, proj.START_DATE, proj.END_DATE, "
cSQL += "proj.EST_BUDGET, proj.FUNDED, proj.USEREID,
proj.PROJMGR, proj.REPORTING, "
cSQL += "proj.FREQUENCYEID, proj.FREQUENCY, proj.ROW_COLOR,
proj.EMAIL, "
cSQL += "proj.ACTIVE, proj.CONTRACT, proj.ENTRYBY,
proj.ENTRYDATE, proj.CREATEDBY, "
cSQL += "proj.CREATEDATE, proj.UPDATED "
cSQL += "FROM PROJECT proj WHERE proj.PROJMGR = '"+xLOGIN
+"' order by proj.projname"


I can extract values from the recordset with :

cPROJNAME := if(empty(oRsProj:Fields("projname"):Value),
space(30), oRsProj:Fields("projname"):Value )

But I can not write back to the recordset .. and here is where i get
the error :

oRsProj:Fields("projname"):Value := cPROJNAME


It definitly has to do with the way the recordset was created .. it is
possible to write back to the UNION recordset ??

Rick Lipkin

To All

As I have just been made aware of .. any SQL Union creates a recordset
( regardless ) of how you open it as READ ONLY.. so the error I got
certainly reflected that confirmation.

Thanks
Rick Lipkin


On Aug 7, 12:12*pm, Rick Lipkin <lipkinrm29...@yahoo.com> wrote:
> To All
>
> I have a SQL query that creates a UNION between two tables .. when I
> try to write back to the recordset I get :
>
> Error ADODB. Recordset:FIELDS /0 *S_OK: _VALUE
>
> Here is the recordset query
>
> *cSQL := "SELECT proj.PROJECTEID, proj.PROJNAME, proj.DESCRIP,
> proj.START_DATE, proj.END_DATE, "
> * * * * * *cSQL += "proj.EST_BUDGET, proj.FUNDED, proj.USEREID,
> proj.PROJMGR, proj.REPORTING, "
> * * * * * *cSQL += "proj.FREQUENCYEID, proj.FREQUENCY, proj..ROW_COLOR,
> proj.EMAIL, "
> * * * * * *cSQL += "proj.ACTIVE, proj.CONTRACT, proj.ENTRYBY,
> proj.ENTRYDATE, proj.CREATEDBY, "
> * * * * * *cSQL += "proj.CREATEDATE, proj.UPDATED "
> * * * * * *cSQL += "FROM PROJECT proj INNER JOIN P_SPONSOR spon ON
> proj.PROJECTEID = spon.PROJECTEID "
> * * * * * *cSQL += "WHERE (((spon.SPONSORID)='"+xLOGIN+"')) "
> * * * * * *cSQL += "UNION "
> * * * * * *cSQL += "SELECT proj.PROJECTEID, proj.PROJNAME,
> proj.DESCRIP, proj.START_DATE, proj.END_DATE, "
> * * * * * *cSQL += "proj.EST_BUDGET, proj.FUNDED, proj.USEREID,
> proj.PROJMGR, proj.REPORTING, "
> * * * * * *cSQL += "proj.FREQUENCYEID, proj.FREQUENCY, proj..ROW_COLOR,
> proj.EMAIL, "
> * * * * * *cSQL += "proj.ACTIVE, proj.CONTRACT, proj.ENTRYBY,
> proj.ENTRYDATE, proj.CREATEDBY, "
> * * * * * *cSQL += "proj.CREATEDATE, proj.UPDATED "
> * * * * * *cSQL += "FROM PROJECT proj WHERE proj.PROJMGR = '"+xLOGIN
> +"' order by proj.projname"
>
> I can extract values from the recordset with :
>
> cPROJNAME * * *:= if(empty(oRsProj:Fields("projname"):Value),
> space(30), oRsProj:Fields("projname"):Value )
>
> But I can not write back to the recordset .. and here is where i get
> the error :
>
> oRsProj:Fields("projname"):Value * * := *cPROJNAME
>
> It definitly has to do with the way the recordset was created .. it is
> possible to write back to the UNION recordset ??
>
> Rick Lipkin