CODEWITHSUNDEEP
c#sqlsql-server
webdad3
webdad3

Reputation: 9080

Odd SQL Error int parameter converting to nvarchar

Error:

The parameterized query '(@TeacherID nvarchar(9),@LeaderID nvarchar(9),@Status int,' expects the parameter '@ORatingID', which was not supplied.

Stored procedure parameters:

@TeacherID varchar(9),
@LeaderID varchar(9),
@Status int,
@SharedDraft bit,
@SGrowthRatingID int,
@ORatingID int,
@Q1Text varchar(max),
@Q2Text varchar(max),
@Q3Text varchar(max),
@Q4Text varchar(max)

SQL Server Profiler shows:

exec sp_executesql N'usp_EOYASave @TeacherID, @LeaderID, @Status, 
@SharedDraft, @SGrowthRatingID, @ORatingID, @Q1Text, @Q2Text, @Q3Text, 
@Q4Text',N'@TeacherID nvarchar(9),@LeaderID nvarchar(9),@Status int,
@SharedDraft bit,@SGrowthRatingID int,@ORatingID nvarchar(4000),
@Q1Text nvarchar(11),@Q2Text nvarchar(4),@Q3Text nvarchar(4000),
@Q4Textnvarchar(4)',@TeacherID=N'100',@LeaderID=N'1002',@Status=7,
@SharedDraft=0,@SGrowthRatingID=1,@ORatingID=default,
@Q1Text=N'test 123 bc',@Q2Text=N'test',@Q3Text=N'',@Q4Text=N'test'

NOTICE:

@ORatingID nvarchar(4000) 
@ORatingID=default

Calling code in C#:

db.Database.ExecuteSqlCommand("usp_EOYASave @TeacherID, @LeaderID, @Status,
         @SharedDraft, @SGrowthRatingID, @ORatingID, 
         @Q1Text, @Q2Text, @Q3Text, @Q4Text",
   new SqlParameter("TeacherID", EOYRating.CurrentTeacher.ID),
   new SqlParameter("LeaderID", EOYRating.IsLeader == true ?
                 EOYRating.CurrentLeader.LeaderID : null),
   new SqlParameter("Status", EOYRating.IsLeader == false ?
                 EOYRating.TeacherStatus : EOYRating.LeaderStatus),
   new SqlParameter("SharedDraft", sharedDraft),
   new SqlParameter("ORatingID", EOYRating.IsLeader == true ?
                (Int32?)EOYRating.OverallRatingValue : null),
   new SqlParameter("SGrowthRatingID", EOYRating.IsLeader == true ?
                (Int32?)EOYRating.StudentGrowthRatingValue : null),
   new SqlParameter("Q1Text", q1Text != null ? q1Text : ""),
   new SqlParameter("Q2Text", q2Text != null ? q2Text : ""),
   new SqlParameter("Q3Text", q3Text != null ? q3Text : ""),
   new SqlParameter("Q4Text", q4Text != null ? q4Text : ""));

Question:

When I run this code (in SQL Server) and change the "default" to a 0 or null it works.

Why is it converting it (or recognizing it) as a nvarchar(4000)?

Update:

I tried this after a suggestion:

new SqlParameter("ORatingID", SqlDbType.Int, 4, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Current , EOYRating.IsLeader == true ? (Int32?)EOYRating.OverallRatingValue : null),

I received the same error message.

Upvotes: 2

Views: 1185

Answers (1)

EkoostikMartin
EkoostikMartin

Reputation: 6921

Probably this constructor is using reflection to check the dataype of the parameter, and since the value is null, it assumes it is a string. You can just specify the sql datatype using an alternate constructor.

var param = new SqlParameter("ORatingID", 
   SqlDbType.Int)

param.Value = EOYRating.IsLeader == true ? (Int32?)EOYRating.OverallRatingValue : (Int32?)null;

Upvotes: 3

Related Questions