SQL Server stored procedure throwing error

Question

I get this error:

Must declare the scalar variable "@ECAT_ID"

How to fix this? Please help me

 ALTER PROCEDURE [dbo].[spADDEVENT]
     (@CULTEVNT_NAME varchar(50),
      @CULTEVNT_SDATE Date,
      @CULTEVNT_EDATE Date,
      @CULTEVNT_RULES1 varchar(255),
      @CULTEVNT_RULES2 varchar(255),
      @CULTEVNT_RULES3 varchar(255),
      @CULTEVNT_RULES4 varchar(255),
      @CULTEVNT_FEES INT,
      @CULTEVNT_ADMIN VARCHAR(50),
      @ECAT_NAME VARCHAR(50),
      @E_ADMIN VARCHAR(50),
      @ESUBCAT_NAME VARCHAR(50),
      @E_ID INT,
      @ESUBCATS_ID INT,
      @ECATS_ID INT)
AS
BEGIN
    --DECLARE @ECATS_ID INT
    INSERT INTO EVENT_CAT(ECAT_ID, ECAT_NAME, E_ADMIN)
    VALUES(@ECATS_ID, @ECAT_NAME, @E_ADMIN)

    SET @ECATS_ID = SCOPE_IDENTITY();

    --DECLARE @ESUBCATS_ID INT
    INSERT INTO EVENT_SCAT(@ESUBCATS_ID, @ECAT_ID, @ESUBCAT_NAME)
    VALUES(@ESUBCATS_ID, @ECATS_ID, @ESUBCAT_NAME)

    SET @ESUBCATS_ID = SCOPE_IDENTITY();

    INSERT INTO CULTEVENT_T(CULTEVNT_NAME, CULTEVNT_SDATE, CULTEVNT_EDATE, 
                            CULTEVNT_RULES1, CULTEVNT_RULES2, 
                            CULTEVNT_RULES3, CULTEVNT_RULES4, 
                            CULTEVNT_FEES, CULTEVNT_ADMIN, 
                            ESUBCATS_ID, ECATS_ID, E_ID)
    VALUES (@CULTEVNT_NAME, @CULTEVNT_SDATE, @CULTEVNT_EDATE,
            @CULTEVNT_RULES1, @CULTEVNT_RULES2,
            @CULTEVNT_RULES3, @CULTEVNT_RULES4,
            @CULTEVNT_FEES, @CULTEVNT_ADMIN,
            @ESUBCATS_ID, @ECATS_ID, @E_ID)
END

check whole stored procedure is this correct?

this is my C# code.

 protected void addevnt_Click(object sender, EventArgs e)
{
    string spname = "spADDEVENT"; ;
    con.Open();
    SqlCommand com = new SqlCommand(spname, con);
    com.CommandType = CommandType.StoredProcedure;
    com.Parameters.AddWithValue("@CULTEVNT_NAME", txtevntname.Text.ToString());
    com.Parameters.AddWithValue("@ECAT_NAME", Textecat.Text.ToString());
    com.Parameters.AddWithValue("@ESUBCAT_NAME",Textscat.Text.ToString());
    com.Parameters.AddWithValue("@CULTEVNT_ADMIN ",txtaname.Text.ToString());
    com.Parameters.AddWithValue("@E_ADMIN", adminame.Text.ToString());
    com.Parameters.AddWithValue("@CULTEVNT_SDATE", txtesdate.Text.ToString());
    com.Parameters.AddWithValue("@CULTEVNT_EDATE", txtedate.Text.ToString());
    com.Parameters.AddWithValue("@CULTEVNT_RULES1", txtrul1.Text.ToString());
    com.Parameters.AddWithValue("@CULTEVNT_RULES2", txtrul2.Text.ToString());
    com.Parameters.AddWithValue("@CULTEVNT_RULES3", txtrule3.Text.ToString());
    com.Parameters.AddWithValue("@CULTEVNT_RULES4", Textrule4.Text.ToString());
    com.Parameters.AddWithValue("@CULTEVNT_FEES",txtfees.Text.ToString());
    com.Parameters.AddWithValue("@ESUBCATID", txtfees.Text.ToString());

    com.ExecuteNonQuery();
    con.Close();
    lblMessage1.Text = "data entered";

}

actually i want to insert data in different tables from single form.and tables contains pk and fk

and i have put most id of the tables as a identity column.

you can look up my previous Q it contains tables ok

Answer 1

It is not easy to describe the errors, so I have used comments in the code. Basically they are in the variables for holding the identity values were in the wrong place, had no value, and were trying to be inserted without a value, and without set identity_insert [tbl] on... off.

ALTER PROCEDURE [dbo].[spADDEVENT]
     (@CULTEVNT_NAME varchar(50),
      @CULTEVNT_SDATE Date,
      @CULTEVNT_EDATE Date,
      @CULTEVNT_RULES1 varchar(255),
      @CULTEVNT_RULES2 varchar(255),
      @CULTEVNT_RULES3 varchar(255),
      @CULTEVNT_RULES4 varchar(255),
      @CULTEVNT_FEES INT,
      @CULTEVNT_ADMIN VARCHAR(50),
      @ECAT_NAME VARCHAR(50),
      @E_ADMIN VARCHAR(50),
      @ESUBCAT_NAME VARCHAR(50),
      @E_ID INT--,
      /* if these are identity, you can not send the value
         maybe you want them as output parameters? */
      --@ESUBCATS_ID INT, 
      --@ECATS_ID INT
      )
AS
BEGIN
    DECLARE @ECATS_ID INT
    /* You do not know the value of @ECATS_ID yet, do you? So you can not insert it */
    INSERT INTO EVENT_CAT(ECAT_NAME, E_ADMIN)
    VALUES(@ECAT_NAME, @E_ADMIN)

    SET @ECATS_ID = SCOPE_IDENTITY();
    /* now you know the value of @ECATS_ID, so you can use that one in the next insert */

    DECLARE @ESUBCATS_ID INT
    /* Insert into tbl (columns) ... , not insert into tbl (@parameters) ...*/
    /* You do not know the value of @ESUBCATS_ID yet, do you? So you can not insert it */
    INSERT INTO EVENT_SCAT(ECAT_ID, ESUBCAT_NAME)
    VALUES(@ECATS_ID, @ESUBCAT_NAME)

    SET @ESUBCATS_ID = SCOPE_IDENTITY();
    /* now you know the value of @ESUBCATS_ID, so you can use that one in the next insert */

    INSERT INTO CULTEVENT_T(CULTEVNT_NAME, CULTEVNT_SDATE, CULTEVNT_EDATE, 
                            CULTEVNT_RULES1, CULTEVNT_RULES2, 
                            CULTEVNT_RULES3, CULTEVNT_RULES4, 
                            CULTEVNT_FEES, CULTEVNT_ADMIN, 
                            ESUBCATS_ID, ECATS_ID, E_ID)
    VALUES (@CULTEVNT_NAME, @CULTEVNT_SDATE, @CULTEVNT_EDATE,
            @CULTEVNT_RULES1, @CULTEVNT_RULES2,
            @CULTEVNT_RULES3, @CULTEVNT_RULES4,
            @CULTEVNT_FEES, @CULTEVNT_ADMIN,
            @ESUBCATS_ID, @ECATS_ID, @E_ID)

Answer 2

Your parameter is @ECATS_IDnot @ECAT_ID