[RESOLVED] VB6 complile error:Too many arguments
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
Code:
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
Code:
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
Re: VB6 complile error:Too many arguments
How about creating a Type, and passing that as a Parameter - possibly ByRef too? (untested)
Re: VB6 complile error:Too many arguments
And a Class would suit too..
Re: VB6 complile error:Too many arguments
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
Re: VB6 complile error:Too many arguments
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
Re: VB6 complile error:Too many arguments
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
Code:
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
Code:
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
Code:
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
:wave:
EDIT : Wrong Names usd corrected
Re: VB6 complile error:Too many arguments
I've never seen a procedure with that many arguments.
Quote:
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.
Code:
Private Function InsertHoldingData(rst As ADODB.Recordset) As Long
Within the function, you can use the passed recordset such as:
Code:
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:
Code:
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.
Re: VB6 complile error:Too many arguments
thanks a lot.
I will try this and post back.
df
Re: VB6 complile error:Too many arguments
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
Code:
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.
Re: VB6 complile error:Too many arguments
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???
Re: VB6 complile error:Too many arguments
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.
Code:
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
Re: VB6 complile error:Too many arguments
You don't always have to populate recordsets to get data transferred between two databases.
Re: VB6 complile error:Too many arguments
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.
Re: VB6 complile error:Too many arguments
No, that is fine. Thanks I will try that again.
Re: VB6 complile error:Too many arguments
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
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
Re: VB6 complile error:Too many arguments
As said in Post#7, within Function InsertHoldingData() you have to replace:
Code:
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:
Re: VB6 complile error:Too many arguments
I got this, thanks again.
df
Re: VB6 complile error:Too many arguments
This is now working following anhn input. Just a few type mismatch errors that I have to fix.
Thanks everyone for the help
df
Re: VB6 complile error:Too many arguments
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
Code:
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
Re: VB6 complile error:Too many arguments
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?
Code:
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,
vb Code:
.Fields("HoldingID").Value
)
'... ...
End Function
Re: VB6 complile error:Too many arguments
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.
Re: VB6 complile error:Too many arguments
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
Re: VB6 complile error:Too many arguments
thanks a lot. is working fine now.
df
