I have the following Stored Proc:

I get the following Error (See Attachment)

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

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

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

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.

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

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

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.

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

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:

This is what the SubRoutine Looks Like.