Problem with stored procedure

[Guest]

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.

 

[Mark Rae]

"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.

 

[Guest]

Thanks, I got it working!

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

Thanks, Justin.

"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]

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

I'd start with SQL Server's Books Online...

 

[Larry]

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