Solved

ADO Disconnected Recordset Problems in ASP

Posted on 2004-03-26
8
1,353 Views
Last Modified: 2012-05-05
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

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






0
Comment
Question by:petrmica
  • 4
  • 2
  • 2
8 Comments
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 10685586
Try puttin Cint("2") as the assignment instead of the 2 on its own... Just curiosity...
0
 
LVL 4

Assisted Solution

by:JNSTAUB
JNSTAUB earned 150 total points
ID: 10685625
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
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 10685629
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:petrmica
ID: 10685760
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
 
LVL 11

Accepted Solution

by:
Slimshaneey earned 100 total points
ID: 10685779
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
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 10685784
JNSTAUB already mentioned that earlier too...
0
 

Author Comment

by:petrmica
ID: 10686561
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
 
LVL 4

Expert Comment

by:JNSTAUB
ID: 10686699
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

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question