passing SQLDATAREADER info to ASPX with c# properties

Question

I'm a beginner and trying to do something you all find simple. I'd like to run a SQL select on page load in my CS and access that data from my ASPX, the following is my default.cs code:

public partial class _Default : System.Web.UI.Page
{
private SqlDataReader reader = null;
public SqlDataReader Reader { get { return reader; } set { reader = value; } }

    protected void Page_Load(object sender, EventArgs e)
    {
    string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
    using (SqlConnection connection = new SqlConnection(connectionString)) {
    connection.Open();

    using (SqlCommand command = new SqlCommand("SELECT * FROM uploads WHERE status IS NULL AND uploader = @uploader", connection)) {
        command.Parameters.Add(new SqlParameter("uploader", "anonymous"));

        Reader = command.ExecuteReader();
    }
    }
    }
}

in my aspx I'm trying to use something like:

<%= Reader.GetString(1) %>

but I keep getting the following errors: Exception Details: System.InvalidOperationException: Invalid attempt to call MetaData when reader is closed.

I know that my using statement is closing the connection when it ends, but I'm not sure why I cannot access the data from my ASPX. If I use the same GetString(1) value within the using loop I can access the relevant data fine.

I'm basically just trying to output all the rows in my results =\

to StackOverflow · Accepted Answer · 2012-02-06 17:37:51Z

1

You should really be updating your page from inside the "using" block. In its simplest form, you could add a Literal control to your page, and update it in the using block:

Reader = command.ExecuteReader();
...
Literal1.Text = Reader.GetString(1);

When you use the following syntax in your markup:

<%= Reader.GetString(1) %>

the compiler will generate a Response.Write statement that is executed during the Render phase of the page life cycle. In your case, the connection is disposed by then.

You could keep the connection and reader alive, and dispose of it in an override of the page's Dispose method, but I wouldn't recommend this approach.

answered Feb 6, 2012 at 17:37

to StackOverflow

125k34 gold badges212 silver badges345 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oleg Dok · Accepted Answer · 2012-02-06 17:43:55Z

0

You should not dispose sql command and sql connection.

Remove using keywords from your Load event, because when you exiting from using section - the connection closes which causes closing the reader. After the reader closed - you cannot use it in rendering or databinding.

public partial class _Default : System.Web.UI.Page
{
private SqlDataReader reader = null;
public SqlDataReader Reader { get { return reader; } set { reader = value; } }
    protected void Page_Load(object sender, EventArgs e)
    {
    string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
    SqlConnection connection = new SqlConnection(connectionString);
    connection.Open();

    SqlCommand command = new SqlCommand("SELECT * FROM uploads WHERE status IS NULL AND uploader = @uploader", connection);
        command.Parameters.Add(new SqlParameter("uploader", "anonymous"));

        Reader = command.ExecuteReader();
    }
}

BUT

do not forget to dispose reader, command and connection upon you finished to render the page!

edited Feb 6, 2012 at 17:43

answered Feb 6, 2012 at 17:21

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

3 Comments

John Saunders Over a year ago

Please say why he shouldn't use using in this case.

John Saunders Over a year ago

@Joe: yes, they need to be disposed, but not at that point in the code if he plans to use it during data binding.

Oleg Dok Over a year ago

@Joe regarding your -1 - updating answer

Collectives™ on Stack Overflow

passing SQLDATAREADER info to ASPX with c# properties

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related