You were almost there. I see that your answer made sense. I made it work by using an unused field as what I wanted, keeping that field's original contents safe anyway as I am not updating the database, just writing a report.

Thanks.


>Alex,
>
>The connection and command object's Execute method return a readonly recordset by default. Use the Open method with the cursortype set to adOpenStatic and the locktype set to adLockBatchOptimistic to get an RS that you can modify.
>
>A trick I have used in the past is adding an extra field to the SQL statement that is based on a real table field but with a different name. This way when it comes back it has the necessary attributes for allowing updates. So, to your SQL add something like: "myTable.myRealField As myDummyField" and you will be able to update the field. Make sure that the data you want to put in fits the field size.
>
>Once you add the extra field (and assuming this is an ASP VBScript) do:
>

Set oRS = Server.CreateObject("ADODB.Recordset")
>oRS.Open cSQLHist, oNNSS, 3, 4

>HTH
>>Vin:
>>
>>I'm not sure the length is an issue here.
>>JCrescencio: yes it was a message typo
>>
>>I added the ADODB.Fields idea and took out the fake field
>>from the query but now get a new error.
>>
>>Here is the code that gets me the error
>>

>>' oNNSS is the connection:
>>Set oNNSS = Server.CreateObject("ADODB.Connection")
>>oNNSS.CursorLocation = adUseClient
>>
>>' connection string is read from globa.asa
>>oNNSS.ConnectionString = Application("NationalNet_ConnectionString")
>>
>>oNNSS.Mode = adModeReadWrite
>>oNNSS.Open
>>
>>'(this is simplified from the original query -- less fields)
>>' at this point cFrom and cTO are valid dates.
>>' query returns proper record set (e.g. query is fine)
>>' but we need to then add country name from another database
>>' countries (and SRE and other info is already
>>' copied from a recordset on another DB
>>' to array aCountries using GetRows()
>>cSQLHist = "select sre, bid, " & _
>>      "CRAmount AS Credit, " & _
>>      "DBAmount AS Debit " & _
>>  "from NSHIST " & _
>>  "where ProcDate between '" & cFrom & "' and '" & cTo & "'"
>>
>>Set oRS = oNNSS.Execute (cSQLHist)
>>If oRS.EOF Then
>>  ' message user here
>>  ' clean up
>>  Response.End
>>end if
>>
>>oRS.Fields.Append "Country",adChar,12        'ERROR
>>

>>
>>Error returned on the oRS.Fields.Append line is:
>>ADODB.Fields error '800a0c93'
>>The operation requested by the application is not allowed in this context.
>>
>>The following is the original code portion with the previous error:
>>

>>' find country name in array and populate column in report's record set
>>' basically faking a sql join between two recordsets
>>oRS.MoveFirst()
>>Do While Not oRS.EOF
>>  for i = 0 to UBound(aCountries,2)
>>    if trim(aCountries(1,i)) = trim(oRS.Fields("SRE").Value) Then
>>      oRS.Fields("Country").Value = aCountries(4,i)    'ERROR
>>      exit for
>>    end if
>>  next
>>Loop
>>

>>
>>The Country value assignment returns "Errors found" w/o specifying the error(s)
>>
>>I am not an expert in ADO syntax so I'm sure I'm missing something simple here.
>>All I need is to add the extra field to the existing recordset and populate it
>>with the proper country name for the report.
>>
>>Any ideas?
>>
>>
>>>Hi Alex. The length of the field in RS1 is not correct -- you are trying to store a value that is too long. ADO generates an error in this case, rather than simply truncating. Set the empty country field to a string of the proper length for the field.
>>>
>>>BTW, if you are using a disconnected recordset, in ADO 2.0 or above, you don't need the "as country" fake-out... just use the field collection's append command (RS.Fields.Append "Country", adChar, XX) to create a new field, then throw your data in there.