VB run-time error : Procedure or function spSaveCasteCerti has too many arguments specified.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » VB run-time error : Procedure or function...

VB run-time error : Procedure or function spSaveCasteCerti has too many arguments specified.

Rate Topic

Display Mode

Topic Options

Author

Message

Lata Patel

Posted Tuesday, April 29, 2008 2:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 8:48 AM
Points: 7, Visits: 31

Dear Frds!

Please Help me regarding above error .

I have made one SQL Stored Procedure for saving/updating record by passing some parameters.

USE [DCIMS]
GO
/****** Object: StoredProcedure [dbo].[spSaveCasteCertificate] Script Date: 04/29/2008 11:01:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spSaveCasteCertificate]
(
@caste_Certi_scst_id numeric(18,0),
@inward_no varchar(30),
@app_date datetime,
@aff_date datetime,
@leaving_certi nchar(1),
@birth_certi nchar(1),
@ration_card nchar(1),
@other_docs nchar(1),
@other_docs_det nvarchar(100),
@initial nvarchar(50),
@applicant_name varchar(100),
@fathers_name nvarchar(100),
@mothers_name nvarchar(100),
@surname nvarchar(100),
@resident_of nvarchar(200),
@caste nvarchar(100),
@village nvarchar(100),
@town nvarchar(100),
@rel_type nvarchar(100),
@panchas_date datetime,
@talathi_repo_date datetime,
@outward_no nvarchar(200),
@outward_date datetime,
@recordstatus nvarchar(1),
@modbyuserid numeric(18, 0),
@statusdate datetime,
@disabled varchar(1))

AS
BEGIN
SET NOCOUNT ON;
if @recordstatus = 'I' --' Insert Mode
begin
--First generate unique CasteCertiID and insert this id
set @caste_Certi_scst_id = ( select max(caste_Certi_scst_id) from caste_certi_for_scst )
set @caste_Certi_scst_id = isnull(@caste_Certi_scst_id,0) + 1 ;

insert into caste_certi_for_scst
( caste_Certi_scst_id, inward_no, app_date,aff_date, leaving_certi, birth_certi,ration_card,other_docs,other_docs_det,initial,applicant_name,fathers_name,mothers_name,surname,resident_of,caste,village,town,
rel_type,panchas_date,talathi_repo_date,outward_no,outward_date,
disabled, recordstatus, modbyuserid, statusdate )
values
( @caste_Certi_scst_id, @inward_no,@app_date,@aff_date,@leaving_certi,@birth_certi,@ration_card,@other_docs,@other_docs_det,@initial,@applicant_name, @fathers_name,@mothers_name,@surname,@resident_of,@caste,@village,@town,@rel_type,@panchas_date,@talathi_repo_date,@outward_no,@outward_date,@disabled, @recordstatus, @modbyuserid, @statusdate )
end
else
--' Update Mode
begin
update caste_certi_for_scst
set
inward_no=@inward_no ,
app_date=@app_date,
aff_date=@aff_date,
leaving_certi=@leaving_certi,
birth_certi=@birth_certi,
ration_card=@ration_card,
other_docs=@other_docs,
other_docs_det=@other_docs_det,
initial=@initial,
applicant_name=@applicant_name,
fathers_name=@fathers_name,
mothers_name=@mothers_name,
surname=@surname,
resident_of=@resident_of,
caste=@caste,
village=@village,
town=@town,
rel_type=@rel_type,
panchas_date=@panchas_date,
talathi_repo_date=@talathi_repo_date,
outward_no=@outward_no,
outward_date=@outward_date,
disabled=@disabled,
recordstatus = @recordstatus,
modbyuserid = @modbyuserid,
statusdate = getdate()
where
caste_Certi_scst_id = @caste_Certi_scst_id;
end

END

Now i have written code for Save Button using Command object to execute this procedure.

-- part of the save button code
Dim cmd As New ADODB.Command
Set cmd = New ADODB.Command

cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spSaveCasteCertificate"
cmd.Parameters.Refresh

cmd.Parameters.Append cmd.CreateParameter("caste_Certi_scst_id", adInteger, adParamInput, 10, "0")

cmd.Parameters.Append cmd.CreateParameter("inward_no", adBigInt, adParamInput, 10, Trim(txtApplicationInwardNumber.Text))

cmd.Parameters.Append cmd.CreateParameter("app_date", adDBTimeStamp, adParamInput, , Format(CDate(txtApplicationDate.Text), "dd/MM/yyyy"))

.....like other parameters

Set rsCaste = cmd.Execute

the last statement will give error that sp contains too many parameters.

In my case i required all this data fields compulsory. So Please Help me of there are another Solution to save record using Stored Procedure.

Hope you will help me soon.
Thanks in Advance!

:)

Lata Patel.

Post #491888

Mahesh Bote

Posted Tuesday, April 29, 2008 3:27 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 21, 2013 6:22 AM
Points: 1,074, Visits: 1,205

You are facing this error dues to mismatch of parameters. You are passing more parameters than what actually Stored Procedure requires. i.e. If you have 10 parameters in Stored Procedure you are passing more than 10 parameters to this Stored Procedure while calling.

Mahesh

MH-09-AM-8694

Post #491936

Lata Patel

Posted Tuesday, April 29, 2008 3:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 8:48 AM
Points: 7, Visits: 31

I have passed the parameters in the procedure like this...as i have written this in topic. Just now i have pasted all the data fields which i have passed through SP.

-- Parameters which are passing in SP

cmd.Parameters.Append cmd.CreateParameter("caste_Certi_scst_id", adInteger, adParamInput, 10, "0")
cmd.Parameters.Append cmd.CreateParameter("inward_no", adBigInt, adParamInput, 10, Trim(txtApplicationInwardNumber.Text))
cmd.Parameters.Append cmd.CreateParameter("app_date", adDBTimeStamp, adParamInput, , Format(CDate(txtApplicationDate.Text), "dd/MM/yyyy"))
cmd.Parameters.Append cmd.CreateParameter("aff_date", adDBTimeStamp, adParamInput, , Format(CDate(txtAffidavitDate.Text), "dd/MM/yyyy"))
cmd.Parameters.Append cmd.CreateParameter("leaving_certi", adChar, adParamInput, 6, IIf(chkLeavingCerti.Value = vbChecked, 1, 0))
cmd.Parameters.Append cmd.CreateParameter("birth_certi", adChar, adParamInput, 6, IIf(chkBirthCerti.Value = vbChecked, 1, 0))
cmd.Parameters.Append cmd.CreateParameter("ration_card", adChar, adParamInput, 6, IIf(chkRationCard.Value = vbChecked, 1, 0))
cmd.Parameters.Append cmd.CreateParameter("other_docs", adChar, adParamInput, 6, IIf(chkOther.Value = vbChecked, 1, 0))
If chkOther.Value = 1 Then
cmd.Parameters.Append cmd.CreateParameter("other_docs_det", adVarChar, adParamInput, 6, Val(txtOther.Text))
End If
cmd.Parameters.Append cmd.CreateParameter("initial", adVarChar, adParamInput, 50, IIf(cmbInitial.Text = "Select", "", cmbInitial.Text))
cmd.Parameters.Append cmd.CreateParameter("applicant_name", adVarChar, adParamInput, 100, ProperCase(txtApplicantName.Text))
cmd.Parameters.Append cmd.CreateParameter("fathers_name", adVarChar, adParamInput, 100, ProperCase(txtFathersName.Text))
cmd.Parameters.Append cmd.CreateParameter("mothers_name", adVarChar, adParamInput, 100, ProperCase(txtMothersName.Text))
cmd.Parameters.Append cmd.CreateParameter("surname", adChar, adParamInput, 100, ProperCase(txtSurname.Text))
cmd.Parameters.Append cmd.CreateParameter("resident_of", adVarChar, adParamInput, 100, cmbCaste.Text)

cmd.Parameters.Append cmd.CreateParameter("caste", adVarChar, adParamInput, 100, cmbCaste.Text)
cmd.Parameters.Append cmd.CreateParameter("village", adVarChar, adParamInput, 100, cmbVillage.Text)
cmd.Parameters.Append cmd.CreateParameter("town", adVarChar, adParamInput, 100, cmbTown.Text)
cmd.Parameters.Append cmd.CreateParameter("rel_type", adVarChar, adParamInput, 100, cmbRelationType.Text)
cmd.Parameters.Append cmd.CreateParameter("panchas_date", adDBTimeStamp, adParamInput, , Format(CDate(cmdStatementDate.Text), "dd/MM/yyyy"))
cmd.Parameters.Append cmd.CreateParameter("talathi_repo_date", adDBTimeStamp, adParamInput, , Format(CDate(cmdTalathiDate.Text), "dd/MM/yyyy"))
str1 = "MAM/DMN/CTC/" & Format(CDate(Now), "yyyy") & "/" & txtApplicationInwardNumber.Text
cmd.Parameters.Append cmd.CreateParameter("outward_no", adVarChar, adParamInput, 75, str1)
cmd.Parameters.Append cmd.CreateParameter("outward_date", adDBTimeStamp, adParamInput, , Now)

cmd.Parameters.Append cmd.CreateParameter("recordstatus", adChar, adParamInput, 6, "I")
cmd.Parameters.Append cmd.CreateParameter("modbyuserid", adInteger, adParamInput, 6, muserId)
cmd.Parameters.Append cmd.CreateParameter("statusdate", adDBTimeStamp, adParamInput, , Now)
cmd.Parameters.Append cmd.CreateParameter("disabled", adChar, adParamInput, 6, "n")

Post #491939

Lata Patel

Posted Tuesday, May 13, 2008 12:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 8:48 AM
Points: 7, Visits: 31

I have solved my error :

i have put following code before appending the parameters.

For C = 0 To cmd.Parameters.Count - 1
cmd.Parameters.Delete (0)
Next

and Problem is solved..... Cool

Post #499346

shamshudheen

Posted Thursday, May 15, 2008 2:29 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 10:50 PM
Points: 120, Visits: 211

Lata Patel (5/13/2008)

I have solved my error :

i have put following code before appending the parameters.

For C = 0 To cmd.Parameters.Count - 1
cmd.Parameters.Delete (0)
Next

and Problem is solved..... Cool

try to solve from your end first , if you can't then you post, it is normal in new comers.

Mostly try to depends on yourself

Post #501100

« Prev Topic | Next Topic »

Permissions