Stored Procedures returning null value in razor pages

Question

Hi,

i have two stored procedures which will execute based on the condition.

First Stored Procedure : AvailableHourSlots

CREATE PROCEDURE [dbo].[AvailableHourSlots] 
   @ActivityName nvarchar(max) ,
   @BookedDate datetime2(7)
AS  

    SELECT distinct HourlyBasedTime 
    FROM   ManageBooking as mb, HourlyBased as hb  
    WHERE bookingdate = @BookedDate and ActivityName = @ActivityName
	and (substring(HourlyBasedTime,1,2) <> substring(mb.PreferredTimeslot,1,2))

Second Stored Procedure : AvailableHalfAnHourSlots

CREATE PROCEDURE [dbo].[AvailableHalfAnHourSlots] 
   @ActivityName nvarchar(max) ,
   @BookedDate datetime2(7)
AS  

    SELECT distinct hh.halfanhourtime
FROM ManageBooking as mb, Halfanhour hh
WHERE  bookingdate = @BookedDate
and ActivityName = @ActivityName 
and (substring(hh.halfanhourtime,1,5) <> substring(mb.PreferredTimeslot,1,5))
and (right(hh.halfanhourtime,5) <> right(mb.PreferredTimeslot,5))

Model created based on the First Stored Procedure Output:

namespace ActivityBookingSystem.Models
{
    public class HourlyBasedView
    {
        public string HourlyBasedTime { get; set; }   
        
    }
}

ApplicationDbContext :

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {            
            modelBuilder.Entity<NonOperatingDaysView>().HasNoKey();
            modelBuilder.Entity<AvailableOneHourSlots>().HasNoKey();
            modelBuilder.Entity<HourlyBasedView>().HasNoKey();
            //modelBuilder.Ignore<HourlyBasedView>(); //ignore create the table for the stored procedure
            //modelBuilder.Query<HourlyBasedView>();            
        }

public DbSet<ActivityBookingSystem.Models.HourlyBasedView> HourlyBasedView { get; set; }

create.cshtml: Ajax Call

function GetAvailableSlots(e) {
        var rootPath = '@Url.Content("~")';
        alert("Hi");
        var ActivityList = document.getElementById("DrpDwnActivityList");
        var ActivityListValue = ActivityList.options[ActivityList.selectedIndex].value;
        var BookedDate = document.getElementById("DateBookedDate").value;
        
        $.ajax({
            type: "Get",
            url: rootPath + "/ManageBooking/Create?handler=GetAvailableSlots",
            headers: { "RequestVerificationToken": $('input[name="__RequestVerificationToken"]').val() },
            data: { Duration: e.value,ActivityName:ActivityListValue,BookedDate:BookedDate },
            dataType: "json",
            success: function (result) {
                //console.log(result),  //just for debug
                //    $("#TrainingType").val(result.trainingType), //mind this should be lower-case letters by default setting
                //    $("#TrainingVersion").val(result.version),
                //    $("#SessionType").val(result.sessionType)
                alert(result);
            },
            error: function (data) {
                alert(data);
            }
        });
    }

create.cshtml.cs

public async Task<JsonResult> OnGetGetAvailableSlotsAsync(string ActivityName, string BookedDate, string Duration)
        {
            Console.WriteLine(Duration);
            DateTime BookingDate = Convert.ToDateTime(BookedDate);
            ActivityName = ActivityName.ToString();
            
            if (Duration == "1 Hour")
            {
                var LstPreferredTimeSlot = _context.HourlyBasedView.FromSqlRaw("EXEC dbo.AvailableHourSlots @BookingDate = {0}, @ActivityName = {1}", BookingDate, ActivityName).
                    AsNoTracking().ToList();

                //var PreferredTimeSlot = await _context.HourlyBasedView.FromSqlRaw($"SELECT distinct HourlyBasedTime  FROM   ManageBooking as mb, HourlyBased as hb  WHERE bookingdate = {BookingDate} and ActivityName = {ActivityName} and (substring(HourlyBasedTime,1,2) <> substring(mb.PreferredTimeslot,1,2))").ToListAsync();

                
            }
            return new JsonResult(LstPreferredTimeSlot);
        }

The stored procedure is returning null value, i executed the query separately in my sql management studio,its returning the value.

Any help would be appreciated !

Thanks,

Teena John

Answer 1

I have only looked through your raw code for anything obvious.

What I noticed is that in create.cshtml.cs you call the Stored Procedure with EXEC dbo.AvailableHourSlots @BookingDate = {0}, @ActivityName = {1}

However the Stored Procedure parameters are defined as:

CREATE PROCEDURE [dbo].[AvailableHourSlots] 
   @ActivityName nvarchar(max) ,
   @BookedDate datetime2(7)

@BookingDate is not the same name as @BookedDate and therefore you are not actually passing any value to the @BookedDate parameter of the Stored Procedure which I suspect is the source of your NULL return value. It would also explain why executing directly in SSMS returns a value as you are working directly with the parameter name there.