How can I use a data reader in ASP.NET

Question

I am learning to use select query for my program .

This my code.

con.Open();

SqlCommand cmd = new SqlCommand("select id from qw where name='" + TextBox1.Text + "'", con);
SqlDataReader dr = SqlDataReader();
dr = cmd.ExecuteReader();
dr.read();
TextBox2.Text = dr[0].Tostring();
dr.close();
con.Close();

it show this error message

'System.Data.SqlClient.SqlDataReader' is a 'type' but is used like a 'variable'

@Arran you cannot create new instance of SqlDataReader. Check this for more http://www.csharp-station.com/Tutorial/AdoDotNet/lesson04 — Nitin Varpe, Oct 25 '14 at 11:41
@NitinVarpe, well spotted - removed :) (teaches me about not looking at the question properly) — Arran, Oct 25 '14 at 11:41
**[Dupe accounts?](http://stackoverflow.com/questions/26560125/trouble-writing-a-select-query)** — Bjørn-Roger Kringsjå, Oct 25 '14 at 12:48

score 3 · Answer 1 · edited May 23 '17 at 12:11

That's the correct code that match yours above

using(SqlConnection con = new SqlConnection(.......))
using(SqlCommand cmd = new SqlCommand("select id from qw where name=@name", con))
{
    con.Open();
    cmd.Parameters.AddWithValue("@name", TextBox1.Text);
    using(SqlDataReader dr = cmd.ExecuteReader())
    {
       if(dr.read())
       {
           TextBox2.Text = dr[0].ToString();
       }
    }
}

Things changed:

Use the using statement around disposable objects (so they are disposed and closed when no more needed and also in case of exceptions)
Use a parameterized query instead of string concatenation (read about Sql Injection)
Get the reader executing ExecuteReader method of your command
Check if the reader returns something

How can I use a data reader in ASP.NET

1 Answers1