Connection string error

Question

Having a problem with IIS on Windows Server 2003 (using a classic ASP web application) The database server (SQL Server 2005) is on the same machine.

I create a recordset, and set its activeconnection as follows:

Dim RS
Set RS = Server.CreateObject("ADODB.Recordset")
RS.ActiveConnection = "Provider=SQLOLEDB;Network Library=DBMSSOCN;Data Source=xxx.xxx.xxx.xxx,1433;Initial Catalog=mydatabase;User ID=myusername;Password=mypassword;"

The problem is that as SOON as I set this connection string, the following error is raised:

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

I know there isnt a problem with this connection string because when I use my development server, but point the connection toward the remote database, it works great.

I have tried many MANY connection string variations (from connectionstrings.com) and they have given the same error.

Answer 1

Try doing something similar to this MSDN Sample.

It's creating a connection separately, then assigning it.

Answer 2

The ActiveConnection property takes an Object of type ADODB.Connection and not a String. You can't assign a Connection String directly, you need to assign a database connection. Your code needs to look like the following:

Set objDB = Server.CreateObject("ADODB.Connection");
objDB.Open = "Provider=SQLOLEDB;Network Library=DBMSSOCN;Data Source=xxx.xxx.xxx.xxx,1433;Initial Catalog=mydatabase;User ID=myusername;Password=mypassword;"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = objDB

Actually you shouldn't really use the ActiveConnection property in this way at all, what you should actually use it for is disconnecting a recordset to avoid keeping the database connection open unecessarily:

Set objDB = Server.CreateObject("ADODB.Connection");
objDB.Open = "Provider=SQLOLEDB;Network Library=DBMSSOCN;Data Source=xxx.xxx.xxx.xxx,1433;Initial Catalog=mydatabase;User ID=myusername;Password=mypassword;"

//Get a Recordset and prep it for forward only disconnected use
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorLocation = adUseClient
objRS.CursorType = adOpenStatic
objRS.LockType = adLockReadOnly
objRS.Open "SELECT * FROM SOME_TABLE", objDB

//Now disconnect the recordset and dispose of the database connection
Set objRS.ActiveConnection = Nothing
objDB.Close
Set objDB = Nothing

//Now do whatever you want with the Recordset
//...

Answer 3

@RobV - Creating a connection object and assigning that to the recordset is indeed an option, however, a shortcut to this is assigning a connection string to the recordset - which does work.

In fact the following code is the most efficient:

Dim RS
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open <sqlstatement>, <connectionstring>

Im not sure what happened with the error I was getting but it SUDDENLY just stopped without having changed any code!