ERROR - Must Declare Scalar Variable - C#

Question

I am doing a function for uploading files into folder using C#. It occur and error where the error is "Must declare the scalar variable "@TABLE_ID" after I click upload. I have done some research and try to use others example but the error still occurs. This is the function for upload:

protected void UploadFile(object sender, EventArgs e)
{
   string str = ConfigurationManager.ConnectionStrings["Ulysses"].ConnectionString;
   string filenam = FileUpload1.FileName.ToString();
   string TABLE_NAME = "REQUEST";

   int table_id = -1;
   table_id = generateNextId("SP_LINK_TABLE_FIELD_ID");

   string path = @"C:\Users\muhd\Documents\CyberLink";

   path = path + filenam;
   FileUpload1.PostedFile.SaveAs(path);

   SqlConnection con = new SqlConnection(str);
   SqlCommand cmd = new SqlCommand();
   cmd.CommandText = "Insert into OLE(LINK_TABLE_ID, LINK_TABLE_NAME, OLE_LINK_FILE_NAME) values(@TABLE_ID,@TABLE_NAME,@FILE_NAME)";
   cmd.CommandType =  CommandType.Text;
   command.Parameters.AddWithValue("@TABLE_ID", SqlDbType.Int).Value = table_id;
   command.Parameters.AddWithValue("@TABLE_NAME", TABLE_NAME);
   command.Parameters.AddWithValue("@FILE_NAME",path);
   cmd.Connection = con;
   con.Open();
   cmd.ExecuteNonQuery();
   con.Close();
}

the table_id is taken from a stored procedure using another function. Please refer below:

protected int generateNextId(string strStoredProcedureName)//this is for inserting table_field_id for uploading attachment
{
  int intNewId = 0;
  try
  {
    strConn = ConfigurationManager.ConnectionStrings["Ulysses"].ToString();
    conn = new SqlConnection(strConn);
    command = new SqlCommand(strStoredProcedureName, conn);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@newid", SqlDbType.Int);
    command.Parameters["@newid"].Direction = ParameterDirection.ReturnValue;
    conn.Open();
    command.ExecuteNonQuery();
    intNewId = (int)command.Parameters["@newid"].Value;
  }
  catch (Exception ex)
  {
  }
  finally
  {
    conn.Close();
    conn = null;
  }
  return intNewId;
}

This is the stored procedure that I use:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_LINK_TABLE_FIELD_ID]
AS
begin tran;

declare @nextid integer;

update generator 
  set current_number = current_number + 1 
  where generator = 'LINK_TABLE_FIELD';

select @nextid = current_number 
  from generator 
  where generator = 'LINK_TABLE_FIELD';

commit tran;

return @nextid;

I am not sure why it needed to be declare even other function with the same style has no problem with the code but this one shows this error. I hope anyone can give some insight about this problem.

Answer 1

The command you're executing is cmd and not command.You're not adding the parameters to the correct command object.

command.Parameters.AddWithValue("@TABLE_ID", SqlDbType.Int).Value = table_id;
command.Parameters.AddWithValue("@TABLE_NAME", TABLE_NAME);
command.Parameters.AddWithValue("@FILE_NAME",path);

should be:

cmd.Parameters.AddWithValue("@TABLE_ID", SqlDbType.Int).Value = table_id;
cmd.Parameters.AddWithValue("@TABLE_NAME", TABLE_NAME);
cmd.Parameters.AddWithValue("@FILE_NAME",path);