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. =?Utf-8?B?SnVzdGlu?=

    Mark Rae Guest

    "Justin" <> wrote in message
    news:...

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


    Which is not surprising since your stored procedure does not have any
    parameters, and your code is trying to pass one to it. As it stands, your
    procedure will return all events from the Reservations and Events tables
    which satisfy the inner join, but it looks like your code is trying to fetch
    details about one particular event, hence the line
    "this.sqlSelectCommand1.Parameters.Add(new ....."

    If that is the case, you need to declare an EventID parameter at the top of
    your procedure:

    ALTER PROCEDURE dbo.Details
    @EventID int
    AS
    SET NOCOUNT ON
    etc
    etc

    and add a WHERE clause at the bottom:

    WHERE Reservations.EventID = @EventID

    > 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.
    Mark Rae, Oct 4, 2004
    #2
    1. Advertising

  3. Thanks, I got it working!

    Can you recommend any articles or books for learning more about stored
    procedures?

    Thanks, Justin.

    "Mark Rae" wrote:

    > "Justin" <> wrote in message
    > news:...
    >
    > > "Procedure Details has no parameters and arguments were supplied."

    >
    > Which is not surprising since your stored procedure does not have any
    > parameters, and your code is trying to pass one to it. As it stands, your
    > procedure will return all events from the Reservations and Events tables
    > which satisfy the inner join, but it looks like your code is trying to fetch
    > details about one particular event, hence the line
    > "this.sqlSelectCommand1.Parameters.Add(new ....."
    >
    > If that is the case, you need to declare an EventID parameter at the top of
    > your procedure:
    >
    > ALTER PROCEDURE dbo.Details
    > @EventID int
    > AS
    > SET NOCOUNT ON
    > etc
    > etc
    >
    > and add a WHERE clause at the bottom:
    >
    > WHERE Reservations.EventID = @EventID
    >
    > > 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
    #3
  4. =?Utf-8?B?SnVzdGlu?=

    Mark Rae Guest

    "Justin" <> wrote in message
    news:...

    > Can you recommend any articles or books for learning more about stored
    > procedures?


    I'd start with SQL Server's Books Online...
    Mark Rae, Oct 4, 2004
    #4
  5. =?Utf-8?B?SnVzdGlu?=

    Larry

    Joined:
    Dec 12, 2008
    Messages:
    1
    Another?

    Mark,

    I'm having a similar issue as the previous gentleman. I am attempting to execute some VB code to pull a stored procedure and I'm receiving the same error.

    Here is the Procedure:
    -----------------below-----------------
    USE [MMS_Data]
    GO
    /****** Object: StoredProcedure [dbo].[InvItems_GetAllForSale_NewMachine] Script Date: 12/12/2008 12:15:32 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER procedure [dbo].[InvItems_GetAllForSale_NewMachine] as
    begin
    Select
    description,
    sellprice
    FROM dbo.MMSInvItems
    where
    ShowOnIDO = 1
    and TrackedInEIS = 1
    order by description
    end
    -----------------above-----------------

    Here is the VB Code
    -----------------below-----------------
    ' Retrieve the name of the Office application to add to the database table
    Dim dom As XPathNavigator = MainDataSource.CreateNavigator()
    Dim officeApplicationName As String = dom.SelectSingleNode( _
    "//my:OfficeApplicationName", NamespaceManager).Value

    ' Retrieve the connection to the database table
    Dim connection As AdoQueryConnection = DataConnections("CITC_MMS_IDO")

    ' Save the original statement that is stored in the Command property
    Dim originalCommand As String = connection.Command

    ' Execute the stored procedure
    connection.Command = "EXEC InvItems_GetAllForSale_NewMachine '" & officeApplicationName & "'"
    connection.Execute()

    ' Restore the statement of the Command property to its original value
    connection.Command = originalCommand
    -----------------above-----------------

    The Stored Procedure functions when executed from SQL, but not from my web form, so therefore, am I correct in assuming my VB code is hosed?

    Thank you :) I'm in a developers chair but haven't developed in more than 20 years. EVERYTHING has changed!

    Larry
    Larry, Dec 12, 2008
    #5
    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:
    421
    Bilbo
    Nov 20, 2003
  4. The Clansman

    Stored Procedure problem

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

Share This Page