Re: Oracle and ASP Error

"Jay" <jerry280_at_yahoo.com> wrote in message news:xGWka.8$Hg5.13910_at_news.uswest.net...
> Hello everyone! I've created an ASP application to connect to an Oracle
> database. I used a file DSN but am now getting the following error
message:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
>
> ODBC driver does not support the requested properties.
>
> This error message is for the following line of code:
> RS.open SQL, conn, 1, 2
>
> Following is my ASP code. Can anyone help me figure out why I am getting
the
> message? Also, is there a better way to connect to the Oracle database
> instead of a file DSN? I've heard arguments for DSN connections and
DSN-Less
> connections with Oracle and am finding that the DSN connection isn't
working
> so well.

Jay,

I wonder why you're setting the cursortype and locktype properties for a select statement? If you were doing DML, I could see the reasoning. But if you're only displaying a table to the screen, and then disposing of the recordset object immediately afterwards, there's no need to lock the recordset with pessimistic locking. Plus, you're setting the cursor type to adOpenKeyset, but, you're only using the rs.MoveNext method. The adOpenForwardOnly (default) cursor should be fine. In fact, try setting the properties of your recordset before opening it. You improve slightly with performance if, for instance, you say "set RS.ActiveConnection = conn" instead of "RS.open SQL, conn". In fact, are you planning on running multiple queries on this page? If not, you might not want to use the connection object; it will use less overhead if you use a recordset object directly.

I think the actual error message you're getting, regardless of cursor types and locking styles, comes from using an old ODBC driver. What version of Oracle are you running? Can you use a more advanced driver, like, the Microsoft OLE DB Provider for Oracle, or, the Oracle Provider for OLE DB (Oracle's version, which I use)? Have you tried leaving the options off?

Also, it looks like you're using VBScript. Have you thought of including the adovbs.inc file in your ASP code (via SSI) to increase readability during the design phase? You can also include the ADO 2.5 library's msado15.dll component via a METADATA tag.

I only use an ODBC DSN for our Oracle 7.3 database, since it doesn't seem to like the MS or Oracle OLE DB providers. But, I use a system DSN, not a file DSN, and reference the DSN name through my database connection string. For instance, my Oracle 7.3 JScript connection goes something like this:

sConn="Provider=MSDASQL.1;Password=myPass;User ID=myUser;Data Source=theNameOfMySystemDSN";

Make sure the DSN's match on your development machine, test server, and production server. Also make sure that all the MDAC versions are the same! If you can use a true OLE DB provider, you'll gain the increased functionality, and pick up some peformance. As to database connection strings; think about using the ASP Application object. My application uses it to hold constant values; such as main contact information (phone number and email address), and database connection strings. Since every active session gets the Application variables associated with it, this is a good central area to store the connection strings.

Hope that helps,

-Thomas Received on Wed Apr 09 2003 - 11:26:34 CDT