question -- close adodb connection inside dll

21 Mar 2006 9:43 PM

Sue

If I close the adodb connection and recordset object inside the dll I
get errors (tried many ways, no solution). In the project where I
reference the dll I am setting the dll class file to nothing after use
- will this automatically set the connection and recordset objects to
nothing and close them as well? Posting code below. Thanks a bunch for
helping!

'---dll code
Public Function rightsCheck(ByVal lg As String, ByVal ps As String,
ByVal nm As String) As Integer
On Error GoTo rightsCheck_err

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnect As String
Dim strSql As String

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

rightsCheck = 100 'initalize variable

sConnect = "Provider=SQLOLEDB;Data Source=hawk.episense.wisc.edu;" &
"Initial Catalog=BOSS1;" & " User ID=" & lg & "; Password=" & ps

strSql = ""
strSql = strSql + "select RightsTbl.login, RightsTbl.programName from "
strSql = strSql + "RightsTbl Where (RightsTbl.login = '" + lg + "'" + "
and (RightsTbl.programName = '"
strSql = strSql + nm + "'" + " or RightsTbl.programName = 'All'))"

cnn.Open sConnect

If Err.Number = 0 Then 'close connection and re-open as user aaa
    rightsCheck = 0
    cnn.Close
    sConnect = "Provider=SQLOLEDB;Data Source=hawk.episense.wisc.edu;"
& " Initial Catalog=BOSS1;" & " User ID=aaa;" & "
Password=f@i7ss89=rk3li1``"
    cnn.Open
Else
rightsCheck = 1
Exit Function
End If

If rightsCheck = 0 Then 'if no login error check for application rights
    rs.Open strSql, cnn, adOpenDynamic, adLockPessimistic 'open
recordset
End If

    If Not rs.EOF Then 'if user is allowed to run application
        rightsCheck = 0
        'rs.Close
        'Set rs = Nothing
        'cn.Close
       ' Set cn = Nothing
    End If
     ' Else 'if eof
     If rs.EOF Then
     rightsCheck = 2 'rights application error
        'rs.Close
        'Set rs = Nothing
       ' cn.Close
        'Set cn = Nothing
        'Exit Function
     End If

rightsCheck_err:
If Err.Number <> 0 Then
    rightsCheck = 1 'login error
Exit Function
End If

'cn.Close
'Set cn = Nothing
End Function

'---------calling function code
Dim dl As clsRights ' create a variable to point to the class file
inside the dll
Set dl = New clsRights 'create an instance of the class
result = dl.rightsCheck(log, pass, appName)
Set rightsDll = Nothing 'clean up

22 Mar 2006 2:41 PM

Paul Clement

On 21 Mar 2006 13:43:25 -0800, "Sue" <sea_***@hotmail.com> wrote:

¤ If I close the adodb connection and recordset object inside the dll I
¤ get errors (tried many ways, no solution). In the project where I
¤ reference the dll I am setting the dll class file to nothing after use
¤ - will this automatically set the connection and recordset objects to
¤ nothing and close them as well? Posting code below. Thanks a bunch for
¤ helping!
¤
¤ '---dll code
¤ Public Function rightsCheck(ByVal lg As String, ByVal ps As String,
¤ ByVal nm As String) As Integer
¤ On Error GoTo rightsCheck_err
¤
¤ Dim cnn As ADODB.Connection
¤ Dim rs As ADODB.Recordset
¤ Dim sConnect As String
¤ Dim strSql As String
¤
¤ Set cnn = New ADODB.Connection
¤ Set rs = New ADODB.Recordset
¤
¤ rightsCheck = 100 'initalize variable
¤
¤
¤ sConnect = "Provider=SQLOLEDB;Data Source=hawk.episense.wisc.edu;" &
¤ "Initial Catalog=BOSS1;" & " User ID=" & lg & "; Password=" & ps
¤
¤
¤ strSql = ""
¤ strSql = strSql + "select RightsTbl.login, RightsTbl.programName from "
¤ strSql = strSql + "RightsTbl Where (RightsTbl.login = '" + lg + "'" + "
¤ and (RightsTbl.programName = '"
¤ strSql = strSql + nm + "'" + " or RightsTbl.programName = 'All'))"
¤
¤ cnn.Open sConnect
¤
¤ If Err.Number = 0 Then 'close connection and re-open as user aaa
¤     rightsCheck = 0
¤     cnn.Close
¤     sConnect = "Provider=SQLOLEDB;Data Source=hawk.episense.wisc.edu;"
¤ & " Initial Catalog=BOSS1;" & " User ID=aaa;" & "
¤ Password=f@i7ss89=rk3li1``"
¤     cnn.Open
¤ Else
¤ rightsCheck = 1
¤ Exit Function
¤ End If
¤
¤ If rightsCheck = 0 Then 'if no login error check for application rights
¤     rs.Open strSql, cnn, adOpenDynamic, adLockPessimistic 'open
¤ recordset
¤ End If
¤
¤     If Not rs.EOF Then 'if user is allowed to run application
¤         rightsCheck = 0
¤         'rs.Close
¤         'Set rs = Nothing
¤         'cn.Close
¤        ' Set cn = Nothing
¤     End If
¤      ' Else 'if eof
¤      If rs.EOF Then
¤      rightsCheck = 2 'rights application error
¤         'rs.Close
¤         'Set rs = Nothing
¤        ' cn.Close
¤         'Set cn = Nothing
¤         'Exit Function
¤      End If
¤
¤
¤ rightsCheck_err:
¤ If Err.Number <> 0 Then
¤     rightsCheck = 1 'login error
¤   Exit Function
¤ End If
¤
¤ 'cn.Close
¤ 'Set cn = Nothing
¤ End Function
¤
¤ '---------calling function code
¤ Dim dl As clsRights ' create a variable to point to the class file
¤ inside the dll
¤ Set dl = New clsRights 'create an instance of the class
¤ result = dl.rightsCheck(log, pass, appName)
¤ Set rightsDll = Nothing 'clean up

What are the errors you are getting and where do they occur?

Setting ADO objects to Nothing will not close (release) the underlying database connection.

Paul
~~~~
Microsoft MVP (Visual Basic)

22 Mar 2006 3:45 PM

Sue

Thanks, Paul - it helps to know that setting objects to nothing will
not close the underlying connection. The error I was getting was inside
the calling program, because after the connection and calling object
are closed the return value was not correct. To solve the probem I
made another sub inside the dll called clean up where the recordsets
and connection objects are closed. Then just before the dll was set to
nothing and after it's use was complete I called the cleanup sub inside
the dll. This seems to have solved the problem -- seems to have been a
logical error... Thanks again for helping!
======================

Paul Clement wrote:
Show quoteHide quote

> On 21 Mar 2006 13:43:25 -0800, "Sue" <sea_***@hotmail.com> wrote:
>
> ¤ If I close the adodb connection and recordset object inside the dll I
> ¤ get errors (tried many ways, no solution). In the project where I
> ¤ reference the dll I am setting the dll class file to nothing after use
> ¤ - will this automatically set the connection and recordset objects to
> ¤ nothing and close them as well? Posting code below. Thanks a bunch for
> ¤ helping!
> ¤
> ¤ '---dll code
> ¤ Public Function rightsCheck(ByVal lg As String, ByVal ps As String,
> ¤ ByVal nm As String) As Integer
> ¤ On Error GoTo rightsCheck_err
> ¤
> ¤ Dim cnn As ADODB.Connection
> ¤ Dim rs As ADODB.Recordset
> ¤ Dim sConnect As String
> ¤ Dim strSql As String
> ¤
> ¤ Set cnn = New ADODB.Connection
> ¤ Set rs = New ADODB.Recordset
> ¤
> ¤ rightsCheck = 100 'initalize variable
> ¤
> ¤
> ¤ sConnect = "Provider=SQLOLEDB;Data Source=hawk.episense.wisc.edu;" &
> ¤ "Initial Catalog=BOSS1;" & " User ID=" & lg & "; Password=" & ps
> ¤
> ¤
> ¤ strSql = ""
> ¤ strSql = strSql + "select RightsTbl.login, RightsTbl.programName from "
> ¤ strSql = strSql + "RightsTbl Where (RightsTbl.login = '" + lg + "'" + "
> ¤ and (RightsTbl.programName = '"
> ¤ strSql = strSql + nm + "'" + " or RightsTbl.programName = 'All'))"
> ¤
> ¤ cnn.Open sConnect
> ¤
> ¤ If Err.Number = 0 Then 'close connection and re-open as user aaa
> ¤     rightsCheck = 0
> ¤     cnn.Close
> ¤     sConnect = "Provider=SQLOLEDB;Data Source=hawk.episense.wisc.edu;"
> ¤ & " Initial Catalog=BOSS1;" & " User ID=aaa;" & "
> ¤ Password=f@i7ss89=rk3li1``"
> ¤     cnn.Open
> ¤ Else
> ¤ rightsCheck = 1
> ¤ Exit Function
> ¤ End If
> ¤
> ¤ If rightsCheck = 0 Then 'if no login error check for application rights
> ¤     rs.Open strSql, cnn, adOpenDynamic, adLockPessimistic 'open
> ¤ recordset
> ¤ End If
> ¤
> ¤     If Not rs.EOF Then 'if user is allowed to run application
> ¤         rightsCheck = 0
> ¤         'rs.Close
> ¤         'Set rs = Nothing
> ¤         'cn.Close
> ¤        ' Set cn = Nothing
> ¤     End If
> ¤      ' Else 'if eof
> ¤      If rs.EOF Then
> ¤      rightsCheck = 2 'rights application error
> ¤         'rs.Close
> ¤         'Set rs = Nothing
> ¤        ' cn.Close
> ¤         'Set cn = Nothing
> ¤         'Exit Function
> ¤      End If
> ¤
> ¤
> ¤ rightsCheck_err:
> ¤ If Err.Number <> 0 Then
> ¤     rightsCheck = 1 'login error
> ¤   Exit Function
> ¤ End If
> ¤
> ¤ 'cn.Close
> ¤ 'Set cn = Nothing
> ¤ End Function
> ¤
> ¤ '---------calling function code
> ¤ Dim dl As clsRights ' create a variable to point to the class file
> ¤ inside the dll
> ¤ Set dl = New clsRights 'create an instance of the class
> ¤ result = dl.rightsCheck(log, pass, appName)
> ¤ Set rightsDll = Nothing 'clean up
>
> What are the errors you are getting and where do they occur?
>
> Setting ADO objects to Nothing will not close (release) the underlying database connection.
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)

22 Mar 2006 3:45 PM

Sue

Thanks, Paul - it helps to know that setting objects to nothing will
not close the underlying connection. The error I was getting was inside
the calling program, because after the connection and calling object
are closed the return value was not correct. To solve the probem I
made another sub inside the dll called clean up where the recordsets
and connection objects are closed. Then just before the dll was set to
nothing and after it's use was complete I called the cleanup sub inside
the dll. This seems to have solved the problem -- seems to have been a
logical error... Thanks again for helping!
======================

Paul Clement wrote:
Show quoteHide quote

> On 21 Mar 2006 13:43:25 -0800, "Sue" <sea_***@hotmail.com> wrote:
>
> ¤ If I close the adodb connection and recordset object inside the dll I
> ¤ get errors (tried many ways, no solution). In the project where I
> ¤ reference the dll I am setting the dll class file to nothing after use
> ¤ - will this automatically set the connection and recordset objects to
> ¤ nothing and close them as well? Posting code below. Thanks a bunch for
> ¤ helping!
> ¤
> ¤ '---dll code
> ¤ Public Function rightsCheck(ByVal lg As String, ByVal ps As String,
> ¤ ByVal nm As String) As Integer
> ¤ On Error GoTo rightsCheck_err
> ¤
> ¤ Dim cnn As ADODB.Connection
> ¤ Dim rs As ADODB.Recordset
> ¤ Dim sConnect As String
> ¤ Dim strSql As String
> ¤
> ¤ Set cnn = New ADODB.Connection
> ¤ Set rs = New ADODB.Recordset
> ¤
> ¤ rightsCheck = 100 'initalize variable
> ¤
> ¤
> ¤ sConnect = "Provider=SQLOLEDB;Data Source=hawk.episense.wisc.edu;" &
> ¤ "Initial Catalog=BOSS1;" & " User ID=" & lg & "; Password=" & ps
> ¤
> ¤
> ¤ strSql = ""
> ¤ strSql = strSql + "select RightsTbl.login, RightsTbl.programName from "
> ¤ strSql = strSql + "RightsTbl Where (RightsTbl.login = '" + lg + "'" + "
> ¤ and (RightsTbl.programName = '"
> ¤ strSql = strSql + nm + "'" + " or RightsTbl.programName = 'All'))"
> ¤
> ¤ cnn.Open sConnect
> ¤
> ¤ If Err.Number = 0 Then 'close connection and re-open as user aaa
> ¤     rightsCheck = 0
> ¤     cnn.Close
> ¤     sConnect = "Provider=SQLOLEDB;Data Source=hawk.episense.wisc.edu;"
> ¤ & " Initial Catalog=BOSS1;" & " User ID=aaa;" & "
> ¤ Password=f@i7ss89=rk3li1``"
> ¤     cnn.Open
> ¤ Else
> ¤ rightsCheck = 1
> ¤ Exit Function
> ¤ End If
> ¤
> ¤ If rightsCheck = 0 Then 'if no login error check for application rights
> ¤     rs.Open strSql, cnn, adOpenDynamic, adLockPessimistic 'open
> ¤ recordset
> ¤ End If
> ¤
> ¤     If Not rs.EOF Then 'if user is allowed to run application
> ¤         rightsCheck = 0
> ¤         'rs.Close
> ¤         'Set rs = Nothing
> ¤         'cn.Close
> ¤        ' Set cn = Nothing
> ¤     End If
> ¤      ' Else 'if eof
> ¤      If rs.EOF Then
> ¤      rightsCheck = 2 'rights application error
> ¤         'rs.Close
> ¤         'Set rs = Nothing
> ¤        ' cn.Close
> ¤         'Set cn = Nothing
> ¤         'Exit Function
> ¤      End If
> ¤
> ¤
> ¤ rightsCheck_err:
> ¤ If Err.Number <> 0 Then
> ¤     rightsCheck = 1 'login error
> ¤   Exit Function
> ¤ End If
> ¤
> ¤ 'cn.Close
> ¤ 'Set cn = Nothing
> ¤ End Function
> ¤
> ¤ '---------calling function code
> ¤ Dim dl As clsRights ' create a variable to point to the class file
> ¤ inside the dll
> ¤ Set dl = New clsRights 'create an instance of the class
> ¤ result = dl.rightsCheck(log, pass, appName)
> ¤ Set rightsDll = Nothing 'clean up
>
> What are the errors you are getting and where do they occur?
>
> Setting ADO objects to Nothing will not close (release) the underlying database connection.
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)