Procedure or function ... has too many arguments specified RSS

• • Reply
  

  mchinta

  Participant

  1765 Points

  523 Posts

  Procedure or function ... has too many arguments specified

  Sep 27, 2010 08:35 PM|LINK

  When i execute this SP in DB, it works fine. But when i execute from asp.net app it is throwing error:

  Procedure or function ... has too many arguments specified

  
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROC [dbo].[sp_Customer]
  
  @brchID char(40),
  @brchName VARCHAR(50),
  @userID VARCHAR(50)
  
  AS
  
  BEGIN
  TRANSACTION
  
  DECLARE @req_pk  int
  DECLARE @customer_org_pk int
  
  IF EXISTS(SELECT * FROM [customer] WHERE [org_id] like @brchID)
  
  BEGIN
      SELECT [org_pk] FROM [customer] WHERE [org_id] like @brchID
  END
  
  
  ELSE
  
  BEGIN
      EXEC sp_get_next_control_no 'organization.org_pk',@customer_org_pk out;
      
      INSERT INTO [customer] (org_pk,[org_id],[org_name])
      VALUES(@customer_org_pk,@brchID,@brchName)
      
      SELECT [org_pk] FROM [customer] WHERE [org_id] like @brchID
  END
  
  --Address Book Entry
  
  IF NOT EXISTS(SELECT * FROM [address_book] WHERE [prsn_id] like @userID)
  
  BEGIN
      exec sp_get_next_control_no 'person.prsn_pk',@req_pk out;
  END
  
  IF (@@ERROR <> 0)
  
  BEGIN
  
  -- Rollback the transaction
  
  ROLLBACK
  
  RETURN
  
  END
  
  
  ELSE
  
  BEGIN
  
  COMMIT
  
  END

  
  

  Here is my asp method:

  public static string Customer(string brchId, string brchName, string userId)            
          {
              string spName = "st_Customer";
  
              SqlParameter OrgPk = new SqlParameter("@org_pk", SqlDbType.Int);
              OrgPk.Direction = ParameterDirection.Output;
              OrgPk.Size = 50;
  
              try
              {
                  SqlHelper.ExecuteNonQuery(Config.ConnectionString,
                       CommandType.StoredProcedure, spName,
                       new SqlParameter("@brchID", brchId),
                       new SqlParameter("@brchName", brchName),
                       new SqlParameter("@userID", userId),
                       OrgPk
                       );
  
                  return Convert.ToString(OrgPk.Value);
              }

  }

  
  

  Any ideas?

  
  

  Please mark the post as Answer if it helps you!
  • Moved by Mikesdotnetting on Sep 27, 2010 09:40 PM
• • Reply
  

  sansan

  All-Star

  53942 Points

  8147 Posts

  Re: Procedure or function ... has too many arguments specified

  Sep 27, 2010 09:45 PM|LINK

  mchinta

         SqlParameter OrgPk = new SqlParameter("@org_pk", SqlDbType.Int);
              OrgPk.Direction = ParameterDirection.Output;
              OrgPk.Size = 50;

  You haven't added this OrgPk as output parameter in the procedure. add it in procedure(@org_pk INT OUTPUT) and set some value to the parameter instead of output parameter in 

   

  mchinta

      EXEC sp_get_next_control_no 'organization.org_pk',@customer_org_pk out;

  Then you should be able to retrieve the value after executing procedure.

  Santhosh
  • Marked as answer by mchinta on Sep 29, 2010 12:37 AM
• • Reply
  

  mchinta

  Participant

  1765 Points

  523 Posts

  Re: Procedure or function ... has too many arguments specified

  Sep 28, 2010 12:08 AM|LINK

  Thanks,

  I added output param to SP

  ALTER PROC [dbo].[st_AddCustomer]
  
  @brchID VARCHAR(40),
  @brchName VARCHAR(50),
  @userID VARCHAR(50),
  @org_pk int output

  
  

  Now im getting output as null value in asp.net app . Do i need to change any more else? What do you mean by 

  set some value to the parameter instead of output parameter in 

  EXEC sp_get_next_control_no 'organization.org_pk',@customer_org_pk out; ?

  Please mark the post as Answer if it helps you!
  • Edited by mchinta on Sep 28, 2010 02:33 AM
• • Reply
  

  KumarHarsh

  All-Star

  15133 Points

  3647 Posts

  Re: Procedure or function ... has too many arguments specified

  Sep 28, 2010 05:50 AM|LINK

  you are getting null in output parameter bacause you are not returning anything from SP.
  
  Declare a parameter as out in SP and return that and again catch the same parameter in front end code. 

  Warm Regards
  Kumar Harsh
  
• • Reply
  

  sureshmdb

  Member

  376 Points

  81 Posts

  Re: Procedure or function ... has too many arguments specified

  Sep 28, 2010 06:30 AM|LINK

  Hi,

   

          You need to assign the value to oupt parameter,otherwise you will get null value

  here is the code please check it, you replace the statement

  SELECT [org_pk] FROM [customer] WHERE [org_id] like @brchID

  with SELECT @org_pk = [org_pk] FROM [customer] WHERE [org_id] like @brchID

  then @org_pk value assigned ,you can consume this value in asp.net application

  Suresh Mediboyina
  
  
  
  Mark as answer if my response is useful to you.
  • Marked as answer by mchinta on Sep 29, 2010 12:37 AM