April 29, 2008 at 2:07 am
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.
April 29, 2008 at 3:27 am
[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
April 29, 2008 at 3:36 am
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")
May 13, 2008 at 12:46 am
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:
May 15, 2008 at 2:29 am
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