Sql – Unable to connect to SQL2005 using VBScript

sql-server-2005vbscript

Using the connection string below I can connect to a SQL2000 DB but not a SQL2005. I have the code in an ASP file.

Dim connStr, cn, rs, sql
connStr = "Provider=SQLOLEDB;Persist Security Info=True" _
           & ";Initial Catalog=mydatabase"  _
           & ";User ID=dbuser" _
           & ";Password=dbpwd" _
           & ";Data Source=servername" 
sql = "SELECT TOP 1 [Column1] FROM [dbo].[MyTable] order by NEWID()"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open cn

set rs= server.CreateObject("ADODB.Recordset")
    rs.CursorLocation=3
    rs.Open sql,cn,3,4

    if not rs.EOF then
       Response.Write("<b>Column1: " & rs("Column1") & "</b><br />")
    end if
set rs.ActiveConnection= nothing    
    rs.Close
    set rs= nothing
if ucase(TypeName(cn)) = "CONNECTION" then
    cn.Close
    Set cn = Nothing
end if

I have even tired with SQLOLEDB.1
Sql login is enabled on the sql server.

Error: The connection cannot be used to perform this operation. It is either closed or invalid in this context.
Happens on rs.Open sql,cn,3,4

Best Answer

It happens to everybody sometime:

Dim connStr, cn, rs, sql
connStr = "Provider=SQLOLEDB;Persist Security Info=True" _
           & ";Initial Catalog=mydatabase"  _
           & ";User ID=dbuser" _
           & ";Password=dbpwd" _
           & ";Data Source=servername" 
sql = "SELECT TOP 1 [Column1] FROM [dbo].[MyTable] order by NEWID()"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open connStr

You are calling a variable conn as connection string but you have declared and filled connStr

Change "cn.Open conn" with "cn.Open connStr"

Related Topic