How to fix "System.NullReferenceException" When connecting ASP.NET 6 to MSSQL database

daowdos 261 Reputation points
2022-09-20T15:28:12.553+00:00

I'm using ASP.net Core App to connect to MSSQL database.
I checked the connection when -
Connect to Database and the database is connected.

But when I'm starting the project I'm getting this error:

Error: response status is 500

System.NullReferenceException: Object reference not set to an
instance of an object. at
TicToeWeb.Controllers.GamesController.GetGameStatistics()
in C:\Users\Elop\Desktop\TicToeWeb\Controllers\GamesController.cs

Microsoft.Extensions.Configuration.ConfigurationExtensions.GetConnectionString(...)
returned null.

I debug this is the line where getting the error -

 SqlConnection connection = new SqlConnection(_configuration.GetConnectionString("tictaktoedb1").ToString());  

 

   **GamesController.cs**  

    [HttpGet]  
            [Route("GamesList")]  
            public Response GetGameStatistics()  
            {  
                List<GameStatistics> lstStatistics = new List<GameStatistics>();  
  
                SqlConnection connection = new SqlConnection(_configuration.GetConnectionString("tictoedb").ToString());  
                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Get_Statistics", connection);  
                DataTable dt = new DataTable();  
                da.Fill(dt);  
                Response response = new Response();  
  
                if (dt.Rows.Count > 0)  
                {  
                    for (int i = 0; i < dt.Rows.Count; i++)  
                    {  
                        GameStatistics gameStatistics = new GameStatistics();  
                        gameStatistics.Game_Number = Convert.ToInt32(dt.Rows[i]["Number_Of_Games"]);  
                        gameStatistics.User1_Wins = Convert.ToInt32(dt.Rows[i]["User1_Wins"]);  
                        gameStatistics.User2_Wins = Convert.ToInt32(dt.Rows[i]["User2_Wins"]);  
                        lstStatistics.Add(gameStatistics);  
                    }  
                    if (lstStatistics.Count > 0)  
                    {  
                        response.StatusCode = 200;  
                        response.StatusMessage = "Data found";  
                        response.liststats = lstStatistics;  
                    }  
                    else  
                    {  
                         response.StatusMessage = "No data found";  
                     }  
                }  
                else  
                {  
                     
                         response.StatusMessage = "No data found";  
                 
                }  
                return response;  
            }  

And why the H is it opening this CMD window together with Chrome?

243009-%D7%9C%D7%9B%D7%99%D7%93%D7%94.png

System.NullReferenceException HResult=0x80004003 Message=Object
reference not set to an instance of an object. Source=TicToeWeb
StackTrace:

Any idea how can I solve this ?
Thanks in advanced

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,076 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,481 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,095 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,535 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Zhi Lv - MSFT 31,756 Reputation points Microsoft Vendor
    2022-09-21T05:52:08.023+00:00

    Hi @Elado,

    Microsoft.Extensions.Configuration.ConfigurationExtensions.GetConnectionString(...) returned null.

    Perhaps the issue relates to the connection string name. By default, asp.net core stores the connection string in the appsettings.json file, you can check it and make sure your are using the correct connection string.

    Refer to the following screenshot, since the connection name not match, it will show the NullReferenceException:

    243254-image.png

    After modify changing the connection name, the code works well:

    243268-1.gif

    And why the H is it opening this CMD window together with Chrome?

    About this issue, try to use IIS Express to debug your application, then, the CMD window will disappear.

    243256-image.png


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Best regards,
    Dillion

    4 people found this answer helpful.

  2. AgaveJoe 25,866 Reputation points
    2022-09-20T19:37:56.653+00:00

    Visual Studio has a feature that allows you to stop execution and single step through the code to find bugs.

    First look at the Visual Studio Debugger

    It might be easier to debug if you refactor the code.

    string conn = _configuration.GetConnectionString("tictoedb");  
    SqlConnection connection = new SqlConnection(conn);  
    

    Set a break point on the _configuration line.
    Run the project.
    Place your cursor over _configuration to see if it is null. You can also use the QuickWatch or the Watch view. If _configuration is not null then press F10 to execute the line of code.
    Check the value of conn. If it is null then your connection string was not found in appsettings.json.

    DI sample code.

    public class HomeController : Controller  
    {  
        private readonly ILogger<HomeController> _logger;  
        private readonly IConfiguration _configuration;  
        public HomeController(ILogger<HomeController> logger, IConfiguration configuration)  
        {  
            _logger = logger;  
            _configuration = configuration;  
        }  
      
        public IActionResult Index()  
        {  
            string conn = _configuration.GetConnectionString("MvcSqlLiteConnectionString");  
            _logger.LogInformation(conn);  
            return Ok(conn);  
        }  
    
    2 people found this answer helpful.

  3. Bruce (SqlWork.com) 53,426 Reputation points
    2022-09-20T15:45:37.117+00:00

    either _configuration is null, or GetConnectionString() is retuning null. use the debugger to determine.


  4. Laxmikant 216 Reputation points
    2022-09-26T21:14:19.807+00:00

    have you added an appsettings.json file to the code, you may use ConfigurationBuilder for it.

    var builder = new ConfigurationBuilder()  
                .SetBasePath(Directory.GetCurrentDirectory())  
                .AddJsonFile("appsettings.json",   
                    optional: false, reloadOnChange: true);  
            _iconfiguration = builder.Build();  
    

    for more details visit - https://geeksarray.com/blog/access-sql-server-database-from-net-core-console-application

    0 comments No comments