Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ADO Disconnected Recordset Problems in ASP

Posted on 2004-03-26
8
Medium Priority
?
1,360 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
[X]
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
  • 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 450 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
Technology Partners: 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 300 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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 informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

604 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