Connecting to SQL Server in ASP.NET

Question

I am trying to connect to the SQL Server from Visual Web Developer using asp.net but I am facing some problems If anybody helps in this regard i will be greatful.

public partial class _Default : Page
{
    protected void Page_Load(object sender, EventArgs e)            
    {     
        SqlConnection conn = new SqlConnection("Server=localhost;" + "Database=DB;User ID=aaaa;" + "Password=aaaa");

        conn.Open(); SqlDataReader reader = conn.ExecuteReader(); 
        while (reader.Read()) {
            employeesLabel.Text += reader["Name"] + "<br />";
        }
        reader.Close(); 
        conn.Close();               
    }
}

I am getting errors saying

'System.Data.SqlClient.SqlConnection' does not contain a definition for 'ExecuteReader' and no extension method 'ExecuteReader' accepting a first argument of type 'System.Data.SqlClient.SqlConnection' could be found (are you missing a using directive or an assembly reference?)

The name 'employeesLabel' does not exist in the current context.

Can anybody tell the possible reason?

Answer 1

You are not passing any command/query to ExecuteReader something like this would be correct:

SqlDataReader rdr = null;
conn.Open();
SqlCommand cmd = new SqlCommand("select * from Customers", conn);
rdr = cmd.ExecuteReader();

Answer 2

using (SqlConnection conn = new SqlConnection(...))
using (SqlCommand command = conn.CreateCommand())
{
    command.CommandText = "...";
    conn.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
        // do the sutff
    }
}

Answer 3

SqlConnection doesn't have any ExecuteReader() method. You have to make object of SqlCommand. Your code should like this:

namespace LearningASP

{ public partial class _Default : System.Web.UI.Page

{ protected void Page_Load(object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection("Server=localhost;" + "Database=DB;User ID=aaaa;" + "Password=aaaa");

        conn.Open(); 
**SqlCommand cmd = new SqlCommand();
        SqlDataReader reader = cmd.ExecuteReader();** 
while (reader.Read()) {             employeesLabel.Text += reader["Name"] + "<br />"; } reader.Close();          conn.Close();      } } }

Answer 4

Oops! Only IDbCommand implementations have ExecuteReader returning the whole DataReader:

• http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx

Answer 5

i think you have to create object of SqlCommand class also and pass the command string to its constructor. try this"

SqlConnection conn = new SqlConnection("Data Source=serverName;"
           + "Initial Catalog=databaseName;"
           + "Persist Security Info=True;"
           + "User ID=userName;Password=password");

conn.Open();

// create a SqlCommand object for this connection
SqlCommand command = conn.CreateCommand();
command.CommandText = "Select * from tableName";
command.CommandType = CommandType.Text;

// execute the command that returns a SqlDataReader
SqlDataReader reader = command.ExecuteReader();

// display the results
while (reader.Read()) {
    //whatever you want to do.
}

// close the connection
reader.Close();
conn.Close();

Answer 6

Try

SqlConnection conn = new SqlConnection("Server=localhost;" + "Database=DB;User ID=aaaa;" + "Password=aaaa");              
conn.Open();
SqlCommand cmd = new SqlCommand("Your Query", conn);//Put your query here
SqlDataReader reader = cmd.ExecuteReader();              
while (reader.Read()) 
{                 
    employeesLabel.Text += reader["Name"] + "<br />"; 
} 
reader.Close();             
conn.Close();