[RESOLVED] Argument Not Optional (DB Connection)

Printable View

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

FORM

VB Code:

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

VB Code:

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

Nov 20th, 2006, 07:18 PM
bushmobile

Re: Argument Not Optional (DB Connection)
your sub call:
VB Code:
```
DatabaseConnection
```
your sub:
VB Code:
```
Public Sub DatabaseConnection[B](objCon As ADODB.Connection)[/B]
```
objCon is not an optional parameter
Nov 20th, 2006, 07:21 PM
RhinoBull

Re: Argument Not Optional (DB Connection)
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:
VB Code:
```
Dim objCon As ADODB.Connection
 
DatabaseConnection [B]objCon[/B]
 
'... continue with your processing...
```
Nov 20th, 2006, 11:26 PM
BrailleSchool

Re: Argument Not Optional (DB Connection)
Quote:
Originally Posted by bushmobile

your sub call:

VB Code:

DatabaseConnection

your sub:

VB Code:

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

objCon is not an optional parameter
i didnt know that, thanks bush... learned something new :)
Nov 20th, 2006, 11:28 PM
BrailleSchool

Re: Argument Not Optional (DB Connection)
Quote:
Originally Posted by 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:

VB Code:

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?
Nov 21st, 2006, 12:40 AM
Bruce Fox

Re: Argument Not Optional (DB Connection)

Quote:

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????????

Re: Argument Not Optional (DB Connection)

Following this along, would this work:

VB Code:

'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

Nov 21st, 2006, 04:35 PM
BrailleSchool

Re: Argument Not Optional (DB Connection)
so if i use a sub, then to use a public declaration... if i use a function then i can do
VB Code:
```
Public Function SomeSubName(objCon As ADODB.Connection)
'....
End Function
```
?

Quote:

Originally Posted by Bruce Fox

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????????
Nov 21st, 2006, 04:37 PM
BrailleSchool

Re: [RESOLVED] Argument Not Optional (DB Connection)

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?

Re: [RESOLVED] Argument Not Optional (DB Connection)

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:

VB Code:

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...

Re: [RESOLVED] Argument Not Optional (DB Connection)

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

VB Code:

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...