ASP & C#: Why can't I connect to MS-SQL database?

[and12]

It is catching my exception error when I try to do a select student_id student_name from Student and put it in my dropdownlist. I already created the databases called college and tables Student, Course, and StudentCourse and put in fake data.

I store my Connection String in the web.config file.

<configuration>
  <connectionStrings>
    <add name="College"
         connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=College;
         Integrated Security=true"/>
  </connectionStrings>
</configuration>

I also set the read and write permissions for my College.mdf and College_log.LDF using windows Explorer.

Yet I can't get data from my database after I compile successfully and load the page.

Does anyone have a clue what else I may need to do?

protected void Page_Load(object sender, EventArgs e)
{
            if(!this.IsPostBack)
            {
                FillStudentList();
            }
      
}

private void FillStudentList()
{
            
            StudentListDropDownList.Items.Clear();

            string connectionString = WebConfigurationManager.ConnectionStrings["College"].ConnectionString;


                // Define the ADO.NET objects for selecting student ID from the datablse
                string selectSQL = "SELECT student_id student_name FROM Student";
               
                //Define the ADO.NET objects
                SqlConnection con = new SqlConnection(connectionString);


            //    con.ConnectionString = @"Data Source=localhost\SQLEXPRESS;" +
            //        @"User Instance=True;AttachDBFile=|DataDirectory|\College.mdf;" +
            //        "Integrated Security=True";

                SqlCommand cmd = new SqlCommand(selectSQL, con);

                SqlDataReader reader;

                try
                {
                    //Open the connection
                    con.Open();
                    reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        ListItem newItem = new ListItem();
                        newItem.Text = reader["student_id"] + " " + reader["firstname"];
                        newItem.Value = reader["student_id"].ToString();
                        StudentListDropDownList.Items.Add(newItem);
                    }
                    reader.Close();

                    InfoLabel.Text = "<b> Server Version: </b> " + con.ServerVersion;
                    InfoLabel.Text += "<br/><b>Connection Is:</b> " + con.State.ToString();
                }
                catch (Exception err)
                {
                    //Handle an error by displaying the information.
                    InfoLabel.Text = "Error reading the database.";
                    InfoLabel.Text += err.Message;
                }
                finally
                {
                    con.Close();
                }

                // Make sure nothing is currently selected in the list box.
                StudentListDropDownList.SelectedIndex = -1;
}

[jcarbillon]

Try this

on your desktop create new text file, open it and save As (type: All files) as "conn.udl". then new icon will be created after you save the file.. open it, choose a provider, then type in your server name and enter the log info of your server. then click "test connection" if the connection succeeded, click ok, then re-open the conn.udl as notepad and you will see the working connection string you need.

- well

[mlesniak]

One thing that I noticed was that you need a comma between each of the columns you wish to retrieve from the table, so change

SELECT student_id student_name FROM Student

to

SELECT student_id, student_name FROM Student