Send Parameters to Stored Procedure

[Chris]

Hi,

How can I send the parameter value for a stored procedure in ASP.NET
2.0 ?

Dim sds_reader As New SqlDataSource

sds_reader.SelectCommand = "STP_select_by_Type"

the line below produces an error:

sds_reader.SelectParameters.Item(@Period_Type_no).DefaultValue =
1

Thanks a lot.

 

[q]

You'll have to convert it to VB... but here it is.

string connectionString =
System.Configuration.ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("DeleteOrderLine",
connection)) {
connection.Open( );
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@LineNumber", lineNumber);
command.ExecuteNonQuery( );
}
}



David Betz
WinFX Harmonics Blog
http://www.davidbetz.net/winfx/

 

[Eliyahu Goldin]

Try sds_reader.SelectParameters.Add(parameter_name, parameter_value)

Eliyahu

 

[Chris]

I tried:

sds_reader.SelectCommand = "STP_select_by_Type"
sds_reader.SelectCommandType =
SqlDataSourceCommandType.StoredProcedure

sds_reader.SelectParameters.Add("@Period_Type_no", 1)

and gives me an error on DATABIND saying:

Procedure or Function 'STP_select_by_Type' expects parameter
'@Period_Type_no', which was not supplied.

 

[Chris]

Thanks a lot.

I tried the following"

Dim sds_reader1 As New SqlDataSource

sds_reader1.DataSourceMode = SqlDataSourceMode.DataReader
sds_reader1.ConnectionString =
ConfigurationManager.ConnectionStrings("SubDispConnectionString1").ConnectionString

sds_reader1.SelectCommand = "STP_select_by_Type"
sds_reader1.SelectCommandType =
SqlDataSourceCommandType.StoredProcedure

sds_reader1.SelectParameters.Add("@Period_Type_no", 1)

dropdownlist.DataSource = sds_reader1
dropdownlist.DataBind()

on DATABIND I get the error :

Procedure or Function 'STP_select_by_Type' expects parameter
'@Period_Type_no', which was not supplied.

I changed it to the following, but error is still the same:

Dim a As New Parameter

a.Name = "@Period_Type_no"
a.DefaultValue = 1
sds_reader1.SelectParameters.Add(a)

 

[q]

You may actually want to use the method I posted (using a
SqlDataAdapter of course)... the SqlDataSource is more for declarative
databinding in the ASP.NET declarative page.

 

[Chris]

I was able to use the code you posted after using imports
system.data.sqlclient.

Why in 2005 the SQLCOMMAND, SQLDATAADAPTER etc are not included in
toolbox as in 2003. Only SQLDATA SOURCE exists in toolbox (that's why I
was trying with that one).

Does it mean that these objects should not be used ? Sorry for my
questions, I just started using .NET and I got confused.

 

[q]

The controls are just there for beginners who don't really know what's
available. When you get into the CODE, you use the code. You
shouldn't really have to mix the designer with code. After a while,
the designer will be a hinderance to your productivity as SqlCommand
and SqlDataAdapter will flow from your fingers like writing an e-mail.

 

[Chris]

Thanks a lot.