Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > sqlDataSource UpdateQuery withe stored procedure and parameters?

Reply
Thread Tools

sqlDataSource UpdateQuery withe stored procedure and parameters?

 
 
Scott.Metzger
Guest
Posts: n/a
 
      10-05-2009
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
 
Reply With Quote
 
 
 
 
Alexey Smirnov
Guest
Posts: n/a
 
      10-05-2009
On Oct 5, 9:46*pm, Scott.Metzger
<(E-Mail Removed)> 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>
<asparameter Type="Int32" Name="eor_supplies_id"></
asparameter>
<asparameter Type="String" Name="total_purchase"></
asparameter>
....
</UpdateParameters>


Reference:

http://msdn.microsoft.com/en-us/libr...tecommand.aspx
http://msdn.microsoft.com/en-us/libr...arameters.aspx

 
Reply With Quote
 
 
 
 
Scott.Metzger
Guest
Posts: n/a
 
      10-05-2009

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

"Alexey Smirnov" wrote:

> On Oct 5, 9:46 pm, Scott.Metzger
> <(E-Mail Removed)> 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>
> <asparameter Type="Int32" Name="eor_supplies_id"></
> asparameter>
> <asparameter Type="String" Name="total_purchase"></
> asparameter>
> ....
> </UpdateParameters>
>
>
> Reference:
>
> http://msdn.microsoft.com/en-us/libr...tecommand.aspx
> http://msdn.microsoft.com/en-us/libr...arameters.aspx
>
>

 
Reply With Quote
 
Alexey Smirnov
Guest
Posts: n/a
 
      10-05-2009
On Oct 5, 11:18*pm, Scott.Metzger
<(E-Mail Removed)> 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>
> * * * * <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
>
>
>
> "Alexey Smirnov" wrote:
> > On Oct 5, 9:46 pm, Scott.Metzger
> > <(E-Mail Removed)> 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>
> > * * * * * * * * *<asparameter Type="Int32" Name="eor_supplies_id"></
> > asparameter>
> > * * * * * * * * *<asparameter Type="String" Name="total_purchase"></
> > asparameter>
> > ....
> > </UpdateParameters>

>
> > Reference:

>
> >http://msdn.microsoft.com/en-us/libr...ebcontrols.sql...
> >http://msdn.microsoft.com/en-us/libr...ntrols.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/...-in-aspnet-20/
 
Reply With Quote
 
Alexey Smirnov
Guest
Posts: n/a
 
      10-06-2009
On Oct 6, 9:09*am, Scott.Metzger
<(E-Mail Removed)> 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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Gridview Template Columns & SqlDataSource UpdateQuery M1iS ASP General 2 02-05-2007 07:57 AM
Gridview Template Columns & SqlDataSource UpdateQuery M1iS ASP .Net Web Controls 0 02-03-2007 05:42 PM
printers that are compatable withe x64 =?Utf-8?B?cmVubW8=?= Windows 64bit 5 07-01-2006 09:45 PM
SqlDataSource - Stored Procedure =?Utf-8?B?YmVub2l0?= ASP .Net 1 12-20-2005 03:28 PM
SQLDataSource and Stored Procedure Question David Hearn ASP .Net 1 10-13-2005 08:21 PM



Advertisments