sqlDataSource UpdateQuery withe stored procedure and parameters?

[Scott.Metzger]

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

 

[Guest]

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>
<asparameter Type="Int32" Name="eor_supplies_id"></
asparameter>
<asparameter Type="String" Name="total_purchase"></
asparameter>
....
</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]

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>
<asparameter DefaultValue="17638" Name="eor_supplies_id" />
<asparameter DefaultValue="200" Name="total_purchase" />
<asparameter DefaultValue="African Green NHP" Name="Animals_Descr"
/>
<asparameter DefaultValue="2" Name="Animal_Qty" />
<asparameter DefaultValue="300" Name="Unit_Price" />
<asparameter 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

 

[Guest]

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>
        <asparameter DefaultValue="17638" Name="eor_supplies_id" />
        <asparameter DefaultValue="200" Name="total_purchase" />
        <asparameter DefaultValue="African Green NHP" Name="Animals_Descr"
/>
        <asparameter DefaultValue="2" Name="Animal_Qty" />
        <asparameter DefaultValue="300" Name="Unit_Price" />
        <asparameter 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

Hi Scott,

<asp:SqlDataSource ...

<UpdateParameters>
                 <asparameter Type="Int32" Name="eor_supplies_id"></
asparameter>
                 <asparameter Type="String" Name="total_purchase"></
asparameter>
....
</UpdateParameters>

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/

 

[Guest]

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