Matt Syvertson Matt Syvertson - 2 months ago 6
SQL Question

How do I take sql results and populate labels in my header?

I am attempting to change my code. I am in the process of learning while I code and many parts of my C#/ASP.Net application use LINQ to SQL and many parts use commands to directly access the sql database. I felt like I should standardize these, so I am changing over the LINQ to SQL code. I have a portion of the code that takes the finds one record based on a session variable and then populates the labels in my header with the sql query results. here is the code:

protected void Page_Load(object sender, EventArgs e)
{
var pinNum = MySession.Current.focusParcel;

if (pinNum != 0)
{
String sql = ConfigurationManager.ConnectionStrings["Recorder"].ConnectionString;

SqlConnection connection = new SqlConnection(sql);
connection.Open();

SqlCommand command = new SqlCommand("Select PARCEL, PIN_TXT, Owner, Address1, Address2, CSZ, ACRES, LEGAL, Active FROM[ParcelView] WHERE PARCEL = " + pinNum, connection);

SqlDataReader selectedParcel = command.ExecuteReader();

if (selectedParcel != null)
{
lblPIN_Num.Text = selectedParcel["PARCEL"].ToString();
lblPIN_TXT.Text = selectedParcel["PIN_TXT"].ToString();
lblDateTime.Text = DateTime.Now.ToString("MMMM dd, yyyy");
lblOwner.Text = selectedParcel["Owner"].ToString();
lblAddress1.Text = selectedParcel["Address1"].ToString();
lblAddress2.Text = selectedParcel["Address2"].ToString();
lblCSZ.Text = selectedParcel["CSZ"].ToString();
lblAcres.Text = string.Format("{0} Acres", selectedParcel["ACRES"]);
lblLegal.Text = selectedParcel["LEGAL"].ToString();
if (selectedParcel["Active"].ToString() == "A")
{
lblInactive.Text = " (ACTIVE)";
}
else
{
lblInactive.Text = " (INACTIVE)";
lnkAddDocument.Visible = false;
}
}
lblCurrentUser.Text = Page.User.Identity.Name;
connection.Close();
}
else
Response.Redirect("./ParcelSearch.aspx");
}


I am receiving the following error:


Invalid attempt to read when no data is present.


I know the SQL statement used will return a record when it is used directly into sql query of database.

Thanks in advance for any advice. Should I be concerned about the lack of consistency in accessing data across the application at all? If so, should I be converting everything to LINQ? Or converting everything away from LINQ? I klnow of the existence of MVC and know I should learn it and use it, but I am too far down the path to try to convert there right now.

Answer

Change

if (selectedParcel != null)

To

if (selectedParcel.Read())

You have to read a record in the data reader before you can start pulling data. Its a forward only cursor that starts off not pointing to a record. The Read() method advances the reader by 1 and returns true/false if it was successful.

Note that it is not necessary to call HasRows if use the code above. The if block will only be entered if there is a row, if there is not then Read returns false and the block is not entered.


Edit

You need to look at Best Practices - Executing Sql Statements, there are many bad practices in your code.

  1. Use parameterized sql!! This is the most important thing you can take away here!
  2. Wrap your connections in using blocks to ensure the connection is ALWAYS closed.

Sql code changes

String sql = ConfigurationManager.ConnectionStrings["Recorder"].ConnectionString;
using(SqlConnection connection = new SqlConnection(sql))
using(SqlCommand command = new SqlCommand("Select PARCEL, PIN_TXT, Owner, Address1, Address2, CSZ, ACRES, LEGAL, Active FROM [ParcelView] WHERE PARCEL = @pinNum", connection))
{
    command.Parameters.Add(new SqlParameter("@pinNum", SqlDbType.VarChar){Value = pinNum});
    connection.Open();
    using(SqlDataReader selectedParcel = command.ExecuteReader())
    {
        if (selectedParcel.Read())
        {
            /*code unchanged*/
        }
    }
    /* 1 line code unchanged*/
}