[RESOLVED] help: function

[iamresearcher]

I have created this function but its not yet properly working that I wanted to ask your help here guys.


basically I wanted to create a function for database connection and recordset connection which I can reuse all over.

module level: 


Public Function ConnectDB(dbcon As String, svrname As String, dbname As String, dbuser As String, dbpwd As String)
On Error GoTo dberror
    
  
    Set dbcn = Nothing
    Set dbcn = New ADODB.Connection
    
    Dim strConnect As String
    
    strConnect = "Provider=SQLNCLI;Server=" & svrname & ";Database=" & dbname & ";Uid=" & dbuser & " ;Pwd=" & dbpwd & ""
    dbcn.Open strConnect


    If dbcn.State = adStateOpen Then
        MsgBox "Connected", vbInformation, ""
    Else
        MsgBox "Not Connected", vbInformation, ""
    End If
    
    
dbexit:
    Exit Function

dberror:
    MsgBox Err.Description, vbInformation, ""
    Resume dbexit

End Function

basically, I added the dbcon variable so that I can reuse that to cn or cn1 or cn2 and the like.

But it always have "byref argument type mismatch" error. I guess because the variable is not declared on my sample

ConnectDB cn, "server1", "test", "sa", "password"


Help me fix my code.

[Doogle]

Couple of things I can see

1. You're passing 'dbcon' and then using 'dbcn'
2. You're passing it as a string and it should be as an 'ADODB.Connection'

also rather than issuing a MessageBox why not just return True or False from the Function?

Public Function ConnectDB(dbcon As ADODB.Connection, _
                          svrname As String, _
                          dbname As String, _
                          dbuser As String, _
                          dbpwd As String) As Boolean
On Error GoTo dberror
    Set dbcon = Nothing
    Set dbcon = New ADODB.Connection
    Dim strConnect As String
    strConnect = "Provider=SQLNCLI;Server=" & svrname & ";Database=" & dbname & ";Uid=" & dbuser & " ;Pwd=" & dbpwd & ""
    dbcon.Open strConnect
    If dbcon.State = adStateOpen Then
        ConnectDB = True
    End If
dbexit:
    Exit Function
dberror:
    MsgBox Err.Description, vbInformation, ""
    Resume dbexit
End Function

call by using:

Dim cn As ADODB.Connection
If ConnectDB(cn, "server1", "test", "sa", "password") Then
    '
    ' Database connection established
    '
    ' process as normal
Else
    MsgBox "Database Connection not established"
End If

[iamresearcher]

thanks I will test now Doogle. I just use the messagebox there for testing purposes so I can see if it what state it is after running the command.

[firoz.raj]

Even no need to write Set dbcon = Nothing after On Error GoTo dberror in this state dbcon by default is Nothing .
anyway doggle suggestion is very nice.