Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Problem with stored procedure

Reply
Thread Tools

Problem with stored procedure

 
 
=?Utf-8?B?SnVzdGlu?=
Guest
Posts: n/a
 
      10-04-2004
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.
 
Reply With Quote
 
 
 
 
Mark Rae
Guest
Posts: n/a
 
      10-04-2004
"Justin" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...

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



 
Reply With Quote
 
 
 
 
=?Utf-8?B?SnVzdGlu?=
Guest
Posts: n/a
 
      10-04-2004
Thanks, I got it working!

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

Thanks, Justin.

"Mark Rae" wrote:

> "Justin" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>
> > "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.

>
>
>

 
Reply With Quote
 
Mark Rae
Guest
Posts: n/a
 
      10-04-2004
"Justin" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...

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


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


 
Reply With Quote
 
Larry Larry is offline
Junior Member
Join Date: Dec 2008
Posts: 1
 
      12-12-2008
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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
Stored Procedure problem The Clansman ASP .Net 1 05-18-2004 11:32 PM
Interesting Stored Procedure Problem.. Bilbo ASP .Net 3 11-20-2003 09:31 PM
Stored Procedure/Parameter problem ElmoWatson ASP .Net 1 08-06-2003 01:19 AM
Stored Procedure Problem Leon Shaw ASP .Net 1 07-29-2003 09:19 AM



Advertisments