Problem with stored procedure

Discussion in 'ASP .Net' started by =?Utf-8?B?SnVzdGlu?=, Oct 4, 2004.

  1. I am trying to query two tables with a stored procedure but I get the
    following error:

    "Procedure Details has no parameters and arguments were supplied."

    Here is my stored procedure as created by Visual Studio:

    ALTER PROCEDURE dbo.Details
    AS
    SET NOCOUNT ON;
    SELECT Events.KeyID, Events.StartDate, Events.StartTime, Events.EventName,
    Events.StartLocation, Events.EndLocation, Events.AvailableSeats,
    Events.Description, Events.RateAdult, Events.RateChild, Events.RateSenior,
    Reservations.KeyID AS Expr1, Reservations.EventID, Reservations.AccountID,
    Reservations.qntyChild, Reservations.qntyAdult, Reservations.qntySenior,
    Reservations.GroupID, Reservations.Status FROM Events INNER JOIN Reservations
    ON Events.KeyID = Reservations.EventID

    Here is the code being used to query the Stored Procedure in the page_load
    event:

    int EventID = Convert.ToInt32(Request.QueryString["ID"]);

    this.sqlSelectCommand1.CommandText = "Details";
    this.sqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure;
    this.sqlSelectCommand1.Connection = this.sqlConnection2;
    this.sqlSelectCommand1.Parameters.Add(new
    System.Data.SqlClient.SqlParameter("@EventID", System.Data.SqlDbType.Int, 4,
    System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)),
    ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));

    this.sqlSelectCommand1.Parameters.Add("@EventID",
    System.Data.SqlDbType.NVarChar).Value = EventID;

    sqlConnection2.Open();
    myReader = sqlSelectCommand1.ExecuteReader();

    if (myReader.Read())
    {
    txtEventName.Text = myReader["EventName"].ToString();
    }
    sqlConnection2.Close();

    Any ideas on why this isn't working?

    This is my first time using a stored procedure in a project so any help
    would be great.

    Thanks, Justin.
    =?Utf-8?B?SnVzdGlu?=, Oct 4, 2004
    #1
    1. Advertising

  2. You're stored procedure doesn't have a parameter and in your code you specify
    a parameter. The stored procedure must begin like:

    ALTER PROCEDURE dbo.Details
    @EventID int
    as begin
    .......

    and in the where clause:
    where Reservations.EventID = @EventID

    The code looks something like:
    SqlParameter sqlPar = new SqlParameter("@EventID", SqlDbType.Int);
    sqlPar.Value = EventID;
    this.SelectCommand1.Parameters.Add(sqlPar);


    "Justin" wrote:

    > I am trying to query two tables with a stored procedure but I get the
    > following error:
    >
    > "Procedure Details has no parameters and arguments were supplied."
    >
    > Here is my stored procedure as created by Visual Studio:
    >
    > ALTER PROCEDURE dbo.Details
    > AS
    > SET NOCOUNT ON;
    > SELECT Events.KeyID, Events.StartDate, Events.StartTime, Events.EventName,
    > Events.StartLocation, Events.EndLocation, Events.AvailableSeats,
    > Events.Description, Events.RateAdult, Events.RateChild, Events.RateSenior,
    > Reservations.KeyID AS Expr1, Reservations.EventID, Reservations.AccountID,
    > Reservations.qntyChild, Reservations.qntyAdult, Reservations.qntySenior,
    > Reservations.GroupID, Reservations.Status FROM Events INNER JOIN Reservations
    > ON Events.KeyID = Reservations.EventID
    >
    > Here is the code being used to query the Stored Procedure in the page_load
    > event:
    >
    > int EventID = Convert.ToInt32(Request.QueryString["ID"]);
    >
    > this.sqlSelectCommand1.CommandText = "Details";
    > this.sqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure;
    > this.sqlSelectCommand1.Connection = this.sqlConnection2;
    > this.sqlSelectCommand1.Parameters.Add(new
    > System.Data.SqlClient.SqlParameter("@EventID", System.Data.SqlDbType.Int, 4,
    > System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)),
    > ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
    >
    > this.sqlSelectCommand1.Parameters.Add("@EventID",
    > System.Data.SqlDbType.NVarChar).Value = EventID;
    >
    > sqlConnection2.Open();
    > myReader = sqlSelectCommand1.ExecuteReader();
    >
    > if (myReader.Read())
    > {
    > txtEventName.Text = myReader["EventName"].ToString();
    > }
    > sqlConnection2.Close();
    >
    > Any ideas on why this isn't working?
    >
    > This is my first time using a stored procedure in a project so any help
    > would be great.
    >
    > Thanks, Justin.
    =?Utf-8?B?RWR3YXJk?=, Oct 4, 2004
    #2
    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. Leon Shaw

    Stored Procedure Problem

    Leon Shaw, Jul 28, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    371
    Jurjen de Groot
    Jul 29, 2003
  2. ElmoWatson

    Stored Procedure/Parameter problem

    ElmoWatson, Aug 4, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    490
    Jerry
    Aug 6, 2003
  3. Bilbo
    Replies:
    3
    Views:
    422
    Bilbo
    Nov 20, 2003
  4. The Clansman

    Stored Procedure problem

    The Clansman, May 19, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    347
    Julie
    May 19, 2004
  5. Mike P
    Replies:
    0
    Views:
    3,298
    Mike P
    Jun 19, 2006
Loading...

Share This Page