Error using dbExecuteScalar - System.NullReferenceException

Question

User2015884762 posted

Hi, I am trying to use the executescalar method to check if the user exist and subsequently login. However, I am facing error while doing it . This is the error

Object reference not set to an instance of an object. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error: 


Line 37:                 //{
Line 38:                     //Execute only if the username is integer and password is in varchar
Line 39:                     store1 = (Int32)cmd.ExecuteScalar(); // for taking single value
Line 40:                 //}
Line 41: 

This is my code, I have inserted the try catch and that works, however, I know I am missing out something, perhaps it is my SP,  it is returning the ID if the user exists but its returning nothing when it does not exist, how do I work around this. 

Code : -

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace BPSFrontEnd
{
    public partial class index : System.Web.UI.Page
    {
        string connStr = ConfigurationManager.ConnectionStrings["mylocalcon"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                int store1 = 0;
                connection.Open();
                //creating instance of type sqlcommand with 2 constructors
                SqlCommand cmd = new SqlCommand("logindb", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                //add parameters into Parameter collection
                cmd.Parameters.Add("@loginid_app", SqlDbType.Int);
                cmd.Parameters.Add("@loginpassword_app", SqlDbType.VarChar);
                cmd.Parameters["@loginid_app"].Value = txtusername.Text;
                cmd.Parameters["@loginpassword_app"].Value = txtpassword.Text;
               
                //try
                //{
                    //Execute only if the username is integer and password is in varchar
                    store1 = (Int32)cmd.ExecuteScalar(); // for taking single value
                //}

                //catch (NullReferenceException ex)
                //{
                //    Label1.Text = "You have entered a null value in either one" + ex;
                //}
                //catch (FormatException ex)
                //{
                //    Label1.Text = "You have entered the wrong format" + ex;
                //}

                if (store1 != 0)  // comparing users from table 
                {
                    Response.Redirect("Welcome.aspx");  //for successful login
                }
                else
                {
                    
                    Label1.Text = "Invalid User Name or word";  //for invalid login
                   
                }
                connection.Close();
            }
        }
        

    }
}

Stored Procedure

USE [oee]
GO
/****** Object:  StoredProcedure [dbo].[Logindb]    Script Date: 23/9/2019 3:10:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Logindb]
@loginid_app int, 
@loginpassword_app varchar(50)

AS
BEGIN
SELECT * from Login where LoginID = @loginid_app and LoginPassword = @loginpassword_app
END

Answer 1

User-719153870 posted

Hi callykalpana,

callykalpana

USE [oee]
GO
/****** Object:  StoredProcedure [dbo].[Logindb]    Script Date: 23/9/2019 3:10:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Logindb]
@loginid_app int,
@loginpassword_app varchar(50)

AS
BEGIN
SELECT * from Login where LoginID = @loginid_app and LoginPassword = @loginpassword_app
END

If you want to use the cmd.ExecuteScalar(), maybe you can modify your select command in your sp like below:

SELECT count(*) from Login where LoginID = @loginid_app and LoginPassword = @loginpassword_app

Best Regard,

Yang Shen

Answer 2

User753101303 posted

Hi,

Yes, ExecuteScalar returns the first column of the first row so returning at least a row is required.

Answer 3

User2015884762 posted

Thank you, I understand it now.