Invalid Attempt To Read Data When No Data Is Present

I have a SQL Server database in C# [built before I got to my present job, creator is gone], and it was fine until last week. On the first page clerk_search.aspx it searches SQL Ser

Solution 1:

You should check the return value of Read method.

If read method returns false then there is no more data. In that case you should not read the data from reader. Doing so will cause this exception.

If you're sure that you'll get only one record try this

if(reader2.Read())
{
    LblLName.Text = "" + reader2["LAST_NAME"];
    LblFName.Text = "" + reader2["FIRST_NAME"];
}

usually DataReader is used as below, since it can contain number of records

while (reader2.Read())
{
    //Consume reader using reader2["Column"] etc
}

Solution 2:

Your code is not properly disposing some objects and it is vulnerable to SQL injection.

DTO:

publicclassEmployee
{
    publicstring FirstName { get; set; }
    publicstring LastName { get; set; }
}

Code:

privatestaticreadonly Lazy<string> ConnectionString = new Lazy<string>(() => WebConfigurationManager.ConnectionStrings["walkin2"].ConnectionString);
privateconststring GetEmployeeBySSNQuery = "SELECT dbo.table_name.SSN, dbo.table_name.LAST_NAME, dbo.table_name.FIRST_NAME, dbo.table_name.MIDDLE_INITIAL, dbo.table_name.COMPONENT_CODE, dbo.table_name.PRESENT_CODE FROM dbo.table_name INNER JOIN dbo.table_name ON dbo.table_name.SSN = @SSN";

protectedvoidPage_Load(object sender, EventArgs e)
{
    // ...if(!IsPostBack)
    {
        GetEmployeeInformation();
    }
}

privatevoidGetEmployeeInformation()
{
    var sctTextBox = (TextBox)Page.PreviousPage.FindControl("Txtsct");
    Txtsct.Text = txtsct.Text; 
    var ssnTextBox = (TextBox)Page.PreviousPage.FindControl("Txtssn");
    Txtssn.Text = ssnTextBox.Text;

    var ssn = ssnTextBox.Text;

    var employee = GetEmployeeBySSN(ConnectionString.Value, ssn);

    if(employee != null)
    {
        LblFName.Text = employee.FirstName;
        LblLName.Text = employee.LastName;
    }
}

private Employee GetEmployeeBySSN(string connectionString, string ssn)
{
    Employee employee = null;

    using(var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using(var command = new SqlCommand(GetEmployeeBySSNQuery, connection)
        {
            command.Parameters.AddWithValue("@SSN", ssn);

            using(var reader = command.ExecuteReader())
            {
                if(reader.Read())
                {
                    employee = new Employee
                                {
                                    FirstName = Convert.ToString(reader["FIRST_NAME"]),
                                    LastName = Convert.ToString(reader["LAST_NAME"])
                                };
                }
            }
        }
    }

    return employee;
}