SqlExeption: Must declare the scalar variable "@theCategoryNbr"

[Silvers11]

Hello,

I am using SQL Server Express and have created a Storage Procedure: "getallFeatureNbrsForSymbolandCategory"

Now I have declared two parameters as seen there: @theSymbolNbr and @theCategoryNbr.


But when I call this procedure from C#, I receive this error. I can't understand what could be missing?

I get an error:

System.Data.SqlClient.SqlExeption (0x80131904): Must declare the scalar variable "@theCategoryNbr" at SqlConnection.OnError


Storage Procedure: "getallFeatureNbrsForSymbolandCategory"

CREATE PROCEDURE getallFeatureNbrsForSymbolandCategory
@theSymbolNbr SMALLINT,
@theCategoryNbr TINYINT

AS

BEGIN
DECLARE @FullQuery nvarchar(1000)
SET @FullQuery = N'SELECT _FeatureNbr FROM allFeaturesNumbersTable WHERE _FeatureCategory = @theCategoryNbr AND
_SymbolNbr = @theSymbolNbr'


EXECUTE sp_executesql
@FullQuery,
N'@theSymbolNbr SMALLINT', @theSymbolNbr,
N'@theCategoryNbr TINYINT', @theCategoryNbr;
END


C# code to execute the Storage Procedure

void function1()
{
SqlConnection connection = new SqlConnection(GetConnectionString());

Int16 SymbolNbr = 0;
byte CategoryNbr = 0;
using (SqlCommand cmd = new SqlCommand("getallFeatureNbrsForSymbolandCategory", connection)) //1. create a command object identifying the stored procedure (gettheSymbolIndex)
{
cmd.CommandType = CommandType.StoredProcedure; //2. set the command object so it knows to execute a stored procedure
cmd.Parameters.Add(new SqlParameter("@theSymbolNbr", SymbolNbr)); //(SMALLINT)
cmd.Parameters.Add(new SqlParameter("@theCategoryNbr", CategoryNbr)); //(TINYINT)
using (SqlDataReader rdr = cmd.ExecuteReader()) //execute the command
{
while (rdr.Read()) //iterate through results
{
//do something
}
}
}
}
public String GetConnectionString() { return "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\andre\\Desktop\\!!!key-gen\\App123\\featuresDatabase1.mdf;Integrated Security=True;Connect Timeout=3600"; }
Thank you!

Continue reading...