question

Elado avatar image
0 Votes"
Elado asked Elado edited

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

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


sql-server-generaldotnet-csharpsql-server-transact-sqldotnet-aspnet-core-mvcdotnet-aspnet-core-webapi
לכידה.png (24.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered Elado edited

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Bruce!
How can I determine?

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




I always debug and check the correct error.
But how can determine where is exactly ? Here in the Screenshot.

0 Votes 0 ·
לכידה.png (36.5 KiB)
AgaveJoe avatar image
1 Vote"
AgaveJoe answered Elado commented

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);
     }





· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you very much Joe!
I'll try this and check out.

0 Votes 0 ·
ZhiLv-MSFT avatar image
3 Votes"
ZhiLv-MSFT answered Elado edited

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



image.png (219.4 KiB)
1.gif (1.2 MiB)
image.png (57.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks so much Dillion!
I'll try your solution, your IIS Express solution already work!
(-:

UPDATE:

Ok I fixed it all, the problem was all in the file -

 appsettings.json

In the ConnectionStrings I changed Persist Security Info= from True to False,
Also I thought the password in the string should remain "", thought it was hidden inside of the code, but it wasn't.
Should I use -
Connection String Builders* to make it safe ?

Plus + that lame database service I'm using of "https://somee.com/doka" only allow me to connect with VPN, why? I don't know why but in the first times I successfully connected to it with my original connections - No VPN(?!!!)
Now I active my Anti-Virus VPN.

And the Response body is 100% real!

Thank you both,

@ZhiLv-MSFT and
@AgaveJoe


0 Votes 0 ·