Procedure or function sp_AddDealer has too many arguments specified (error via SqlDataSource) RSS

• • Reply
  

  ckigar

  0 Points

  4 Posts

  Procedure or function sp_AddDealer has too many arguments specified (error via SqlDataSource)

  Apr 09, 2007 10:37 PM|LINK

  Procedure or function sp_AddDealer has too many arguments specified.

  
  I am experiencing the 'too many arguments specified' error.  I am running on SQL 2005.  The Parameters lists on SQL server (when I view a dropdown under the sp name) shows a 'returns integer'  (but without the @ the signifies a parameter).
  I have looked around the forums and haven't seen quite this flavor of this error.  My forehead is sore from beating it against the wall... any clue would be appreciated!

  The error occurs when I click the 'new' link button, enter some data and then click the update link button after ... BOOM - Procedure or function sp_AddDealer has too many arguments specified.

  Thanks!!

  Chip Kigar

  Here is the stored Procedure:

  set ANSI_NULLS ON
  set QUOTED_IDENTIFIER ON
  go

  
  ALTER PROCEDURE [dbo].[sp_AddDealer]  @sCarcareSystem varchar(100),
   @sDealerName varchar(50),
  @sDealerPhysAddrStreet varchar(200),
  @sDealerPhysAddrCity varchar(100),
  @sDealerPhysAddrState varchar(10),
  @sDealerPhysAddrZip varchar(20),
   @nReturnCode bigint output,
   @nReturnId bigint output AS

   SET NOCOUNT ON
   
   DECLARE @m_nCt bigint
   
   SET @nReturnCode = 0
   SET @nReturnId = 0

   -- VALIDATE
   IF (@nReturnCode = 0)
   BEGIN
    SELECT
     @m_nCt = COUNT(tblDealers.[_DealerId])
     FROM
     tblDealers
     WHERE
     [Dealer Name] = @sDealerName
   
    IF (@m_nCt >0)
    BEGIN
     SET @nReturnCode = -2000 --'Error for exsiting Dealer'
     SET @nReturnId = 0
    END
   END

   -- PROCESS
   IF (@nReturnCode = 0)
   BEGIN
    SET @nReturnCode = -2 --' Error getting new record id'
    DECLARE @m_nNewRecId bigint
    SET @m_nNewRecId = 0
    EXEC sp_GetNewRecId @m_nNewRecId output
    IF (@m_nNewRecId > 0)
    BEGIN
     SET @nReturnCode = -1 --'Error adding Dealer'
     INSERT INTO
      tblDealers
      (
      [_DealerId],
      [Carcare System],
      [Dealer Name],
      [Dealer Phys Addr Street],
      [Dealer Phys Addr City],
      [Dealer Phys Addr State],
      [Dealer Phys Addr Zip]
      )
      VALUES
      (
      @m_nNewRecId,
      @sCarcareSystem,
      @sDealerName,
      @sDealerPhysAddrStreet,
      @sDealerPhysAddrCity,
      @sDealerPhysAddrState,
      @sDealerPhysAddrZip
      )
   
     SET @nReturnCode = 0 --'Success'
     SET @nReturnId = @m_nNewRecId

     END
   
   END

   

  Here is the SQLDataSource.  I plugged the ID parameter, so I got a schema back, but no data.

      <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
          InsertCommand="sp_AddDealer" InsertCommandType="StoredProcedure"
          SelectCommand="sp_GetDealerByDealerId"   SelectCommandType="StoredProcedure">
          <SelectParameters>
              <asp:Parameter DefaultValue="2" Name="nDealerId" Type="Int64" />
              <asp:Parameter DefaultValue="" Direction="Output" Name="nReturnCode" Type="Int64" />
              <asp:Parameter Direction="Output" Name="nReturnId" Type="Int64" />
          </SelectParameters>
          <InsertParameters>
              <asp:Parameter Name="sCarcareSystem" Type="String" />
              <asp:Parameter Name="sDealerName" Type="String" />
              <asp:Parameter Name="sDealerPhysAddrStreet" Type="String" />
              <asp:Parameter Name="sDealerPhysAddrCity" Type="String" />
              <asp:Parameter Name="sDealerPhysAddrState" Type="String" />
              <asp:Parameter Name="sDealerPhysAddrZip" Type="String" />
              <asp:Parameter Direction="InputOutput" Name="nReturnCode" Type="Int64" />
              <asp:Parameter Direction="InputOutput" Name="nReturnId" Type="Int64" />
          </InsertParameters>
      </asp:SqlDataSource>

  
  Here is the Formview:

      <asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1">
          <EditItemTemplate>
              _DealerId:
              <asp:TextBox ID="_DealerIdTextBox" runat="server" Text='<%# Bind("_DealerId") %>'>
              </asp:TextBox><br />
              Carcare System:
              <asp:TextBox ID="Carcare_SystemTextBox" runat="server" Text='<%# Bind("[Carcare System]") %>'>
              </asp:TextBox><br />
              Dealer Name:
              <asp:TextBox ID="Dealer_NameTextBox" runat="server" Text='<%# Bind("[Dealer Name]") %>'>
              </asp:TextBox><br />
              Address Street:
              <asp:TextBox ID="Address_StreetTextBox" runat="server" Text='<%# Bind("[Address Street]") %>'>
              </asp:TextBox><br />
              Address City:
              <asp:TextBox ID="Address_CityTextBox" runat="server" Text='<%# Bind("[Address City]") %>'>
              </asp:TextBox><br />
              Address State:
              <asp:TextBox ID="Address_StateTextBox" runat="server" Text='<%# Bind("[Address State]") %>'>
              </asp:TextBox><br />
              Address Zip:
              <asp:TextBox ID="Address_ZipTextBox" runat="server" Text='<%# Bind("[Address Zip]") %>'>
              </asp:TextBox><br />
              <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"
                  Text="Update">
              </asp:LinkButton>
              <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
                  Text="Cancel">
              </asp:LinkButton>
          </EditItemTemplate>
          <InsertItemTemplate>
              _DealerId:
              <asp:TextBox ID="_DealerIdTextBox" runat="server" Text='<%# Bind("_DealerId") %>'>
              </asp:TextBox><br />
              Carcare System:
              <asp:TextBox ID="Carcare_SystemTextBox" runat="server" Text='<%# Bind("[Carcare System]") %>'>
              </asp:TextBox><br />
              Dealer Name:
              <asp:TextBox ID="Dealer_NameTextBox" runat="server" Text='<%# Bind("[Dealer Name]") %>'>
              </asp:TextBox><br />
              Address Street:
              <asp:TextBox ID="Address_StreetTextBox" runat="server" Text='<%# Bind("[Address Street]") %>'>
              </asp:TextBox><br />
              Address City:
              <asp:TextBox ID="Address_CityTextBox" runat="server" Text='<%# Bind("[Address City]") %>'>
              </asp:TextBox><br />
              Address State:
              <asp:TextBox ID="Address_StateTextBox" runat="server" Text='<%# Bind("[Address State]") %>'>
              </asp:TextBox><br />
              Address Zip:
              <asp:TextBox ID="Address_ZipTextBox" runat="server" Text='<%# Bind("[Address Zip]") %>'>
              </asp:TextBox><br />
              <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
                  Text="Insert">
              </asp:LinkButton>
              <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
                  Text="Cancel">
              </asp:LinkButton>
          </InsertItemTemplate>
          <ItemTemplate>
              _DealerId:
              <asp:Label ID="_DealerIdLabel" runat="server" Text='<%# Bind("_DealerId") %>'>
              </asp:Label><br />
              Carcare System:
              <asp:Label ID="Carcare_SystemLabel" runat="server" Text='<%# Bind("[Carcare System]") %>'>
              </asp:Label><br />
              Dealer Name:
              <asp:Label ID="Dealer_NameLabel" runat="server" Text='<%# Bind("[Dealer Name]") %>'>
              </asp:Label><br />
              Address Street:
              <asp:Label ID="Address_StreetLabel" runat="server" Text='<%# Bind("[Address Street]") %>'>
              </asp:Label><br />
              Address City:
              <asp:Label ID="Address_CityLabel" runat="server" Text='<%# Bind("[Address City]") %>'>
              </asp:Label><br />
              Address State:
              <asp:Label ID="Address_StateLabel" runat="server" Text='<%# Bind("[Address State]") %>'>
              </asp:Label><br />
              Address Zip:
              <asp:Label ID="Address_ZipLabel" runat="server" Text='<%# Bind("[Address Zip]") %>'>
              </asp:Label><br />
              <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"
                  Text="New">
              </asp:LinkButton>
          </ItemTemplate>
      </asp:FormView>
  

  Stored Procedures SqlDataSource

  Chip Kigar
  • Moved by SomeNewKid on Apr 10, 2007 12:32 AM
• • Reply
  

  busyweb

  Member

  706 Points

  121 Posts

  Re: Procedure or function sp_AddDealer has too many arguments specified (error via SqlDataSource)

  Apr 10, 2007 01:57 AM|LINK

  Hello,

  I wonder why these two parameter direction is InputOutput, it should be Output???

              <asp:Parameter Direction="InputOutput" Name="nReturnCode" Type="Int64" />
              <asp:Parameter Direction="InputOutput" Name="nReturnId" Type="Int64" />
  

  Is it made by Visual Studio?

  for InputOutput direction, stored procedure expects any input value from caller, at least Default Value ..

  Why don't you try add default value for these parameters like DBNull.value or 0(zero).

  Or you can set the default value on your Insert Stored Procedure.

  ALTER PROCEDURE [dbo].[sp_AddDealer]  @sCarcareSystem varchar(100),
   @sDealerName varchar(50),
  @sDealerPhysAddrStreet varchar(200),
  @sDealerPhysAddrCity varchar(100),
  @sDealerPhysAddrState varchar(10),
  @sDealerPhysAddrZip varchar(20),
   @nReturnCode bigint = null output,
   @nReturnId bigint = null output AS

   

  Try, and let me know how you solved. Curious!!

• • Reply
  

  ckigar

  0 Points

  4 Posts

  Re: Procedure or function sp_AddDealer has too many arguments specified (error via SqlDataSource)

  Apr 10, 2007 02:18 AM|LINK

  The visual studio 'configure data source' indeed forces the parameters to InputOutput.  I did go in and hand tweak the generated ASP:datasource code and set them to output without altering my results.  I was anticipating being able to test the returned values in the insert_completed event.
  

  It's odd to me that what I would consider to be the same functionality in CODE works, and this does not.  It will certain add a lot of dev time if I cannot use form and SQLDatasource
  

  Chip Kigar
• • Reply
  

  ckigar

  0 Points

  4 Posts

  Re: Procedure or function sp_AddDealer has too many arguments specified (error via SqlDataSource)

  Apr 12, 2007 02:09 PM|LINK

  The answer:

  The COLUMNS produced by the select query MUST match in name and quantity with the input parameters of the UpdateCommand.  These select column names are used in the grid columns, and subsequently 'bound' to the named parameters in the Update command. So, essentially, this error is resolved by modifying the stored procedures.

  For example:

  
  -----Select Query--------------------
  sp_GetDealerGuideAssetsByDealerId] @nDealerId bigint, @nReturnCode bigint output, @nReturnId bigint output AS
  
      SET @nReturnCode = 0
      SET @nReturnId = 0
      SELECT
          ISNULL(tblDealerGuideAssets.[_DealerGuideAssetsId],0) as [nDealerGuideAssetsId],
          ISNULL(tblDealerGuideAssets.[Model Size],0) as [nModelSize],       
          ISNULL(tblDealerGuideAssets.[Assets],0) as nAsset,
          ISNULL(tblDealerGuideAssets.[_DealerId],0) as [nDealerId]
          FROM
          tblDealerGuideAssets
          WHERE
          (tblDealerGuideAssets.[_DealerId] = @nDealerId)
          ORDER BY
          tblDealerGuideAssets.[Model Size]
  
      SET @nReturnCode = 0
      SET @nReturnId = 0
  -----Udate --------------------------
  sp_EditDealerGuideAssetByDealerGuideAssetsId]
  @nDealerGuideAssetsId bigint,
  @nDealerId bigint,
  @nModelSize bigint,
  @nAsset money,
  @nReturnCode bigint output,
  @nReturnId bigint output AS
  
      SET @nReturnCode = 0
      SET @nReturnId = 0
  
      DECLARE @m_nCt bigint
  
  
      BEGIN
          UPDATE
              tblDealerGuideAssets
              SET
              [_DealerId] = @nDealerId,            [Model Size] = @nModelSize,            [Assets] = @nAsset
              WHERE
              ([_DealerGuideAssetsId] = @nDealerGuideAssetsId)
                 
          SET @nReturnCode = 0
          SET @nReturnId = @nDealerGuideAssetsId    
  

   

  two other points:  running the 'configure' wizard seems to reset the parameter directions.  I found it was necessary to edit the ASP to ensure that all the Direction="Input" and Direction="Output" were explicit
  

   

  <%@ Page Language="vb" AutoEventWireup="false" MasterPageFile="~/Dealer.Master" CodeBehind="DealerAssets.aspx.vb" Inherits="hrs2.WebForm16"
      title="Untitled Page" %>
  <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
      <asp:Label ID="Label1" runat="server" Text="Label" Width="249px"></asp:Label>&nbsp;
      <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HRS Fast AccessConnectionString %>"
          SelectCommand="sp_GetDealerAssetsByDealerId" SelectCommandType="StoredProcedure"
          UpdateCommand="sp_EditDealerAssetByDealerGuideAssetsId" UpdateCommandType="StoredProcedure">
          <UpdateParameters>
              <asp:Parameter  Direction="Input" Name="nDealerGuideAssetsId" Type="Int64" />
              <asp:Parameter  Direction="Input" Name="nDealerId" Type="Int64" />
              <asp:Parameter  Direction="Input" Name="nModelSize" Type="Int64" />
              <asp:Parameter  Direction="Input" Name="nAsset" Type="Decimal" />
              <asp:Parameter Direction="Output" Name="nReturnCode" Type="Int64" />
              <asp:Parameter Direction="Output" Name="nReturnId" Type="Int64" />
          </UpdateParameters>
          <SelectParameters>
              <asp:Parameter Direction="Input" DefaultValue="24" Name="nDealerId" Type="Int64" />
              <asp:Parameter Direction="Output" Name="nReturnCode" Type="Int64" />
              <asp:Parameter Direction="Output" Name="nReturnId" Type="Int64" />
          </SelectParameters>
      </asp:SqlDataSource>
      <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" DataKeyNames="nDealerGuideAssetsId,nDealerId">
          <Columns>
              <asp:CommandField ShowEditButton="True" />
              <asp:BoundField DataField="nDealerGuideAssetsId" HeaderText="nDealerGuideAssetsId"                ReadOnly="True" SortExpression="nDealerGuideAssetsId" />
              <asp:BoundField DataField="nDealerId" HeaderText="nDealerId" ReadOnly="True"                SortExpression="nDealerId" />
              <asp:BoundField DataField="nModelSize" HeaderText="nModelSize"                SortExpression="nModelSize" />
              <asp:BoundField DataField="nAsset" HeaderText="nAsset" ReadOnly="True" SortExpression="nAsset" />
          </Columns>
      </asp:GridView>
  </asp:Content>
   

  Chip Kigar
  • Marked as answer by ckigar on Apr 12, 2007 02:10 PM
• • Reply
  

  mcupryk

  Member

  243 Points

  394 Posts

  Re: Procedure or function sp_AddDealer has too many arguments specified (error via SqlDataSource)

  Aug 01, 2012 01:27 PM|LINK

  Can u help me with http://forums.asp.net/p/1829050/5091410.aspx/1?p=True&t=634794096147336323

  www.omegalove.com