Not getting any results...

Question

I'm trying to create a website using ASP.Net and a SQL server connection to retrieve data which is all of data type "nvarchar" and not null. My code is as follows:

(Database connection here)

            using (SqlConnection connection = new(connectionString))

            {

                connection.Open();

                String sql = "select * from Info where Format = 'Ultra HD' order by Title";

                using (SqlCommand command = new(sql, connection))

                {

                    using (SqlDataReader reader = command.ExecuteReader())

                    {

                        while (reader.Read())

                        {

                            MovieInfo movieinfo = new()

                            {

                                Title = reader.GetChar(0).ToString(),

                                Year = reader.GetChar(1).ToString(),

                                Format = reader.GetChar(2).ToString(),

                                Length = reader.GetChar(3).ToString(),

                                Video = reader.GetChar(4).ToString(),

                                Audio = reader.GetChar(5).ToString(),

                            };

                            ListMovies.Add(movieinfo);

                        }

                    }

                }

            }

        }

        catch (Exception ex)

        {

            Console.WriteLine("Exception: " + ex.ToString());

        }

    }

}

public class MovieInfo

{   public String Title;

    public String Year;

    public String Format;

    public String Length;

    public String Video;

    public String Audio;

}

}

I'm guessing that there are no results because I'm doing something wrong in the "While" clause. Anyone have suggestions as to what I might do to fix this?

Answer 1

I'm guessing that there are no results because I'm doing something wrong in the "While" clause. Anyone have suggestions as to what I might do to fix this?

Use the Visual Studio debugger to step through your code and make sure the List<MovieInfo> has data.

https://learn.microsoft.com/en-us/visualstudio/debugger/debugger-feature-tour?view=vs-2022

I noticed you have a try...catch that writes to the console which could be hiding exceptions unless you are checking the console for messages. Perhaps your code is throwing an exception which is causing the zero results. It might be easier to remove the try...catch for now so any exceptions are displayed in the browser.

Lastly, what kind of ASP.NET application are you building; Web Forms, MVC, Web API?

Is there any way you can show use the code render the HTML or returns JSON/XML?

In Web Forms we need to see where you are binding List<MovieInfo> to data bound control. In MVC you would be passing a List<MovieInfo> model to a view. We need the controller and view.

Answer 2

you have a couple issues.

• you do a select *, but the code assume the column order. specify the column names in the select so you know the return order
• .GetChar() only return the first character of a column value
• if no rows are returned, then "Format = 'Ultra HD'" is finding no matches

Answer 3

Fixed: It seems removing the section on the connectionString that refers to "trust server certification" fixed the issue: Thanks for the help!

Answer 4

Anyone know of the easiest way to add the record count to the webpage using the code previously provided above?

Your Program.cs files looks like Razor Pages. If ListMovies is a property within the Razor Page then the Razor Syntax is...

@Model.ListMovies.Count()

Markup

@page
@model RazorPagesDemo.Pages.MoviesModel
@{
    ViewData["Title"] = "Movies";
}

<div>
    My Count = @Model.ListMovies.Count()
</div>

Code

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
namespace RazorPagesDemo.Pages
{
    public class MoviesModel : PageModel
    {
        public void OnGet()
        {
            for (int i = 0; i < 5; i++)
            {
                ListMovies.Add($"Movie {i}");
            }
        }
        public List<string> ListMovies { get; set; } = new(); 
    }
}