namasayadin namasayadin - 4 months ago 94
SQL Question

ERROR - Must Declare Scalar Variable - C#

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. Thanks in advanced.

Answer

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);
Comments