Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

VB run-time error : Procedure or function spSaveCasteCerti has too many arguments specified. Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2008 2:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 29, 2008 3:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:10 AM
Points: 1,093, Visits: 1,221
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
Posted Tuesday, April 29, 2008 3:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, May 13, 2008 12:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.....
Post #499346
Posted Thursday, May 15, 2008 2:29 AM
SSC-Enthusiastic

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


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 »

Add to briefcase

Permissions Expand / Collapse