Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ADO Disconnected Recordset Problems in ASP

Posted on 2004-03-26
8
1,352 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

808 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