[RESOLVED] Argument Not Optional (DB Connection)

[BrailleSchool]

I am trying to connect to mysql database a different way than usual and getting an argument not optional error on the highlighted.

FORM

Private Sub cmdAdd_Click()
    Dim LvwItm As ListItem
    
    With Me
        Set LvwItm = frmLedger.lvwAccountLedger.ListItems.Add(, , Format(Date, "mm/dd/yyyy"))
        LvwItm.SubItems(1) = .txtTransactionDescription.Text
        LvwItm.SubItems(2) = Format(.txtCredit.Text, "0.00")
        LvwItm.SubItems(3) = Format(.txtDebit.Text, "0.00")
    End With
    
    [hl]DatabaseConnection[/hl]
    
    strSQL = "SELECT * "
    strSQL = strSQL & "FROM Ledger"
    objCon.Execute strSQL
    
    OpenRecordset
    
    With frmLedger
        strSQL = "INSERT INTO Ledger ("
        strSQL = strSQL & "AccountNumber, "
        strSQL = strSQL & "TransDesc, "
        strSQL = strSQL & "Credit, "
        strSQL = strSQL & "Debit, "
        strSQL = strSQL & "EntryDate) "
        strSQL = strSQL & "VALUES ("
        strSQL = strSQL & "'" & .cboAccountNumber.Text & "', "
        strSQL = strSQL & "'" & .txtTransactionDescription.Text & "', "
        strSQL = strSQL & "'" & Format(.txtCredit.Text, "0.00") & "', "
        strSQL = strSQL & "'" & Format(.txtDebit.Text, "0.00") & "', "
        strSQL = strSQL & "'" & Now() & "')"
    End With
    
    objCon.Execute strSQL
    
    Unload Me
End Sub

MODULE

Option Explicit
 
Public Sub DatabaseConnection(objCon As ADODB.Connection)
    Set objCon = New ADODB.Connection
    
    With objCon
        .CursorLocation = adUseClient
        .ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
                          & "SERVER=IP_ADDRESS;" _
                          & "DATABASE=DATABASE_NAME;" _
                          & "UID=USERNAME;" _
                          & "PWD=PASSWORD;" _
                          & "OPTIONS=3;"
    End With
End Sub
 
Public Sub OpenRecordset(objCon As ADODB.Connection, _
                         objRs As ADODB.Recordset, _
                         strSQL As String)
    
    Set objCon = New ADODB.Connection
    Set objRs = New ADODB.Recordset
    
    objCon.Open strSQL, objCon, adOpenStatic, adLockReadOnly
End Sub
                         
Public Sub CloseRecordset(objRs As ADODB.Recordset)
    objRs.Close
    Set objRs = Nothing
End Sub
 
Public Sub CloseConnection(objCon As ADODB.Connection)
    objCon.Close
    Set objCon = Nothing
End Sub

[bushmobile]

your sub call:

DatabaseConnection

your sub:

Public Sub DatabaseConnection[B](objCon As ADODB.Connection)[/B]

objCon is not an optional parameter

[RhinoBull]

Error is quite obvious according to procedure declaration:

DatabaseConnection(objCon As ADODB.Connection)

Few different ways to overcome:

- get rid of objCon As ADODB.Connection from within procedure header
- use public object variable declaration
- change procedure to function that return ado connection
- or use code below:

Dim objCon As ADODB.Connection
 
DatabaseConnection [B]objCon[/B]
 
'... continue with your processing...

[BrailleSchool]

your sub call:

DatabaseConnection

your sub:

Public Sub DatabaseConnection[B](objCon As ADODB.Connection)[/B]

objCon is not an optional parameter

i didnt know that, thanks bush... learned something new

[BrailleSchool]

Error is quite obvious according to procedure declaration:

DatabaseConnection(objCon As ADODB.Connection)

Few different ways to overcome:

- get rid of objCon As ADODB.Connection from within procedure header
- use public object variable declaration
- change procedure to function that return ado connection
- or use code below:

Dim objCon As ADODB.Connection
 
DatabaseConnection [B]objCon[/B]
 
'... continue with your processing...

so anything to do with ado can never be in a header declaration?

[Bruce Fox]

so anything to do with ado can never be in a header declaration?

Sure it can. RB was giving you a few options.


In the example you posted (as BM pointed out) you called a Sub that had an parameter - being in this case a 'Connection'.

Looking at the rest of your code, the Connection is used in all your Subs too.

So in this case, pass the Connection in the DatabaseConnection call as Rhino and bushmobile pointed out.


EDIT: Whats confussing, is that your passing the Connection, but then Setting it to a New Connection in that Sub????????

[Bruce Fox]

Following this along, would this work:

'Form Level
[b]Dim objCon As ADODB.Connection[/b]   'This can then be [i]Set to Nothing[/i] when your done
 
Public Sub DatabaseConnection()  'Note, No parameter
    Set objCon = New ADODB.Connection
    
    With objCon
        .CursorLocation = adUseClient
        .ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
                          & "SERVER=IP_ADDRESS;" _
                          & "DATABASE=DATABASE_NAME;" _
                          & "UID=USERNAME;" _
                          & "PWD=PASSWORD;" _
                          & "OPTIONS=3;"
    End With
End Sub

[BrailleSchool]

so if i use a sub, then to use a public declaration... if i use a function then i can do

Public Function SomeSubName(objCon As ADODB.Connection)
'....
End Function

?

Sure it can. RB was giving you a few options.


In the example you posted (as BM pointed out) you called a Sub that had an parameter - being in this case a 'Connection'.

Looking at the rest of your code, the Connection is used in all your Subs too.

So in this case, pass the Connection in the DatabaseConnection call as Rhino and bushmobile pointed out.


EDIT: Whats confussing, is that your passing the Connection, but then Setting it to a New Connection in that Sub????????

[BrailleSchool]

but the thing is that i dont understand is if i used a function, you have to have a datatype for the function name, what datatype would that be?

[RhinoBull]

I'll try to explain one more time:
- function returns value and sub doesn't
- if you want function to return something then it must return some type (ado connection, recordset, integer, string, etc)
- to return ado connection you declare it as adodb.connection but also you do not have to pass any arguments at all...

Here is a quick sample:

Public Function DatabaseConnection() As ADODB.Connection
Dim objCon As ADODB.Connection
 
    Set objCon = New ADODB.Connection
    
    With objCon
        .CursorLocation = adUseClient
        .ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
                          & "SERVER=IP_ADDRESS;" _
                          & "DATABASE=DATABASE_NAME;" _
                          & "UID=USERNAME;" _
                          & "PWD=PASSWORD;" _
                          & "OPTIONS=3;"
        .Open
    End With
    
    DatabaseConnection = objCon
 
End Function
 
'usage:
Dim objNewCon As ADODB.Connection
 
Set objNewCon = New ADODB.Connection
Set objNewCon = DatabaseConnection
 
'continue...

[RhinoBull]

If you want to have a sub then try sample below:

Public Sub DatabaseConnection(objCon As ADODB.Connection) 
 
    With objCon
        .CursorLocation = adUseClient
        .ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
                          & "SERVER=IP_ADDRESS;" _
                          & "DATABASE=DATABASE_NAME;" _
                          & "UID=USERNAME;" _
                          & "PWD=PASSWORD;" _
                          & "OPTIONS=3;"
        .Open
    End With
 
End Sub
 
'usage:
Dim objCon As ADODB.Connection
 
Set objCon = New ADODB.Connection
DatabaseConnection objCon
 
'continue...