Passing parameters to stored procedures

Hi,

If the existing ado code is using a stored procedure
and passing parameters, in ado.net is creating
parameters and adding to the parameter collection
the only way ??
Can I do the following in ado.net ?

strSQL = "sp_xxx" & dr("loc_cd") & "," & q(as_of_date) & "
sc.CommandType = CommandType.StoredProcedure

sc.CommandText = strSQL

Thanks in advance,

Re: Passing parameters to stored procedures

No. You cannot.
You have to instaniate each parameter first, give the value and then add into the instance.

Re: Passing parameters to stored procedures

An example is approriate here:

string cSelect = "dbo.Test";

SqlDataAdapter DataAdapter = new SqlDataAdapter( cSelect, conConstant );

DataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

DataAdapter.SelectCommand.Parameters.AddWithValue( "@iClientID", (Guid)Session[ "gClientID" ] );

Hope this helps,

Nick H

Re: Passing parameters to stored procedures

kmr,

Adding Parameters to the parameters collection is more secure and automatically figures out the type of the parameter (Varchar, Int, etc).

Thanks,

Re: Passing parameters to stored procedures

I created a simple stored procedure to add a record to my [movies] table:

CREATE PROCEDURE dbo.AddMovie
(@isdn nvarchar(10), @title varchar(80), @rating varchar(50),
@widescreen varchar(1), @genre varchar(80), @plot varchar(250),
@director varchar(50), @producer varchar(50))
AS
INSERT INTO dbo.movies

VALUES
(@isdn, @title, @rating, @widescreen, @genre,
@plot, @director, @producer)

My question is: "How do I pass the parematers to the stored procedure when my ASP page says:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:chucksaConnectionString %>"
SelectCommand="SELECT * FROM [movies]" ProviderName="<%$ ConnectionStrings:chucksaConnectionString.ProviderName %>"
DeleteCommand="DELETE FROM movies"
InsertCommand="addMovie" InsertCommandType="StoredProcedure"
UpdateCommand="UPDATE movies SET">
</asp:SqlDataSource>

I am using a DetailsView control - Is this an issue of data binding?

Regards,
Chuck