Solved

ADO Disconnected Recordset Problems in ASP

Posted on 2004-03-26
8
1,350 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now