Send Parameters to Stored Procedure

Discussion in 'ASP .Net' started by Chris, Mar 22, 2006.

  1. Chris

    Chris Guest

    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.
     
    Chris, Mar 22, 2006
    #1
    1. Advertising

  2. Chris

    q Guest

    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/
     
    q, Mar 22, 2006
    #2
    1. Advertising

  3. Try sds_reader.SelectParameters.Add(parameter_name, parameter_value)

    Eliyahu

    "Chris" <> wrote in message
    news:...
    > 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.
    >
     
    Eliyahu Goldin, Mar 22, 2006
    #3
  4. Chris

    Chris Guest

    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, Mar 23, 2006
    #4
  5. Chris

    Chris Guest

    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)
     
    Chris, Mar 23, 2006
    #5
  6. Chris

    q Guest

    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.
     
    q, Mar 23, 2006
    #6
  7. Chris

    Chris Guest

    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.
     
    Chris, Mar 24, 2006
    #7
  8. Chris

    q Guest

    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.
     
    q, Mar 24, 2006
    #8
  9. Chris

    Chris Guest

    Thanks a lot.
     
    Chris, Mar 27, 2006
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. kavitha N via .NET 247

    passing parameters to stored procedure from crystal reports

    kavitha N via .NET 247, Jun 8, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    10,918
    galahad
    Feb 15, 2011
  2. TaeHo Yoo
    Replies:
    0
    Views:
    417
    TaeHo Yoo
    Aug 14, 2003
  3. Patrick

    send email through stored procedure

    Patrick, Jan 24, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    4,168
    Steve C. Orr [MVP, MCSD]
    Jan 24, 2005
  4. Mike P
    Replies:
    0
    Views:
    3,381
    Mike P
    Jun 19, 2006
  5. Shahid Juma

    Send Dictionary Object to Stored procedure?

    Shahid Juma, Oct 12, 2004, in forum: ASP General
    Replies:
    5
    Views:
    771
    Bob Barrows [MVP]
    Oct 12, 2004
Loading...

Share This Page