[RESOLVED] VB6 complile error:Too many arguments

[dfuas]

Hi,

I have a vb6 application I which selects a csv file and imports it in sql server 2000 database tables. I am adding further fields (just one at mo while i am trying to undertand it) in the csv file. I have a function which I believe needs to add a argument of the new field i am adding but i am getting the error: too many arguments.
Can you please help or explain why is this?
Part of the code is shown below. I can provide more if you are willing to have a look.

thanks

df

Private Function InsertHoldingData(dteBatchDate As Date, _
                                                BatchName As String, URN As String, _
                                                Title As String, Initial As String, Surname As String, _
                                                Organisation As String, POBox As String, _
                                                Address1 As String, Address2 As String, Address3 As String, _
                                                Locality As String, Town As String, County As String, Postcode As String, _
                                                DPS As String, Country As String, CountryCode As String, _
                                                Telephone As String, Email As String, dteBirthday As Variant, _
                                                AgeUnder25 As Integer, Age2635 As Integer, Age3645 As Integer, _
                                                Age4655 As Integer, Age5565 As Integer, AgeOver65 As Integer, _
                                                CdiorUser As Integer, StoreCardHolder As Integer, _
                                                DoNotMail As Integer, DoNotTel As Integer, DoNotEmail As Integer, _
                                                DoNotPass As Integer, dteInitialDate As Date, _
                                                SkinCareFocusDry As Integer, SkinCareFocusDehyd As Integer, _
                                                SkinCareFocusFirstSignAge As Integer, _
                                                SkinCareFocusVisibleAge As Integer, SkinCareFocusFirm As Integer, _
                                                SkinCareFocusRadiance As Integer, SkinCareFocusOil As Integer, _
                                                ProdPurchBasics As Integer, ProdPurchAA As Integer, _
                                                ProdPurchIOD As Integer, ProdPurchPrestige As Integer, _
                                                ProdPurchFoundation As Integer, ProdPurchMakeUp As Integer, _
                                                ProdPurchFF As Integer, ProdPurchMF As Integer, _
                                                ProdPurchCapture As Integer, ProdPurchNoAge As Integer, ProdPurchMove As Integer, _
                                                ProdPurchBikini As Integer, ProdPurchBronze As Integer, _
                                                ProdPurch As Currency, Store As String, Suppressed As Integer, _
                                                UploadID As Integer, HoldingID As String) As Long
On Error GoTo Err_InsertHoldingData
     
    'Normal variables
    Dim strInitialDate As String
    Dim strLoadDate As String
    Dim strBatchDate As String
    Dim strBirthDay As String
    Dim rstInsertHoldingData As ADODB.Recordset
    Dim cmInsertHoldingData As ADODB.Command
    
       
           'Purchase Variables
    Dim prmStoreCardHolder As ADODB.Parameter
    Dim prmCDiorUser As ADODB.Parameter
    Dim prmSkinCareFocusDry As ADODB.Parameter
    Dim prmSkinCareFocusDehyd As ADODB.Parameter
    Dim prmSkinCareFocusFirstSignAge As ADODB.Parameter
    Dim prmSkinCareFocusVisibleAge As ADODB.Parameter
    Dim prmSkinCareFocusFirm As ADODB.Parameter
    Dim prmSkinCareFocusRadiance As ADODB.Parameter
    Dim prmSkinCareFocusOil As ADODB.Parameter
    Dim prmProdPurchBasics As ADODB.Parameter
    Dim prmProdPurchAA As ADODB.Parameter
    Dim prmProdPurchIOD As ADODB.Parameter
    Dim prmProdPurchPrestige As ADODB.Parameter
    Dim prmProdPurchFoundation As ADODB.Parameter
    Dim prmProdPurchMakeUp As ADODB.Parameter
    Dim prmProdPurchFF As ADODB.Parameter
    Dim prmProdPurchMF As ADODB.Parameter
    Dim prmProdPurch As ADODB.Parameter
    Dim prmdteLoaded As ADODB.Parameter
    Dim prmdteInitial As ADODB.Parameter
    Dim prmdteBatchDate As ADODB.Parameter
    Dim prmUploadID As ADODB.Parameter
    Dim prmBatchName As ADODB.Parameter
    Dim prmProdPurchCapture As ADODB.Parameter
    Dim prmProdPurchNoAge As ADODB.Parameter
    Dim prmProdPurchMove As ADODB.Parameter
    Dim prmProdPurchBikini As ADODB.Parameter
    Dim prmProdPurchBronze As ADODB.Parameter
    
    'Report Date
    Dim prmintReportDateURN As ADODB.Parameter
    
    'Store Parameters
    Dim prmStore As ADODB.Parameter
    
    'Output Paramter
    Dim prmResult As ADODB.Parameter
    
    'Clear errors collection
    Err.Clear
    
    'Address Table Parameters
    Set prmAddress1 = New ADODB.Parameter
    With prmAddress1
        .Direction = adParamInput
        .Type = adChar
        .Size = 50
        .Value = Trim(Address1)
    End With
    Set prmPostcode = New ADODB.Parameter
    With prmPostcode
        .Direction = adParamInput
        .Type = adChar
        .Size = 11
        .Value = Trim(Postcode) 'Has to be not null
    End With
    Set prmOrganisation = New ADODB.Parameter
    With prmOrganisation
        .Direction = adParamInput
        .Type = adChar
        .Size = 65
        .Value = Trim(Organisation)
    End With

Public Function UploadMainData() As Integer
On Error GoTo Err_UploadMainData
Dim strErr As String
Dim strErrSource As String
Dim intErr As Integer
Dim intRecCount As Integer
Dim rstResult As ADODB.Recordset
Dim lngResult As Long

'Err.Clear

'Populate rstResult recordset from either the new or repeat holding table
Set rstResult = SelectHoldingData(mvarintErrorRecs, mvarintSpecificErrorRec)

rstResult.MoveFirst
rstResult.MoveLast
intRecCount = rstResult.RecordCount

'Pope added 20020503
With rstResult

'Pope took out the connect from inside the loop statement 20020510
Call Connect

UploadMainData = 1

If mvaroptNew = True Then 'If the user has elected to upload New Data
    .MoveFirst
    Do Until .EOF
        If IsDate(.Fields("txtInitialDate").Value) = True And IsDate(.Fields("txtBatchDate").Value) = True Then
            lngResult = InsertHoldingData(CDate(.Fields("txtBatchDate").Value), .Fields("txtBatch").Value, _
            .Fields("txtURN").Value, .Fields("txtTitle").Value, Left(Trim(.Fields("txtInitial").Value), 1), .Fields("txtSurname").Value, _
            .Fields("txtCompany").Value, .Fields("txtPOBox").Value, .Fields("txtAdd1").Value, _
            .Fields("txtAdd2").Value, .Fields("txtAdd3").Value, .Fields("txtLocality").Value, .Fields("txtTown").Value, _
            .Fields("txtCounty").Value, .Fields("txtPostcode").Value, .Fields("txtDPS").Value, _
            .Fields("txtCountry").Value, .Fields("txtCountryCode").Value, _
            .Fields("txtPhone").Value, .Fields("txtEmail").Value, IIf(Len(.Fields("txtBirthday").Value) < 1, Null, .Fields("txtBirthday").Value), _
            .Fields("txtAgeRangeTo25").Value, .Fields("txtAgeRangeTo35").Value, _
            .Fields("txtAgeRangeTo45").Value, .Fields("txtAgeRangeTo55").Value, .Fields("txtAgeRangeTo65").Value, .Fields("txtAgeRangeOver65").Value, _
            .Fields("txtDiorUser").Value, .Fields("txtStoreCard").Value, .Fields("txtDNM").Value, .Fields("txtDNT").Value, .Fields("txtDNE").Value, .Fields("txtDNTP").Value, _
            CDate(.Fields("txtInitialDate").Value), _
            .Fields("txtDry").Value, .Fields("txtDehyd").Value, .Fields("txtFirstAge").Value, _
            .Fields("txtVisible").Value, .Fields("txtLOFirm").Value, .Fields("txtRadiance").Value, .Fields("txtOily").Value, .Fields("txtPPB").Value, _
            .Fields("txtPPAA").Value, .Fields("txtPPIOD").Value, .Fields("txtPPP").Value, .Fields("txtPPF").Value, .Fields("txtPPMU").Value, _
            .Fields("txtPPFF").Value, .Fields("txtPPMF").Value, .Fields("txtPPC").Value, .Fields("txtPPNA").Value, .Fields("txtPPM").Value, _
            .Fields("txtPPBI").Value, .Fields("txtPPBR").Value, .Fields("txtValue").Value, .Fields("txtStoreNum").Value, _
            0, mvarintUploadID, .Fields("HoldingID").Value _
            )
            DoEvents
            frmImport.pbDataImport.Value = ((.AbsolutePosition / intRecCount) * 250) + 750
            If lngResult = 0 Then UploadMainData = 0
            .MoveNext
        Else
            DoEvents
            frmImport.pbDataImport.Value = ((.AbsolutePosition / intRecCount) * 250) + 750
            UploadMainData = 0
            m_strLoadState = m_strLoadState & "Info: Insert Failed Holding in Rec: " & CLng(.Fields("HoldingID").Value) & vbCrLf
            Call HoldingErrors(CLng(.Fields("HoldingID").Value), "Invalid Date", "New", 0)
            .MoveNext
        End If
    Loop

[Bruce Fox]

How about creating a Type, and passing that as a Parameter - possibly ByRef too? (untested)

[zeezee]

And a Class would suit too..

[dfuas]

Could you please elaborate how to do that? this is an application which was developed initially by someone else and I do not have much knowledge of classes and creating type.
thanks a lot
df

[dfuas]

from the code I have above, could you please give me an example how to go about it, as this is new territory for me. thanks
df

[zeezee]

Here are some examples in classes and UDTs

http://www.developerfusion.co.uk/show/3959/5/
http://en.wikibooks.org/wiki/Program...ed_Programming

http://www.developerfusion.co.uk/show/51/5/
http://articles.techrepublic.com.com...3-5493772.html


For your situation , you can use both

Eg: 1 - UDT

In a module add a public type to it

Public Type MyType
    myName As String
    myAge As Integer
    myAddress As String
End Type

Then you can use this type as your parameter type in the function

Private Function InsertHoldingData(myData As MyType) As Long
    Debug.Print myData.myName
    Debug.Print myData.myAge
    Debug.Print myData.myAddress
End Function

To assign values to a UDT variable and pass to a function use like this

Dim MyUDTvar As MyType
Dim result As Long

    MyUDTvar.myName = "Test Name"
    MyUDTvar.myAge = 20
    MyUDTvar.myAddress = "Test Address"

result = InsertHoldingData(MyUDTvar)

The using of calss would be somewhat similar , but implemantation is bit different


EDIT : Wrong Names usd corrected

[anhn]

I've never seen a procedure with that many arguments.

According to Bill Gates:
A procedure can have only 60 arguments.
If you must specify more arguments, define a user-defined type to collect multiple arguments of different types, or use a ParamArray as the final argument and pass multiple values to it. You can also pass multiple arguments by placing them in an array.

Howerver, I have counted parameters in your function and see that it has only 59 parameters???!!!

In this case instead of passing that many field values to function InsertHoldingData(), to make it simpler, you can pass the recordset to it.
With that you do not need to create any Class or UDT.

Private Function InsertHoldingData(rst As ADODB.Recordset) As Long

Within the function, you can use the passed recordset such as:

CDate(rst.Fields("txtBatchDate").Value)       instead of  dteBatchDate,
rst.Fields("txtBatch").Value                  instead of  BatchName
rst.Fields("txtURN").Value                    instead of  URN
rst.Fields("txtTitle").Value,                 instead of  Title
Left(Trim(rst.Fields("txtInitial").Value), 1) instead of  Initial
... ...

In case you have to use one field value many times, if you want to avoid the use of the lengthy formulas or references, you can declare variables within the function such as:

Dim dteBatchDate As Date
Dim Initial As String
'... ...
dteBatchDate = CDate(rst.Fields("txtBatchDate").Value)
Initial = Left(Trim(rst.Fields("txtInitial").Value), 1)

I hope this is the quickest solution for you.

[dfuas]

thanks a lot.
I will try this and post back.

df

[dfuas]

I am just trying the ahn solution. I want to know how to change Public Function UploadMainData, because when I use the passed recordset describe above

CDate(rst.Fields("txtBatchDate").Value)       instead of  dteBatchDate,
rst.Fields("txtBatch").Value                  instead of  BatchName
rst.Fields("txtURN").Value                    instead of  URN
rst.Fields("txtTitle").Value,                 instead of  Title
Left(Trim(rst.Fields("txtInitial").Value), 1) instead of  Initial
... ...

I get rst variable not defined error, unless I did not undertand you properly.

[leinad31]

Have you considered using

INSERT INTO mssql_tbl (fieldslist) SELECT fieldslist FROM csv_tbl IN 'local path of csv file' WHERE primary_key = specified_value

executed on mssql connection???

[anhn]

You have to pass rstResult from UploadMainData() to InsertHoldingData(), rstResult will become rst within the second function.
There are 2 parameters (Suppressed and UploadID) that do not belong to rstResult, you have to add them in the list.

Private Function InsertHoldingData(rst As ADODB.Recordset, _
                                   Suppressed As Integer, UploadID As Integer) As Long
'... ...
End Function


Public Function UploadMainData() As Integer
'... ...
If mvaroptNew = True Then 'If the user has elected to upload New Data
    .MoveFirst
    Do Until .EOF
        If IsDate(.Fields("txtInitialDate").Value) = True And IsDate(.Fields("txtBatchDate").Value) = True Then
            lngResult = InsertHoldingData(rstResult, 0, mvarintUploadID)
            '... ...
End Function

[leinad31]

You don't always have to populate recordsets to get data transferred between two databases.

[anhn]

I know there are many ways to do the job, however what I provided is just a simple solution to avoid the error "Too many parameters".
I don't touch the rest of the structure of dfuas' program. That is another matter.

[dfuas]

No, that is fine. Thanks I will try that again.

[dfuas]

Following anhn solution, it is working up to a point.
when it comes to the parameters they all set to nothing. E.g below code

    'Normal variables
    Dim strInitialDate As String
    Dim strLoadDate As String
    Dim strBatchDate As String
    Dim strBirthDay As String
    Dim rstInsertHoldingData As ADODB.Recordset
    Dim cmInsertHoldingData As ADODB.Command
    
       
           'Purchase Variables
    Dim prmStoreCardHolder As ADODB.Parameter
    Dim prmCDiorUser As ADODB.Parameter
    Dim prmSkinCareFocusDry As ADODB.Parameter
    Dim prmSkinCareFocusDehyd As ADODB.Parameter
    Dim prmSkinCareFocusFirstSignAge As ADODB.Parameter
    Dim prmSkinCareFocusVisibleAge As ADODB.Parameter
    Dim prmSkinCareFocusFirm As ADODB.Parameter
    Dim prmSkinCareFocusRadiance As ADODB.Parameter
    Dim prmSkinCareFocusOil As ADODB.Parameter
    Dim prmProdPurchBasics As ADODB.Parameter
    Dim prmProdPurchAA As ADODB.Parameter
    Dim prmProdPurchIOD As ADODB.Parameter
    Dim prmProdPurchPrestige As ADODB.Parameter
    Dim prmProdPurchFoundation As ADODB.Parameter
    Dim prmProdPurchMakeUp As ADODB.Parameter
    Dim prmProdPurchFF As ADODB.Parameter
    Dim prmProdPurchMF As ADODB.Parameter
    Dim prmProdPurch As ADODB.Parameter
    Dim prmdteLoaded As ADODB.Parameter
    Dim prmdteInitial As ADODB.Parameter
    Dim prmdteBatchDate As ADODB.Parameter
    Dim prmUploadID As ADODB.Parameter
    Dim prmBatchName As ADODB.Parameter
    Dim prmProdPurchCapture As ADODB.Parameter
    Dim prmProdPurchNoAge As ADODB.Parameter
    Dim prmProdPurchMove As ADODB.Parameter
    Dim prmProdPurchBikini As ADODB.Parameter
    Dim prmProdPurchBronze As ADODB.Parameter
    
    'Report Date
    Dim prmintReportDateURN As ADODB.Parameter
    
    'Store Parameters
    Dim prmStore As ADODB.Parameter
    
    'Output Paramter
    Dim prmResult As ADODB.Parameter
    
    'Clear errors collection
    Err.Clear
    
    'Address Table Parameters
    Set prmAddress1 = New ADODB.Parameter
    With prmAddress1
        .Direction = adParamInput
        .Type = adChar
        .Size = 50
        .Value = Trim(Address1)
    End With
    Set prmPostcode = New ADODB.Parameter
    With prmPostcode
        .Direction = adParamInput
        .Type = adChar
        .Size = 11
        .Value = Trim(Postcode) 'Has to be not null, .....

Is there something I did not undertand?
Thanks again for all your help
df

[anhn]

As said in Post#7, within Function InsertHoldingData() you have to replace:

dteBatchDate  with  CDate(rst.Fields("txtBatchDate").Value)
BatchName     with  rst.Fields("txtBatch").Value
URN           with  rst.Fields("txtURN").Value
Title         with  rst.Fields("txtTitle").Value
Initial       with  Left(Trim(rst.Fields("txtInitial").Value), 1)
... ...
Address1      with  rst.Fields("txtAdd1").Value
... ...

There are too many, I cannot list them all. Being smart!

I recommend you add the below line on top of the module:

Option Explicit

[dfuas]

I got this, thanks again.
df

[dfuas]

This is now working following anhn input. Just a few type mismatch errors that I have to fix.
Thanks everyone for the help
df

[dfuas]

hi again.

after 1 or 2 records this fails as type mismatch (CLng(HoldingID)= "".
This is a table generated Id and I can't figure out where or how to fix this.
It fails around here

If lngReturn <> 0 Then
        'Successfull Insert of repeat record
        strNewRptHolding = "Redemp"
        strErr = ""
        Call HoldingErrors(CLng(HoldingID), strErr, strNewRptHolding, 1)
    ElseIf lngReturn = 0 Then
        'Unsuccessfull Insert of repeat record
        strNewRptHolding = "Redemp"
        If GiftPoints > CustPoints Then
            strErr = "Not enough points available for redemption"
        Else
            strErr = "Insert failed at stored procedure level"
        End If
        m_strLoadState = m_strLoadState & "Info: Insert Failed Holding Record No: " & HoldingID & vbCrLf
        Call HoldingErrors(CLng(HoldingID), strErr, strNewRptHolding, 0)
    End If

most of the code is set above in N1#
thanks again for the help
df

[dfuas]

Sorry I cannot try this right now but just thought that in relation to my previous post if I had the HoldingID argument into Private Function InsertHoldingData and Public Function UploadMainData() As Integer should it solve the issue?

Private Function InsertHoldingData(rst As ADODB.Recordset, _
                                   Suppressed As Integer, UploadID As Integer, 

	
	
HoldingID As String
) As Long
'... ...
End Function


Public Function UploadMainData() As Integer
'... ...
If mvaroptNew = True Then 'If the user has elected to upload New Data
    .MoveFirst
    Do Until .EOF
        If IsDate(.Fields("txtInitialDate").Value) = True And IsDate(.Fields("txtBatchDate").Value) = True Then
            lngResult = InsertHoldingData(rstResult, 0, mvarintUploadID,

	
	
.Fields("HoldingID").Value
)
            '... ...
End Function

[zeezee]

Yes, you need to pass the values/variables that cannot be retrieved from your recordset. However, in your first post, it seems HoldingID is a field in the recordset. So it should be able to retrive from the recordset in InsertHoldingData.

If you can , please post your new function. and check whether HoldingID is not null.

[anhn]

zeezee is right, "HoldingID" is a field in the recordset so you don't need to pass it as a separate parameter.
However, again, within InsertHoldingData() function, you have to replace
HoldingID with rst.Fields("HoldingID").Value

[dfuas]

thanks a lot. is working fine now.
df