Stored Procedure Error


Author
Message
Terry Bottorff
Terry Bottorff
StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)
Group: Forum Members
Posts: 448, Visits: 12K
I have the following Stored Proc:

CREATE PROCEDURE [dbo].[sp_RodeoEvtConfig]

-- Add the parameters for the stored procedure here

@rodeoid int = 1,

@eventcd char(2)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT rodeoevtconfig_pk, rodeoId, eventCD, eventName, goCount, committeePurse, sponsorPurse, additionalCompensation, eventStatus, finalsFlag, finalsPayoutFactor,

finalsAmount, CommitteeEventMoney, dayMoneyPayoutPositions, backToBackFlag, dayMoneyDeduction, deductionPercent, orderOfFill, decimalPrecision,

stockBackNumberUsed, leftRightJudgesSets

FROM RodeoEventConfiguration

WHERE (rodeoId = @rodeoid) AND (eventCD = @eventcd)

END

I get the following Error (See Attachment)

Attachments
NoParameter.png (292 views, 17.00 KB)
Reply
Les Pinter
Les Pinter
StrataFrame Team Member (95 reputation)StrataFrame Team Member (95 reputation)StrataFrame Team Member (95 reputation)StrataFrame Team Member (95 reputation)StrataFrame Team Member (95 reputation)StrataFrame Team Member (95 reputation)StrataFrame Team Member (95 reputation)StrataFrame Team Member (95 reputation)StrataFrame Team Member (95 reputation)
Group: Forum Members
Posts: 43, Visits: 213
Hi Terry,

I created a table in my Pinter database using this:

CREATE TABLE [dbo].[RodeoEventConfiguration](
 [rodeoevtconfig_pk] [int] IDENTITY(1,1) NOT NULL,
 [rodeoId] [int] NOT NULL DEFAULT ((1)),
 [eventCD] [char](2) NOT NULL DEFAULT ('  '),
 [eventName] [varchar](10) NOT NULL DEFAULT (''),
 [goCount] [int] NOT NULL DEFAULT ((1)),
 [committeePurse] [int] NOT NULL DEFAULT ((1000)),
 [sponsorPurse] [int] NOT NULL DEFAULT ((1000)),
 [additionalCompensation] [int] NOT NULL DEFAULT ((1000)),
 [eventStatus] [bit] NOT NULL DEFAULT ((1)),
 [finalsFlag] [bit] NOT NULL DEFAULT ((1)),
 [finalsPayoutFactor] [int] NOT NULL DEFAULT ((1)),
 [finalsAmount] [int] NOT NULL DEFAULT ((1)),
 [CommitteeEventMoney] [int] NOT NULL DEFAULT ((1)),
 [dayMoneyPayoutPositions] [int] NOT NULL DEFAULT ((1)),
 [backToBackFlag] [bit] NOT NULL DEFAULT ((1)),
 [dayMoneyDeduction] [int] NOT NULL DEFAULT ((1)),
 [deductionPercent] [int] NOT NULL DEFAULT ((10)),
 [orderOfFill] [int] NOT NULL DEFAULT ((1)),
 [decimalPrecision] [int] NOT NULL DEFAULT ((2)),
 [stockBackNumberUsed] [bit] NOT NULL DEFAULT ((1)),
 [leftRightJudgesSets] [bit] NOT NULL DEFAULT ((1))
) ON [PRIMARY]
GO

I inserted three records:

INSERT INTO RodeoEventConfiguration ( rodeoID, eventCD ) VALUES ( 1, 'AA' )
INSERT INTO RodeoEventConfiguration ( rodeoID, eventCD ) VALUES ( 1, 'BB' )
INSERT INTO RodeoEventConfiguration ( rodeoID, eventCD ) VALUES ( 1, 'CC' )

I then added this stored procedure:

CREATE PROCEDURE [dbo].[sp_RodeoEvtConfig]
@rodeoid int = 1,
@eventcd char(2)
AS
BEGIN
SET NOCOUNT ON;
SELECT
   rodeoevtconfig_pk, rodeoId, eventCD, eventName, goCount, committeePurse, sponsorPurse, additionalCompensation, eventStatus, finalsFlag, finalsPayoutFactor,
   finalsAmount, CommitteeEventMoney, dayMoneyPayoutPositions, backToBackFlag, dayMoneyDeduction, deductionPercent, orderOfFill, decimalPrecision,
   stockBackNumberUsed, leftRightJudgesSets
  FROM RodeoEventConfiguration
 WHERE (rodeoId = @rodeoid) AND (eventCD = @eventcd)
END

My business object, based on the RodeoEventConfiguration table, had this method:

      public void FillRodeoConfig(int nrod, string cevt)
      { SqlCommand cmd = new SqlCommand("dbo.sp_RodeoEvtConfig");
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@rodeoid",SqlDbType.Int).Value = 1;
        cmd.Parameters.Add("@eventcd",SqlDbType.Char).Value = cevt;
        FillDataTable(cmd); }

In my new SF project, Form1 contains two textboxes bound respectively to rodeoID and eventCD in the RodeoEventConfiguration table, and three buttons. Here's their Click event code:

    private void button1_Click(object sender, EventArgs e)
    { rodeoBO1.FillRodeoConfig(1, "AA"); }

    private void button2_Click(object sender, EventArgs e)
    { rodeoBO1.FillRodeoConfig(1, "BB"); }

    private void button3_Click(object sender, EventArgs e)
    { rodeoBO1.FillRodeoConfig(1, "CC"); }

And everything works correctly.

I can only assume that there's something about your table that it doesn't like, probably (as mentioned by several forum members) either the length of the eventCD column, or the length of the @eventCD parameter.

Regards,

Les

GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...





Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search