error sql server too many parameters

[holly]

Hi

Ive created a stored procedure in sql 2ooo which nsimply inserts a record into a table
SP

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--PURPOSE
--Inserts a new quote into the TabQMS_quotes table
--

ALTER      PROCEDURE up_Ins_NewQuote (@Contact_Char varchar (20), @Status varchar
(20), @AC_No varchar(10), @RiskName_char varchar(50), @Renewal_date datetime,
@TargetPremium_cur money,
@TargetInsurer_char varchar(50), @Carrier_char varchar(50), @QuoteReference_char
varchar(50), @CarrierSource_char varchar (50), 
@QuotedPremium_cur money, @Terms_note varchar(300), @CreditTerms_char varchar(20),
@NextDiary_date as datetime, @RiskTransfer_char varchar(10), @BrkComm_per char(20),
@BDComm_per char(10), @ProductCode_char varchar(10),
@DiaryReason_note varchar(50)) as 

--Declare Variables
declare
--@QuoteRefID int,
@Risk_No varchar(10),
@Contact_No int,
@MaxQuote int,
@gnCodeInt int,
@RecievedDate datetime

set @RecievedDate = getdate()

set @Contact_No = (SELECT REF_ID FROM tabQMS_ContactList WHERE REF_ID = any (SELECT
Ref_ID FROM tabQMS_ContactList WHERE (tabQMS_Contactlist.Contact_Char) =
@Contact_Char ))
set @Risk_No = (SELECT RefID FROM tabQMS_RiskDetails WHERE RefID = any (SELECT RefID
FROM tabQMS_RiskDetails WHERE (tabQMS_RiskDetails.[Name]) = @RiskName_char ))

if @status <> '0' begin set @status = 'Declined' end
else 
begin set @status = 'Current' end 

Insert into tabQMS_Quotes( 
--QuoteRef_ID,
Status,        
AC_No,
Contact_No,
Risk_No,
RiskName_char,
Renewal_date,
TargetPremium_cur,
TargetInsurer_char,
Carrier_char,
QuoteReference_char,
CarrierSource_char,
QuotedPremium_cur,
Terms_note,
CreditTerms_char,
NextDiary_date        ,
RiskTransfer_char,
BrkComm_per,
BDComm_per,
ProductCode_char,
DiaryReason_note,
Recieved_date)

values(
--@QuoteRefID,#
@status,
@Ac_No, 
@Contact_No,
@Risk_No,
@RiskName_char,
@Renewal_date,
@TargetPremium_cur,
@TargetInsurer_char,
@Carrier_char,
@QuoteReference_char,
@CarrierSource_char,
@QuotedPremium_cur,
@Terms_note,
@CreditTerms_char,
@NextDiary_date        ,
@RiskTransfer_char,
@BrkComm_per,
@BDComm_per,
@ProductCode_char,
@DiaryReason_note,
@RecievedDate)

Set @MaxQuote = (SELECT QuoteRef_ID FROM tabQMS_Quotes WHERE QuoteRef_ID = any
(SELECT mAX(QuoteRef_ID) as QuoteNum from tabQMS_Quotes))

if @status = 'Declined' begin set @gnCodeInt = '1301' end --declined
else
begin set @gnCodeInt = '1300' end --curent


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

then I call the SP using the below code in MS ACCESS 2000 BUT i RECEIVE AN ERROR TOO MANY PARAMETERS IN THE SQL SERVER sp...IS THERE ? AND IF THERE IS NOT HOW CAN i FIX MY LITTLE PROBLEM!! THANKS

Public Sub AddNewQuote()
On Error GoTo Err_Ac_No_AfterUpdate

    Dim stdoc As String
    Dim rsSet As New ADODB.Recordset
    Dim ObjCmd As Command
    Dim QuoteRefID As Integer
    Dim reReply_Str As String
    Dim gnCode As Integer
    
    Set rsSet = New ADODB.Recordset
    
    With rsSet
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .CursorType = adOpenDynamic
    End With
    
    Set ObjCmd = New Command
          
          With ObjCmd
   
            .ActiveConnection = gEDIBass
            .CommandType = adCmdStoredProc
            .CommandText = "Up_Ins_NewQuote"
            .Parameters.Append .CreateParameter("@QuoteRefID", adInteger,
adParamReturnValue)
            .Parameters.Append .CreateParameter("@gnCodeInt", adInteger,
adParamReturnValue)
            .Parameters.Append .CreateParameter("@status", adVarChar, adParamInput,
20, Declined)
            .Parameters.Append .CreateParameter("@Ac_No", adVarChar, adParamInput,
10, AC_NO)
            .Parameters.Append .CreateParameter("@Contact_No", adInteger,
adParamReturnValue)
            .Parameters.Append .CreateParameter("@Risk_No", adInteger,
adParamReturnValue)
            .Parameters.Append .CreateParameter("@Contact_Char", adVarChar,
adParamInput, 20, CmboContact)
            .Parameters.Append .CreateParameter("@RiskName_char", adVarChar,
adParamInput, 50, Risk_Name)
            .Parameters.Append .CreateParameter("@Renewal_date", adDBTimeStamp,
adParamInput, 12, Renewal_date)
            .Parameters.Append .CreateParameter("@TargetPremium_cur", adInteger,
adParamInput, 12, TargetPremium_cur)
            .Parameters.Append .CreateParameter("@TargetInsurer_char", adVarChar,
adParamInput, 12, TargetInsurer_char)
            .Parameters.Append .CreateParameter("@Carrier_char", adVarChar,
adParamInput, 12, Carrier_char)
            .Parameters.Append .CreateParameter("@QuoteReference_char", adVarChar,
adParamInput, 12, Quote_Ref)
            .Parameters.Append .CreateParameter("@CarrierSource_char", adVarChar,
adParamInput, 50, source_char)
            .Parameters.Append .CreateParameter("@QuotedPremium_cur", adVarChar,
adParamInput, 12, QuotedPremium_cur)
            .Parameters.Append .CreateParameter("@Terms_note", adVarChar,
adParamInput, 12, Terms_note)
            .Parameters.Append .CreateParameter("@CreditTerms_char", adVarChar,
adParamInput, 12, cmboCreditTerm)
            .Parameters.Append .CreateParameter("@NextDiary_date", adDBTimeStamp,
adParamInput, 12, NextDiary_date)
            .Parameters.Append .CreateParameter("@RiskTransfer_char", adVarChar,
adParamInput, 12, cmboRiskTransfer)
            .Parameters.Append .CreateParameter("@BrkComm_per", adVarChar,
adParamInput, 12, BrkComm_per)
            .Parameters.Append .CreateParameter("@BDComm_per", adVarChar,
adParamInput, 12, BDComm_per)
            .Parameters.Append .CreateParameter("@ProductCode_char", adVarChar,
adParamInput, 12, CmboProduct)
            .Parameters.Append .CreateParameter("@DiaryReason_note", adVarChar,
adParamInput, 12, DiaryReason_char)
    
    
        End With
    
    Set rsSet = ObjCmd.Execute
    
    QuoteRefID = rsSet.Fields("@QuoteRefID")
    gnCode = rsSet.Fields("gnCode_int")
    
    gnNote_Str = "New Quote - "
    
    If IsNull(Me.QuoteComments_note) = False Then
        gnNote_Str = gnNote_Str & Me.QuoteComments_note
    End If
    AddNote
     
    reReply_Str = MsgBox("Your Quote has been submitted" & vbCrLf & "Quote
Reference" & " " & QuoteRefID, vbOK, "Quote Submitted")
    
    
  
    rsSet.Close
    Set ObjCmd = Nothing
  
Exit_Ac_No_AfterUpdate:
    Exit Sub

Err_Ac_No_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_Ac_No_AfterUpdate
    Resume

tHANKS

[techgnome]

Ok, well first off.... a stored procedure is only going to return ONE value.... and you're adding two return parameters.

Secondly, I don't see where you are RETURNing anything from the SP.

Lastly... the parameters you've added in code don't quite match the SP definition.
The first parameter in your SP is @Contact_Char varchar (20).... but in your code, you've added two return parameters (I've already addressed that), skipped Contact_Char, and went straight to Status.

-tg

[holly]

Thanks for replying...

the two returned parameters..especially the quoterefid are neede dto return to the user that the quote id has sucessfull been added. secondly the gncodeint is needed to be returned inorder to add notes and the code integer value to another table.
and thirdly..I only need to pass the contact_char as a input parameter to the SP

Thanks

[GaryMazzone]

tg

Why is an SP limited to one return value only. Surely you can return more then one thing from and SP. Only one from a function but from and SP. At least in Oracle I can return as many things as I want (sent then as InputOut parameters).

[holly]

so, IS IT POSSIBLE to return more than one value with a SP???


if not how would I do the above?

[GaryMazzone]

I know it is in Oracle I thought it was in SQL Server. But tg is mch more versed in SQL Server then I am that is why I asked the question of him. I'll defer till I we hear from him or szlamay.

[techgnome]

Gary - InOut, yes you can have many of those.... they are defined as OUTPUT parameters..... In the VB code the first TWO parameters were added as type adParamReturnValue..... which means they are filled by using the RETURN keyword in SQL....which causes the SP to return immediately. So there's no way to RETURN two parameters like that.

Holly - I understand that contact_char is only an input ---- BTW ALL of your Parameters are input as far as the SP is concerned. I was trying to tell you that is is NOT in the VB code, therefore it never gets passed in.... Also, if you want those parameters to pass data back out of the SP, then 1) In the SP they need to be defined as OUTPUT parameters and 2) The VB code needs to have their direction changed to InnputOutput (or just Output) parameters.

The parameters in the SP and any parameters added in the VB code need to match up one for one.... (with the exception of a RETURN value).

-tg

[techgnome]

so, IS IT POSSIBLE to return more than one value with a SP???


if not how would I do the above?

Depends... (this was posted while I was writing my other post).... using ReturnValue parameters, no.... using OUPUT, yes....

-tg

[GaryMazzone]

Thanks for the clarification (on my part not yours) tg. I guess I misunderstood the Return concept from the SP. Thanks for the insight.

[holly]

Cheers, so, If I change my parameters to output and match the SP back with the code one for one...IT SHOULD WORK!!!!??

TG I ask one more favour ..whats the difference in specifying it as a return value and specifying it as a input/output parameter?

Ta

[techgnome]

It's not that far removed from the concept of VB functions for a single value vs using ByRef to "return" multiple values. When ADO sees the ReturnValue type, it's expecting SQL to return something using the RETURN keyword. Which is why I noted that nothing is being RETURNed. If multiple values are to be passed back, then the SP needs a little tweaking, and the ReturnValue direction setting needs to be changed to adParameterOutput.

It's a minor thing. But the Contact_char parameter still isn't being passed in the first place.

-tg

[holly]

Thanks for the explanation!! its something which I will not forget too easily!!

Ta

[techgnome]

Let me see if I can put this w/o confusing things:

Using RETURN:

Create Procedure AddNumber 
  @FirstNum Int,
  @SecondNum Int
As
RETURN @FirstNum + @SecondNum -- This is the RETURN I keept referring to

GO

From VB, I would create it like this:

.Parameters.Add .CreateParameter("@RetValue", adInteger, , adParameterReturnValue)
.Parameters.Add .CreateParameter("@FirstNum", adInteger, , adParameterInput, 15)
.Parameters.Add .CreateParameter("@SecondNum", adInteger, , adParameterInput, 20)

.Execute

MsgBox .Parameters("@RetValue").Value 'Produces 35.

Ouput works like this:

Create Procedure AddNumber2
  @FirstNum Int,
  @SecondNum Int,
  @SumNum int OUTPUT
As
Set @SumNum = @FirstNum + @SecondNum
GO

From VB, I would create it like this:

.Parameters.Add .CreateParameter("@FirstNum", adInteger, , adParameterInput, 15)
.Parameters.Add .CreateParameter("@SecondNum", adInteger, , adParameterInput, 20)
.Parameters.Add .CreateParameter("@SumNum", adInteger, , adParameterOutput)

.Execute

MsgBox .Parameters("@SumNum").Value 'Produces 35.

Now, it is possible to combine the two

Create Procedure AddNumber3
  @FirstNum Int,
  @SecondNum Int,
  @SumNum int OUTPUT
As
Set @SumNum = @FirstNum + @SecondNum
RETURN @FirstNum + @SecondNum 
GO

From VB, I would create it like this:

.Parameters.Add .CreateParameter("@RetValue", adInteger, , adParameterReturnValue)
.Parameters.Add .CreateParameter("@FirstNum", adInteger, , adParameterInput, 15)
.Parameters.Add .CreateParameter("@SecondNum", adInteger, , adParameterInput, 20)
.Parameters.Add .CreateParameter("@SumNum", adInteger, , adParameterOutput)

.Execute

MsgBox .Parameters("@RetValue").Value 'Produces 35.
MsgBox .Parameters("@SumNum").Value 'Produces 35.

-tg

[holly]

ta for the explanation !

so correct me if I am wrong but with RETURN you can do the manipulation of data inside a SP and return the value but with OUTPUT you have to do the manipulation outside of the SP!!??

[techgnome]

No. You can still do manipulations in SQL, it doesn't matter how you return the data. If you notice in my examples, all the manipulations are done inside the SP....the difference is in the number of things that can be passed back, and how you define the parameters in VB.

-tg