Error 30001: Arguments are of the Wrong Type (1 Viewer)

[Oludare]

Hi All,

Please help . I received the errors below when i call a stored procedure with input parameters.

Error 3001 Arguments are of the wrong type, are out of acceptable range , or are in conflict with one another

The error occured at line Set prm = cmd.CreateParameter("@BordNo", adVarChar, adParamInput, 50, strParentBordNo)


Private Sub UpdateReissueStgData()
On Error GoTo UpdateReissue_ERR
'Connect to SQL Server
Dim cn As ADODB.Connection
Set cn = modDataHelper.GetAdodbConnection
Dim strParentBordNo As String
Dim strReissueStatus As String
strParentBordNo = Nz(Forms!frmReissuePaymentsMain.CboBordereau, "")
strReissueStatus = "'Pending'"

'Configure the command
Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandTimeout = cn.CommandTimeout
cmd.CommandText = "spUpdateStgReissueData"
cmd.CommandType = adCmdStoredProc
Dim prm As ADODB.Parameter
Set prm = cmd.CreateParameter("@BordNo", adVarChar, adParamInput, 50, strParentBordNo)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@BordItem", adInteger, adParamInput, , Me.txtBordItem)
cmd.Parameters.Append prm
'Set prm = cmd.CreateParameter("@EstateNamePrefix", adVarChar, adParamInput, 10, Me.cboEstatepfx)
'cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@PolicyHolder", adVarChar, adParamInput, 255, Me.txtPolicyHolderName)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@PolicyNo", adVarChar, adParamInput, 50, Me.txtPolicyNo)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@GrossAmount", adInteger, adParamInput, , Me.txtGrossAmount)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@NetAmount", adInteger, adParamInput, , Me.txtNetAmount)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@SupportingDocuments", adVarChar, adParamInput, 10, Me.cboSupportDoc)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@AccountNo", adInteger, adParamInput, , Me.txtAccountNo)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@SortCode", adInteger, adParamInput, , Me.txtSortCode)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@Urgent", adVarChar, adParamInput, 10, Me.cboUrgent)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@PaymentMethod", adVarChar, adParamInput, 50, Me.cboPaymentMthd)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@PayAt100Pct", adInteger, adParamInput, , Me.txtPayAt100Pct)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@PayAt90Pct", adInteger, adParamInput, , Me.txtPayAt90Pct)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@PaymentReceipientRefNo", adVarChar, adParamInput, 50, Me.txtPaymentRecipientRefNo)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ClaimantDOB", adVarChar, adParamInput, 50, Me.txtClaimantDOB)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@PayeeDOB", adVarChar, adParamInput, 50, Me.txtPayeeDOB)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@PayeeEmailAddress", adVarChar, adParamInput, 255, Me.txtPayeeEmailAddress)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ClaimantTitle", adVarChar, adParamInput, 50, Me.cboClaimantTitle)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ClaimantForename", adVarChar, adParamInput, 50, Me.txtClaimantForename)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ClaimantSurname", adVarChar, adParamInput, 50, Me.txtClaimantSurname)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@PayeeTitle", adVarChar, adParamInput, 50, Me.cboPayeeTitle)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@PayeeForename", adVarChar, adParamInput, 50, Me.txtPayeeForename)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@PayeeSurname", adVarChar, adParamInput, 50, Me.txtPayeeSurname)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ChequeRecipientTitle", adVarChar, adParamInput, 50, Me.cboChequeTitle)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ChequeRecipientForename", adVarChar, adParamInput, 50, Me.txtChequeForename)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ChequeRecipientSurname", adVarChar, adParamInput, 50, Me.txtChequeSurname)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ChequeRecipientAddressLine1", adVarChar, adParamInput, 100, Me.txtChequeAddressLine1)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ChequeRecipientAddressLine2", adVarChar, adParamInput, 100, Me.txtChequeAddressLine2)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ChequeRecipientAddressLine3", adVarChar, adParamInput, 100, Me.txtChequeAddressLine3)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ChequeRecipientTownCity", adVarChar, adParamInput, 100, Me.txtChequeTownCity)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ChequeRecipientCounty", adVarChar, adParamInput, 100, Me.txtChequeCounty)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ChequeRecipientCountry", adVarChar, adParamInput, 50, Me.txtChequeCountry)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ChequeRecipientPostCode", adVarChar, adParamInput, 50, Me.txtChequePostCode)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ReissueReason", adVarChar, adParamInput, 100, Me.cboReissueReason)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ReissueStatus", adVarChar, adParamInput, 50, strReissueStatus)
cmd.Parameters.Append prm
cmd.Execute

'Close and release ADO resources
On Error Resume Next
if Not cmd Is Nothing Then
Set cmd = Nothing
End If
If Not cn Is Nothing Then
cn.Close
Set cn = Nothing
End If

UpdateReissue_Exit:
Exit Sub

UpdateReissue_ERR:
modMessageHelper.ShowErrorMessage Err.Description, "frmInsertReissueRecord.UpdateReissue_Cmdsave_Click", Err.Number
Resume UpdateReissue_Exit
End Sub

 

[Isaac]

Apparently your sql procedure spUpdateStgReissueData requires datatypes passed in different than your form controls are yielding. Maybe postcode? Ask the guy who wrote the stored procedure if you're unsure. Maybe you need some CLNG()'s in there , wrapping your form controls' values.

 

[Oludare]

Hello Isaac, This code has been working before. The @BordNo in the stored procedure has the datatype Nvarchar(50).

 

[Isaac]

You've got numerous other ones too. The error is telling you that the datatype being passed in is wrong on at least one of them. One might be null, or empty, or text when it should be numeric (likely), etc.

 

[Oludare]

Hello Isaac. Thanks issue resolved.

 

[Minty]

I can't help thinking the process would be easier with a simple update or insert query?
This looks like a lot of hard work to update or insert one record.

I can understand passing parameters in a complicated process through to a stored procedure, but see no benefit here unless I'm missing something.

 

[Isaac]

But Minty who knows what the procedure actually does?

 

[Minty]

But Minty who knows what the procedure actually does?

True.
But If an SP needs that many parameters to achieve something other than an Insert or Update of a whole record I'd love to see what it does. It must involve space flight or predicting lottery wins.

If it just an insert/update I'd dump the lot into a local temp table from a bound form and then run the appropriate query.