sqlDataSource UpdateQuery withe stored procedure and parameters?

Discussion in 'ASP .Net' started by Scott.Metzger, Oct 5, 2009.

  1. Hi,

    I want to call a stored procedure with parameters from
    sqlDataSource.UpdateQuery. How do I do this?

    I tried setting UpdateCOmmandType=StoredProcedure and '
    UpdateQuery = "sp_UpdateVetMedSupplies @eor_supplies_id,
    @total_purchase,
    @Animals_Descr,
    @Animal_Qty,
    @Unit_Price,
    @date_needed"
    But I get run time error saying the stored proc cannot be found.

    I also tried UpdateCommandType=String with
    UpdateQuery = "exec sp_UpdateVetMedSupplies @eor_supplies_id,
    @total_purchase,
    @Animals_Descr,
    @Animal_Qty,
    @Unit_Price,
    @date_needed"
    But then I get an error saying that nvarchar cannot be found.

    What is the proper way to call a Stored Proc, passing parameters, in the
    UpdateCommand?

    Thanks,
    Scott
    Scott.Metzger, Oct 5, 2009
    #1
    1. Advertising

  2. On Oct 5, 9:46 pm, Scott.Metzger
    <> wrote:
    > Hi,
    >
    > I want to call a stored procedure with parameters from
    > sqlDataSource.UpdateQuery.  How do I do this?
    >
    > I tried setting UpdateCOmmandType=StoredProcedure and '
    > UpdateQuery = "sp_UpdateVetMedSupplies @eor_supplies_id,
    >         @total_purchase,
    >         @Animals_Descr,
    >         @Animal_Qty,
    >         @Unit_Price,
    >         @date_needed"
    > But I get  run time error saying the stored proc cannot be found.
    >
    > I also tried UpdateCommandType=String with
    > UpdateQuery = "exec sp_UpdateVetMedSupplies @eor_supplies_id,
    >         @total_purchase,
    >         @Animals_Descr,
    >         @Animal_Qty,
    >         @Unit_Price,
    >         @date_needed"
    > But then I get an error saying that nvarchar cannot be found.
    >
    > What is the proper way to call a Stored Proc, passing parameters, in the
    > UpdateCommand?
    >
    > Thanks,
    > Scott


    Hi Scott,

    try

    <asp:SqlDataSource ...

    UpdateCommand="sp_UpdateVetMedSupplies"
    UpdateCommandType="StoredProcedure">

    <UpdateParameters>
    <asp:parameter Type="Int32" Name="eor_supplies_id"></
    asp:parameter>
    <asp:parameter Type="String" Name="total_purchase"></
    asp:parameter>
    ....
    </UpdateParameters>


    Reference:

    http://msdn.microsoft.com/en-us/lib...controls.sqldatasourceview.updatecommand.aspx
    http://msdn.microsoft.com/en-us/lib...bcontrols.sqldatasource.updateparameters.aspx
    Alexey Smirnov, Oct 5, 2009
    #2
    1. Advertising

  3. Re: sqlDataSource UpdateQuery withe stored procedure and parameter

    Thanks, thats closer. Neither one of those links deals with a stored
    procedure.
    Now I am getting "Procedure or function sp_UpdateVetMedSupplies has too many
    arguments specified."

    UpdateCommand="sp_UpdateVetMedSupplies" UpdateCommandType="StoredProcedure">
    <UpdateParameters>
    <asp:parameter DefaultValue="17638" Name="eor_supplies_id" />
    <asp:parameter DefaultValue="200" Name="total_purchase" />
    <asp:parameter DefaultValue="African Green NHP" Name="Animals_Descr"
    />
    <asp:parameter DefaultValue="2" Name="Animal_Qty" />
    <asp:parameter DefaultValue="300" Name="Unit_Price" />
    <asp:parameter DefaultValue="January" Name="date_needed" />
    </UpdateParameters>

    Stored Procedure:
    Create PROCEDURE [dbo].[sp_UpdateVetMedSupplies]
    @eor_supplies_id int,
    @total_purchase int,
    @Animals_Descr varchar(255),
    @Animal_Qty int,
    @Unit_Price int,
    @date_needed varchar(50)
    AS
    update eor_supplies
    set [Total Anticipated]=@total_purchase,
    [Total Cost]=@total_purchase,
    [Animals Descr]=@Animals_Descr,
    [Animal Qty]=@Animal_Qty,
    [Unit Price]=@Unit_Price,
    Date_Needed=@date_needed
    where eor_supplies_id=@eor_supplies_id

    "Alexey Smirnov" wrote:

    > On Oct 5, 9:46 pm, Scott.Metzger
    > <> wrote:
    > > Hi,
    > >
    > > I want to call a stored procedure with parameters from
    > > sqlDataSource.UpdateQuery. How do I do this?
    > >
    > > I tried setting UpdateCOmmandType=StoredProcedure and '
    > > UpdateQuery = "sp_UpdateVetMedSupplies @eor_supplies_id,
    > > @total_purchase,
    > > @Animals_Descr,
    > > @Animal_Qty,
    > > @Unit_Price,
    > > @date_needed"
    > > But I get run time error saying the stored proc cannot be found.
    > >
    > > I also tried UpdateCommandType=String with
    > > UpdateQuery = "exec sp_UpdateVetMedSupplies @eor_supplies_id,
    > > @total_purchase,
    > > @Animals_Descr,
    > > @Animal_Qty,
    > > @Unit_Price,
    > > @date_needed"
    > > But then I get an error saying that nvarchar cannot be found.
    > >
    > > What is the proper way to call a Stored Proc, passing parameters, in the
    > > UpdateCommand?
    > >
    > > Thanks,
    > > Scott

    >
    > Hi Scott,
    >
    > try
    >
    > <asp:SqlDataSource ...
    >
    > UpdateCommand="sp_UpdateVetMedSupplies"
    > UpdateCommandType="StoredProcedure">
    >
    > <UpdateParameters>
    > <asp:parameter Type="Int32" Name="eor_supplies_id"></
    > asp:parameter>
    > <asp:parameter Type="String" Name="total_purchase"></
    > asp:parameter>
    > ....
    > </UpdateParameters>
    >
    >
    > Reference:
    >
    > http://msdn.microsoft.com/en-us/lib...controls.sqldatasourceview.updatecommand.aspx
    > http://msdn.microsoft.com/en-us/lib...bcontrols.sqldatasource.updateparameters.aspx
    >
    >
    Scott.Metzger, Oct 5, 2009
    #3
  4. Re: sqlDataSource UpdateQuery withe stored procedure and parameter

    On Oct 5, 11:18 pm, Scott.Metzger
    <> wrote:
    > Thanks, thats closer.  Neither one of those links deals with a stored
    > procedure.
    > Now I am getting "Procedure or function sp_UpdateVetMedSupplies has too many
    > arguments specified."
    >
    > UpdateCommand="sp_UpdateVetMedSupplies" UpdateCommandType="StoredProcedure">
    > <UpdateParameters>
    >         <asp:parameter DefaultValue="17638" Name="eor_supplies_id" />
    >         <asp:parameter DefaultValue="200" Name="total_purchase" />
    >         <asp:parameter DefaultValue="African Green NHP" Name="Animals_Descr"
    > />
    >         <asp:parameter DefaultValue="2" Name="Animal_Qty" />
    >         <asp:parameter DefaultValue="300" Name="Unit_Price" />
    >         <asp:parameter DefaultValue="January" Name="date_needed" />
    >     </UpdateParameters>
    >
    > Stored Procedure:
    > Create PROCEDURE [dbo].[sp_UpdateVetMedSupplies]
    >         @eor_supplies_id int,
    >         @total_purchase int,
    >         @Animals_Descr varchar(255),
    >         @Animal_Qty int,
    >         @Unit_Price int,
    >         @date_needed varchar(50)        
    > AS
    > update eor_supplies
    > set [Total Anticipated]=@total_purchase,
    >         [Total Cost]=@total_purchase,
    >         [Animals Descr]=@Animals_Descr,
    >         [Animal Qty]=@Animal_Qty,
    >         [Unit Price]=@Unit_Price,
    >         Date_Needed=@date_needed
    > where eor_supplies_id=@eor_supplies_id
    >
    >
    >
    > "Alexey Smirnov" wrote:
    > > On Oct 5, 9:46 pm, Scott.Metzger
    > > <> wrote:
    > > > Hi,

    >
    > > > I want to call a stored procedure with parameters from
    > > > sqlDataSource.UpdateQuery.  How do I do this?

    >
    > > > I tried setting UpdateCOmmandType=StoredProcedure and '
    > > > UpdateQuery = "sp_UpdateVetMedSupplies @eor_supplies_id,
    > > >         @total_purchase,
    > > >         @Animals_Descr,
    > > >         @Animal_Qty,
    > > >         @Unit_Price,
    > > >         @date_needed"
    > > > But I get  run time error saying the stored proc cannot be found.

    >
    > > > I also tried UpdateCommandType=String with
    > > > UpdateQuery = "exec sp_UpdateVetMedSupplies @eor_supplies_id,
    > > >         @total_purchase,
    > > >         @Animals_Descr,
    > > >         @Animal_Qty,
    > > >         @Unit_Price,
    > > >         @date_needed"
    > > > But then I get an error saying that nvarchar cannot be found.

    >
    > > > What is the proper way to call a Stored Proc, passing parameters, in the
    > > > UpdateCommand?

    >
    > > > Thanks,
    > > > Scott

    >
    > > Hi Scott,

    >
    > > try

    >
    > > <asp:SqlDataSource ...

    >
    > > UpdateCommand="sp_UpdateVetMedSupplies"
    > > UpdateCommandType="StoredProcedure">

    >
    > > <UpdateParameters>
    > >                  <asp:parameter Type="Int32" Name="eor_supplies_id"></
    > > asp:parameter>
    > >                  <asp:parameter Type="String" Name="total_purchase"></
    > > asp:parameter>
    > > ....
    > > </UpdateParameters>

    >
    > > Reference:

    >
    > >http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sql...
    > >http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sql...- Hide quoted text -

    >
    > - Show quoted text -


    Do you have a gridview or something like this? Each datakeyname
    specified in the gridview control is automatically sent to the stored
    procedure.

    http://www.whitworth.org/2006/01/16...as-too-many-arguments-specified-in-aspnet-20/
    Alexey Smirnov, Oct 5, 2009
    #4
  5. Re: sqlDataSource UpdateQuery withe stored procedure and parameter

    On Oct 6, 9:09 am, Scott.Metzger
    <> wrote:
    > Ok, so that is really messed up.
    >
    > Why did MS make it so difficult?  If your using a regular Update SQL
    > statement it doesn't add fields to your update statement.
    > IMO it should only pass params you specify.
    >
    > Its adding ALL my fields from my select statement, most of them I don't want.


    This is why I never use it. It's more easy and clear to work with the
    database from the code behind
    Alexey Smirnov, Oct 6, 2009
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Andy in South Jersey
    Replies:
    3
    Views:
    2,484
    Andy in South Jersey
    Feb 11, 2009
  2. Andy in South Jersey
    Replies:
    0
    Views:
    287
    Andy in South Jersey
    Feb 11, 2009
  3. Andy in South Jersey
    Replies:
    0
    Views:
    331
    Andy in South Jersey
    Feb 11, 2009
  4. M1iS
    Replies:
    0
    Views:
    143
  5. M1iS
    Replies:
    2
    Views:
    143
    Mike Brind
    Feb 5, 2007
Loading...

Share This Page