1

I am trying to achieve this with 2 different syntax'. In the first I am using _context.FromSql and the second I'm using the datareader syntax. In the first attempt, my error is Must declare the scalar variable "@radLastName". In the second attempt, my error is InvalidOperationException: The model item passed into the ViewDataDictionary is of type 'System.Data.SqlClient.SqlDataReader', but this ViewDataDictionary instance requires a model item of type 'System.Collections.Generic.IEnumerable`1[TRAIntranet2019.Models.vw_GetRadQAAddendums]' ... How do I convert my view dictionary, or how do I declare the scalar variable? 

//first attempt
var result = 
_context.vw_GetRadQAAddendums.FromSql("dbo.usp_RadQAAddendumsNonAdmin 
@radLastName, @radFirstName", LN, FN).ToList();

//second attempt
_context.Database.OpenConnection();
            var command = 
_context.Database.GetDbConnection().CreateCommand();
            command.CommandText = "dbo.usp_RadQAAddendumsNonAdmin";
            command.CommandType = 
System.Data.CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@radLastName", Ln));
            command.Parameters.Add(new SqlParameter("@radFirstName", 
Fn));
            var RQA = (command.ExecuteReader());

            return View(RQA);

//variables with data for the parameters
        var FN = (string)@ViewBag.TechQAFirstName ?? "";
        var LN = (string)@ViewBag.TechQALastName ?? "";
        var Fn = (string)@ViewBag.FirstName ?? "";
        var Ln = (string)@ViewBag.LastName ?? "";


ALTER PROCEDURE [dbo].[usp_RadQAAddendumsNonAdmin]
    -- Add the parameters for the stored procedure here
    @radLastName varchar(100),
    @radFirstName varchar(100)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    select  a.AccessionNumber, c.order_SignLastName, c.Order_SignDateTime, 
b.CreateDate, a.MRN,  a.LastName + ', ' + a.FirstName as PatientName, 
b.Findings, b.Reccommendation, b.DueDate, b.CreateDate 
    from [RadQA].dbo.tblRadQAReports a 
    inner join [RadQA].dbo.tblRadQAAnswers b on a.RadQAReportsID = 
b.RadQAOrdersID 
    inner join [HL7].dbo.Order_Tracking c on a.AccessionNumber = 
c.order_accession 
    where c.Order_SignLastName = @radLastName and c.Order_SignFirstName = 
@radFirstName
    and  (b.AddendumDate is NULL and b.NotifiedDate is NULL and 
b.PublishDate is Not NULL and b.RemoveDate is NULL) 
END
qpc4ever
  • 67
  • 6
  • 1
    You seem to be `ExecuteReader`ing the command before you set the values of the variables. And are those the actual names of the variables in the stored procedure? Or just what you have called them in your C#? – Jacob H Jul 26 '19 at 19:34
  • Yes, those are definitely the correct names. And when I set a break point, LN and FN have the correct expected values. – qpc4ever Jul 26 '19 at 19:40
  • Can we get the stored procedure header as well? i.e. `ALTER PROCEDURE usp_RadQAAddendumsNonAdmin ... AS ` – Matti Jul 26 '19 at 20:23
  • I added the stored procedure. – qpc4ever Jul 26 '19 at 20:34
  • 1
    `In the second attempt, my error is InvalidOperationException: The model item passed into the ViewDataDictionary` OK, so you have two **completely different problems**. First up - the SQL. The second attempt solves it, so ignore the first attempt. So, first problem is solved. Now your second problem is to do with the fact that you are passing a `SqlDataReader` to your view (which is what your error says). This is a terrible idea since your view expects an `IEnumerable` (which is what your error says). See the duplicate. – mjwills Jul 26 '19 at 21:46

0 Answers0