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

  • 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.

  • [font="Verdana"]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[/font]

    MH-09-AM-8694

  • 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")

  • 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:

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply