Evening ( morning. ahh shucks... Hi.

Learning ASP but been VB programmer and I think some habits die hard...
can someone please explain the error of my ways..

I want to have a connection opened in a function or subroutine as follows
it's in a seperate include file and yes included....


    Function SetConn(db)
    Set Conn = Server.CreateObject("ADODB.Connection")
    MdbFilePath = "D:\Inetpub\wwwroot\Database\" & DB & ".mdb"
    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MdbFilePath & ";User Id=admin;Password="
    SetConn = Conn
    end Function

db is passed to it by calling it such as
Set MyConn = SetConn("Helpdesk")
after 'set'ing the RS and msql for the sql statement

RS.Open msql, MyConn works fine......

however, you can't use MyConn.Close as the object doesn't exist...

I have tried elsewhere and suggestion was put it in a sub in an inc file ( which the function already is )

The reason for having as a function is so I have one point of code to change, and it's used frequently in the code.

Does anyone have experience with this or any suggestions.....
I just want to be able to close MyConn.

Sean

I have come up with a kind of solution, but not very glamourous in fact quite ugly....

after using the record set I call a CloseConn Function,
Open the connection to the DB again, and close it.

Not 100% sure, but it seems to be helping clear the lines..

would really like a proper answer though :)

Just like the variable inside the function, you need to set the function return:

Function SetConn(db)
    Set Conn = Server.CreateObject("ADODB.Connection")
    ...
    Set SetConn = Conn
end Function

it already is

    Function SetConn(db)
    Set Conn = Server.CreateObject("ADODB.Connection")
    MdbFilePath = "D:\Inetpub\wwwroot\Database\" & DB & ".mdb"
    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MdbFilePath & ";User Id=admin;Password="
    SetConn = Conn
    end Function

Function SetConn(db)
    Set Conn = Server.CreateObject("ADODB.Connection")
    ...
    Set SetConn = Conn
end Function

Peter is correct, you're missing the Set statement. But why do you bother with the intermediate Conn variable? Why not just:

Thanks,
between the two of you and a little more vigilant checking, have resolved it.

Sometimes there was also no SET before the MyConn = SetConn("Helpdesk")

So I have revised the whole lot.
Two connections get opened and used throughout.
    Function SetConn
    Set SetConn = Server.CreateObject("ADODB.Connection")
    MdbFilePath = "D:\Inetpub\wwwroot\Database\Helpdesk.mdb"
    SetConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MdbFilePath & ";User Id=admin;Password="
    End Function

    Function SetConn1
    Set SetConn1 = Server.CreateObject("ADODB.Connection")
    MdbFilePath = "D:\Inetpub\wwwroot\Database\Users.mdb"
    SetConn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MdbFilePath & ";User Id=admin;Password="
    End Function

used as per PGTIPS suggestion thanks...

then just need to use the relevant SetConn each time I call it
at least I can now close the damn things :)


Thanks

Hello ppl, ive been looking at the same code for an hour or two and instead of deleting everything, i thought id ask for some help! :s

im getting the same prob with the object:
Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required

ive got:
Dim DBConn5, rs5
Set DBConn5=server.CreateObject("ADODB.Connection")
DBConn5.Provider="Microsoft.Jet.OLEDB.4.0"
DBConn5.Open "C:\Inetpub\wwwroot\database\db.mdb"
set rs5=Server.CreateObject("ADODB.Recordset")
rs5.Open "SELECT * FROM Users;", DBConn5

do while not rs5.EOF
if (StrCOMP(rs5("username"), adminnew, vbTextCompare)=0) then
organisation=rs5("organisation")
rs5.Close
Set rs5 = Nothing
DBConn5.Close
Set DBConn5 = Nothing
else
rs5.MoveNext
End if
Loop

I get the error on the do while not rs5.EOF. The thing is i have 4 other connections that work in exactly the same way.
Any help is welcome!!!!
thanx

sorry for the fuss, its 5 am and brain is prolly not working...found the problem.......
closing the connection before eof finishes :s