CODEWITHSUNDEEP
c#asp.netsqlvisual-studioado.net
korben
korben

Reputation: 537

passing SQLDATAREADER info to ASPX with c# properties

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 =\

Upvotes: 0

Views: 1226

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21766

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!

Upvotes: 0

to StackOverflow
to StackOverflow

Reputation: 124746

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.

Upvotes: 1

Related Questions