StrataFrame Forum » .NET Forums » General .NET Discussion » Stored Procedure Error

Stored Procedure Error

Terry Bottorff — Wed, 09 Dec 2009 10:57:22 GMT

I have the following Stored Proc:

[codesnippet]

CREATE PROCEDURE [dbo].[sp_RodeoEvtConfig]

-- Add the parameters for the stored procedure here

@rodeoid int = 1,

@eventcd char(2)

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

[/codesnippet]

I get the following Error (See Attachment)

RE: Stored Procedure Error

Greg McGuffey — Wed, 09 Dec 2009 10:57:22 GMT

Glad you got it going!

RE: Stored Procedure Error

Terry Bottorff — Tue, 08 Dec 2009 13:23:59 GMT

I dropped the SProc and Table in the Database and Recreated both of them. I dropped the BO on the Form and readded it and then rebuilt the project. Now All works fine. Thank all of you for all your help. This is what I ended up with.

This is what I used to Call:

[codesnippet]

Me.RodeoEvtConfigBO1.FillAllRodeoEvtConfig(1, "BB")

[/codesnippet]

This is what the SubRoutine Looks Like.

[codesnippet]

Public Sub FillAllRodeoEvtConfig(ByVal nrod As Integer, _

ByVal cevt As String)

Dim cmd = New SqlCommand("dbo.sp_RodeoEvtConfig")

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@rodeoid", SqlDbType.Int).Value = nrod

cmd.Parameters.Add("@eventCD", SqlDbType.Char, 2).Value = cevt

FillDataTable(cmd)

End Sub

[/codesnippet]

RE: Stored Procedure Error

Greg McGuffey — Tue, 08 Dec 2009 11:28:59 GMT

I didn't check the syntax, so you should check that. But you can (and should) set the length of the parameter in .NET.

RE: Stored Procedure Error

Terry Bottorff — Tue, 08 Dec 2009 09:40:51 GMT

Les I will delete the table and recreate it and the SProc and see what happens. I certainly appreciate everyones time and effort. Thanks. I will get back as soon as I recreate all of this.

RE: Stored Procedure Error

Les Pinter — Tue, 08 Dec 2009 08:42:41 GMT

Hi all,

A quick correction to my posted code: The line reading

cmd.Parameters.Add("@rodeoid",SqlDbType.Int).Value = 1;

should be

cmd.Parameters.Add("@rodeoid",SqlDbType.Int).Value = nrod;

No change, however, to the way the program works (my test records had rodeoID=1...)

Les

RE: Stored Procedure Error

Les Pinter — Tue, 08 Dec 2009 08:38:42 GMT

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

RE: Stored Procedure Error

Terry Bottorff — Tue, 08 Dec 2009 06:22:07 GMT

Greg, I tried it with:

cmd.Parameters.Add("@eventcd", SqlDbType.Char, 2).Value = cevt

cmd.Parameters.Add("@eventcd", SqlDbType.Char, 2).Value = "RB"

I got the same error on both???

Also, Peter the reason for char(2) is: Every record must have 2 characters in this column.

Anyone else have any ideas? I am not sure how to try and find the problem. I can tell you in the SQL Management studio the SProc works like it is suppose to.

Thanks in Advance.

RE: Stored Procedure Error

Peter Jones — Mon, 07 Dec 2009 16:58:10 GMT

Hi Douglas,

I suspect Geof is correct. I'm pretty sure the default length for Char is 1 so if cevt contains more than 1 character it may invalidate the parameter definition causing it not to exist.

Are you sure you want to use Char (fixed length) rather than varChar (variable length) data type.

Cheers, Peter

RE: Stored Procedure Error

Greg McGuffey — Mon, 07 Dec 2009 15:27:32 GMT

Er...I mean in the .NET code.

[codesnippet]cmd.Parameters.Add("@eventcd", SqlDataType.Char, 2).Value = cevt[/codesnippet]

RE: Stored Procedure Error

Greg McGuffey — Mon, 07 Dec 2009 15:25:30 GMT

Problem may be that the char param expects a length. Just guessing, but I'd try setting its length too.