ADO Disconnected Recordset Problems in ASP

I am experiencing problems creating an ADO disconnected recordset, adding a record to this rs, and then assigning a value to only
one of the fields via an ASP page.   The record consists of 4 fields of which 3 work fine.   The only differences I can see between
field 3 (which fails the value assignment) - 'intCount' is that this is a summary count field, and it's data type is #2 - a 2-byte signed i
nteger.  Field 4 - 'intReportSort' accepts the value assignment statement and is of type 17 - a 1-byte unsigned integer.    
Environment is: Windows 2000 Enterprise and IIS 5.0.    Code with comments is attached.  This also contains the ASP error generated.  
I tried capturing the ADO error collection however couldn't find a way since the Connection object is obviously closed on a
disconnected recordset.  I need help figuring out how to track the ADO errors, and why intCount won't accept the simple value
assignment.

Thanks,  Al

// Actual ASP code follows,  Error included way at the bottom

dim objConn2
set objConn2 = Server.CreateObject("ADODB.Connection")
objConn2.Provider = "SQLOLEDB"

objConn2.ConnectionString = "Uid=XXX;" & _
                        "Pwd=XXXXX;" & _      
                        "Data Source=XXXXXXX;" & _
                        "Initial Catalog=AccessMeasures"
                                          
objConn2.Open

strSQL = "SELECT TOP 100 PERCENT CAST(YEAR(dbo.tblAppts.dtmApptDateTime) AS char(4)) " & _
           "+ '-' + dbo.fncConvNumMonthTo2DigitStringRep(MONTH(dbo.tblAppts.dtmApptDateTime)) AS strYearDate, dbo.tblAccessEvalCodes.strDescript, " & _
             "CAST(COUNT(dbo.tblAppts.intApptPK) AS smallint) AS intCount,dbo.tblAccessEvalCodes.intReportSort " & _
             "FROM dbo.tblAppts INNER JOIN " & _
             "dbo.tblAccessEvalCodes ON dbo.tblAppts.intAccessEvalCode = dbo.tblAccessEvalCodes.intAccessEvalCode INNER JOIN " & _
             "dbo.tblMTFs ON dbo.tblAppts.intDMISID = dbo.tblMTFs.intDMISID AND dbo.tblAppts.strMEPRS3 = dbo.tblMTFs.strMEPRS3 " & _
             "WHERE (dbo.tblAppts.intDMISID = " & intDMIS & ") AND (dbo.tblAppts.strProviderFlag = 'P') AND " & _
             "(dbo.tblAppts.dtmApptDateTime >= '" & dtmStartDate & "') AND (dbo.tblMTFs.blnClinicOpenAccess = 1) AND (dbo.tblAppts.strMEPRS3 = '" & strpMEPRS3 & "') " & _
             "GROUP BY CAST(YEAR(dbo.tblAppts.dtmApptDateTime) AS char(4)) + '-' + dbo.fncConvNumMonthTo2DigitStringRep(MONTH(dbo.tblAppts.dtmApptDateTime)), " & _
         "dbo.tblAccessEvalCodes.strDescript, dbo.tblAccessEvalCodes.intIncludeInReportNum, dbo.tblAccessEvalCodes.intReportSort " & _
             "HAVING (dbo.tblAccessEvalCodes.intIncludeInReportNum = 1) " & _
           "ORDER BY CAST(YEAR(dbo.tblAppts.dtmApptDateTime) AS char(4)) + '-' + dbo.fncConvNumMonthTo2DigitStringRep(MONTH(dbo.tblAppts.dtmApptDateTime)), " & _
             "dbo.tblAccessEvalCodes.intReportSort "
             
set objRS2 = Server.CreateObject("ADODB.Recordset")            
objRS2.ActiveConnection = objConn2
objRS2.source = strSQL
objRS2.CursorLocation = adUseClient
objRS2.CursorType = adOpenStatic
objRS2.LockType = adLockOptimistic
objRS2.Open
' disconnect recordset
objRS2.ActiveConnection = Nothing
objConn2.Close

' above code works, disconnected recordset established, when uncommented following code 'echos the rs.field(0) to the web page
'Do While NOT objRS2.EOF    ' when uncommented this works, shows connection is being established
'      response.write objRS2.Fields(0) & "<br>"
'      objRS2.movenext
'Loop

' ***** actual recordset returned, ran strSQL from above in SQL Query Analyzer
' field names - strYearDate, strDescript, intCount, intReportSort
'      2003-07      Seen Same Day      193      1
'      2003-07      Seen within 24 hour      13      2
'      2003-07      Bad Backlog      49      5
'      2003-08      Seen Same Day      189      1
'      2003-08      Seen within 24 hour      1      2
'      2003-08      Good Backlog      1      4
'      2003-08      Bad Backlog      100      5
'      2003-09      Seen Same Day      251      1
'      2003-09      Seen within 24 hour      2      2
'      2003-09      Bad Backlog      79      5
'      2003-10      Seen Same Day      223      1
'      2003-10      Seen within 24 hour      17      2
'      2003-10      Good Backlog      1      4
'      2003-10      Bad Backlog      124      5

' testing the status of field 3 in the recordset
response.write("Field intCount status " & objRS2.Fields(2).Status & "<br>")  ' this returns 0

' testing the type of field 3 in the recordset
response.write "Field intCount Type " & objRS2.Fields(2).Type & "<br>"  ' this returns 2

' testing the type of field 4 in the recordset
response.write "Field intReportSort Type " & objRS2.Fields(3).Type & "<br>"   ' this returns 17

' testing the byte size of field 3 in the recordset
response.write "Field intCount Actual Size " & objRS2.Fields(2).ActualSize & "<br>"  ' this returns 2

With objRS2
      .AddNew
      .Fields(0).Value = "2000-99"
      .Fields(1).Value = "Good Backlog"

      ' #### Following Assignment Fails ###########################
      .Fields(2).Value = 2       ' This is line # 196 referenced in the error
      ' ################################################

      ' this assignment works, I've testeed this by commenting out above line and  
             ' performing the .Update below
      .Fields(3).Value = 7
      
      ' When I have the On Error Resume Next Statement at the top of the page following  
             'doesn't show an error message.  Currently ON Error Resume Next removed.
      If Err.number <> 0 then
            response.write "error occurred"
            response.end()
      End If
      .Update
End With

response.end()


' ##################### Screen Capture of the ASP error returned on the web page ####

'Microsoft Cursor Engine error '80040e21'

'Multiple-step operation generated errors. Check each status value.

'/metrics/openaccess2/0020_OAMetric1.asp, line 217

' ##################################################################






petrmicaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SlimshaneeyCommented:
Try puttin Cint("2") as the assignment instead of the 2 on its own... Just curiosity...
0
JNSTAUBCommented:
i don't understand why you want to update a read only field, if you want to modify this field ,compute the count in asp and store the result in a numeric field,in the database but never update a computed field!!
0
SlimshaneeyCommented:
Ah, you are Casting from one data type in the query to an integer. You cannont then update to integer because the datatype is not integer in the database structure.  That is the problem I think.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

petrmicaAuthor Commented:
1. I have already tried doing Cint("2")  and that didn't work.
2. I want to create a disconnected RS, add a record to that and then assign values to the fields.  The reason I am
   doing this is -> I always need 4 groupings per month - a. Seen Same Day b. Seen within 24 hour
   c. Good Backlog and d. Bad Backlog.    This rs is then passed to a charting activex object which builds a
   dynamic chart.    If you notice the first month only comes up with 3 groupings and this throws the charting
   tool.

   My solution was going to be:   generate the recordset, disconnect it, read the first 4 rows, if one of the
   groupings is missing then do .AddNew and Assign the correct values - intCount would be zero 0 in this
   case.  Finally pass the new corrected rs to the charting tool.
 
   I only need to read the summary rs and make sure it is good (4 groupings).  I don't need to update the
   source tables at all.

3. When I first started I didn't cast the summary result at all.   I then received a type 3 - 4-byte signed integer
    result.   Since I had trouble doing the value.assign I tried the cast to see if it was possibly a issue with the
    way I formatted the assigned number in .Fields(2).Value = 2 .   I thought if I cast it a shorter int type
    then I could experiment with .Fields(2).Value = 0002   or .Fields(2).Value = +00002 in various combos.
    I reviewed a WROX reference book last night and found formatting was not a issue.   I can remove the
    cast and still have the same problem.
 
    Since this is a disconnected rs I (with no intent of updating the source tables) I don't think this matters.

0
SlimshaneeyCommented:
Yeah, but you also have a Count in the cast, which makes that col read only. You cannot update a recordset col with Count in its seelect, or any other type of computational processing
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SlimshaneeyCommented:
JNSTAUB already mentioned that earlier too...
0
petrmicaAuthor Commented:
Ok - you say I can't update an integer field in disconnected recordset because it came from a computation.  Can you
tell why this matters since the recordset is in a disconnected state.    Is this a property assigned to the field?   If I
clone the recordset would it also have the same properties associated with the computed field?  

Before I give away the points can you suggest other methods of accomplishing the recordset validation to ensure
I always have the 4 groupings in the first month's worth of data.

0
JNSTAUBCommented:
easy create an array with 4 rows and 4 collumns, initialise it and fill it with your recordset then pass it to your activeX.you will be sure to transfer four rows.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.