Solved

Classic ASP SQL Server error

Posted on 2009-05-13
5
265 Views
Last Modified: 2012-05-06
Error message is Microsoft OLE DB Provider for SQL Server error '80040e21' The requested properties cannot be supported.

I am trying to get this code to run on my new dev environment.  If I comment out lines 4,5 and 6, the code works.  This code does work on the production box.  I have tried updating MDAC.  My sql server driver is version 2000.86.3959.00 which is the same as production.   I can't comment out the lines because there are hundreds of other lines of code similar to this.



Set RSPortalProgrammer = Server.CreateObject("ADODB.Recordset")
RSPortalProgrammer.ActiveConnection = MM_PortalDBConn_STRING
RSPortalProgrammer.Source = "SELECT * FROM tblNames"
RSPortalProgrammer.CursorType = 0
RSPortalProgrammer.CursorLocation = 2
RSPortalProgrammer.LockType = 3
RSPortalProgrammer.Open()

Open in new window

0
Comment
Question by:timothyrmyers
[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
5 Comments
 
LVL 5

Expert Comment

by:Aanvik
ID: 24380951
Try this,
RSPortalProgrammer.CursorLocation = adUseClient
RSPortalProgrammer.CursorType = adOpenStatic
RSPortalProgrammer.LockType = adLockBatchOptimistic

Open in new window

0
 
LVL 10

Accepted Solution

by:
Banthor earned 500 total points
ID: 24380992
Better yet still
Create a sub for all database connections and fix it once.
EXtensibility & Encapsulation
The above code anticipates the enumerations are available, I will attach my Include.
For best performance the values for CursorType and LockType should be dependent the current use.
I only use Read forward only for recordsets on my sites and have excellent performance far better than ASP.NET sites.
RSPortalProgrammer.CursorLocation = 2  ' adUseServer
RSPortalProgrammer.CursorType = 0 'adOpenForwardOnly
RSPortalProgrammer.LockType = 1 'adLockReadOnly

adodb-enumerations.txt
0
 

Author Comment

by:timothyrmyers
ID: 24381139
This is what i got with the first solution:

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
0
 
LVL 10

Expert Comment

by:Banthor
ID: 24381420
The First Solution will work if you Include My attached File.
adodb-enumerations.txt
They convert the Enumerations in the Actual Values.
 
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24384807
This does not make sense.  Those attributes (UseServer, ForwardOnly, ReadOnly) should be the default.  What happens when you try it this way (on a temporary basis for testing purposes only):

Set cn = Server.CreateObject("ADODB.Connection")
cn.ConnectionString = MM_PortalDBConn_STRING
cn.Open
Set RSPortalProgrammer = cn.Execute("SELECT * FROM tblNames")
ResponseWrite "CursorType = " & CStr(RSPortalProgrammer.CursorType) & "<br>"
ResponseWrite "CursorLocation = " & CStr(RSPortalProgrammer.CursorLocation) & "<br>"
ResponseWrite "LockType = " & CStr(RSPortalProgrammer.LockType) & "<br>"
RSPortalProgrammer.Close
cn.Close
0

Featured Post

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

617 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