sqlDataSource UpdateQuery withe stored procedure and parameters?

  • Thread starter Thread starter Scott.Metzger
  • Start date Start date
S

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
 
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
Click to expand...

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
 
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
 
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



Hi Scott,

<asp:SqlDataSource ...

<UpdateParameters>
                 <asp:Parameter Type="Int32" Name="eor_supplies_id"></
asp:Parameter>
                 <asp:Parameter Type="String" Name="total_purchase"></
asp:Parameter>
....
</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 -
Click to expand...

- Show quoted text -
Click to expand...

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/
 
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.
Click to expand...

This is why I never use it. It's more easy and clear to work with the
database from the code behind
 
Back
Top