SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Lata Patel
Lata Patel
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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!

Smile

Lata Patel.
Mahesh Bote
Mahesh Bote
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1472 Visits: 1274
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
Lata Patel
Lata Patel
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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")
Lata Patel
Lata Patel
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
shamshudheen
shamshudheen
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search