Problem with stored procedure

G

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

Mark Rae

"Procedure Details has no parameters and arguments were supplied."
Click to expand...

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.
Click to expand...
 
G

Guest

Thanks, I got it working!

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

Thanks, Justin.

Mark Rae said:
"Procedure Details has no parameters and arguments were supplied."
Click to expand...

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.
Click to expand...
Click to expand...
 
Top