SQL Server Stored Procedure ignores SqlCommand.ParameterCollection

[Jon and Saranya]

I cannot get a SQL Server 2008R2 stored procedure to acknowledge the VS2008 C# Windows form parameters and return the rows. If the parametercollection is present, no rows are returned. If I comment out the parameters, and the stored procedure uses its default values, I get the correct rows returned.

I started with a DataAdapter.Fill approach, but switched to the SqlCommand and SqlDataReader so that I could inspect the objects and values more easily. When the parameters are Added, I can see the collection of two, and I can see the correct values. I thought using the ampersand in AddWithValue was the problem, so I am adding that in the stored procedure.

Am I missing an obvious step?

thank you, Jon Angel

this.cmdGetRankedScoresheet = new System.Data.SqlClient.SqlCommand();
System.Data.DataRow dr;
this.sqlConnection1.ConnectionString =
System.Configuration.ConfigurationManager.AppSettings["sqlServerConnectionString"].ToString();
this.sqlConnection1.Open();
this.cmdGetRankedScoresheet.Connection = this.sqlConnection1;
this.cmdGetRankedScoresheet.CommandType = CommandType.StoredProcedure;
this.cmdGetRankedScoresheet.CommandText = "vsreports.sp_GetRankedScoresheetFinal";
this.cmdGetRankedScoresheet.Parameters.AddWithValue("@GRADE", this.txtPromYear.Text.Substring(2, 2) + "SP");
this.cmdGetRankedScoresheet.Parameters.AddWithValue("@BOARD_ID", sBoardType);

System.Data.SqlClient.SqlDataReader rdrRankedScoresheet;

rdrRankedScoresheet = cmdGetRankedScoresheet.ExecuteReader();
while (rdrRankedScoresheet.Read())
{
dr = this.dsBoards2.Tables["Ranked_Scoresheet_Final_numbers"].NewRow();
dr["GRADE"] = rdrRankedScoresheet.GetString(0);
dr["BOARD_ID"] = rdrRankedScoresheet.GetString(1);
dr["EMPLOYEE_ID"] = rdrRankedScoresheet.GetString(2);
dr["PHS_NO"] = rdrRankedScoresheet.GetString(3);
dr["Hundred_AVG"] = rdrRankedScoresheet.GetDouble(4);
dr["Eightyfive_AVG"] = rdrRankedScoresheet.GetDouble(5);
dr["BOARDREC"] = rdrRankedScoresheet.GetString(6);
dr["Rank_order"] = rdrRankedScoresheet.GetInt32(7);
this.dsBoards2.Tables["Ranked_Scoresheet_Final_numbers"].Rows.Add(dr);

}
rdrRankedScoresheet.Close();
sqlConnection1.Close();
dataGrid1.Refresh();



ALTER PROCEDURE [vsreports].[sp_GetRankedScoresheetFinal]
(
@GRADE nvarchar(3) = T,
@BOARD_ID nvarchar(9) = 15SP
)
AS
SET NOCOUNT ON;
BEGIN
OPEN SYMMETRIC KEY CCBPMPIIkey
DECRYPTION BY CERTIFICATE CCBPMcert;

DECLARE @GRADEamp nvarchar(3)
DECLARE @BOARD_IDamp nvarchar(9)
SET @GRADEamp = @GRADE + %
SET @BOARD_IDamp = @BOARD_ID + %


SELECT GRADE, BOARD_ID,
CONVERT(VARCHAR(11), DecryptByKey(EMPLOYEE_ID)) as EMPLOYEE_ID,
CONVERT(NVARCHAR(10), DecryptByKey(PHS_NO)) as PHS_NO,
Hundred_AVG, Eightyfive_AVG, BOARDREC, Rank_order
FROM dbo.Ranked_Scoresheet_Final_numbers
WHERE GRADE like @GRADEamp and BOARD_ID like @BOARD_IDamp
ORDER BY BOARD_ID, Rank_order
END

Continue reading...