using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.OleDb;

public partial class RetrieveReaderColumns : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
       if (!IsPostBack)
       {
          LoadArtists();
          panArtist.Visible = false;
       }
    }
   private OleDbConnection GetConnection()
   {
      string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
      connString += Server.MapPath("~/App_Data/ModernEyeCatalog.mdb") + ";";

      OleDbConnection conn = null;
      try
      {

         conn = new OleDbConnection(connString);
         conn.Open();
      }
      catch (Exception ex)
      {
         labMsg.Text = "Error occurred accessing the database";
         labMsg.Text += "<br/>" + ex.Message;
      }
      return conn;
   }
   private void LoadArtists()
   {
      OleDbConnection conn = null;
      try
      {

         conn = GetConnection();
         if (conn != null)
         {

            string sql = "SELECT ArtistId, LastName+', '+FirstName As Name ";
            sql += " FROM Artists ORDER BY LastName";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();

            lboxArtists.DataSource = reader;
            lboxArtists.DataBind();
            reader.Close();
         }

      }
      catch (Exception ex)
      {
         labMsg.Text = "Error occurred accessing the database";
         labMsg.Text += "<br/>" + ex.Message;
      }
      finally
      {
         if (conn != null)
            conn.Close();
      }
   }
   protected void drpArtists_SelectedIndexChanged(object sender, EventArgs e)
   {
      panArtist.Visible = true;

      // retrieve the artist id selected by user
      string sId = lboxArtists.SelectedValue;

      OleDbConnection conn = null;
      try
      {
         conn = GetConnection();
         if (conn != null)
         {

            string sql = "SELECT ArtistId,FirstName,LastName,Nationality,YearOfBirth,YearOfDeath";
            sql += " FROM Artists WHERE ArtistId=@ID";

            OleDbCommand cmd = new OleDbCommand(sql, conn);

            // set up parameters to query
            OleDbParameter param = new OleDbParameter("@ID", sId);
            // add data parameter
            cmd.Parameters.Add(param);

            OleDbDataReader reader = cmd.ExecuteReader();

            // position the reader to the first record
				if (reader.Read())
				{
					// retrieve the columns from the reader
					// using the database field names
               int id = (int)reader[0];
               txtId.Text = id.ToString();

					if (! reader.IsDBNull(1))
						txtFirst.Text = (string)reader["FirstName"];
					else
						txtFirst.Text = "";
					if (! reader.IsDBNull(2))
						txtLast.Text = (string)reader["LastName"];
					else
						txtLast.Text = "";
               if (!reader.IsDBNull(3))
                  txtNationality.Text = reader.GetString(3);
               else
                  txtNationality.Text = "";
					if (! reader.IsDBNull(4))
					{
						int birth = reader.GetInt16(4);
						txtBirth.Text = birth.ToString();
					}
					else
						txtBirth.Text = "";
					if (! reader.IsDBNull(5))
					{
						int death = reader.GetInt16(5);
						txtDeath.Text = death.ToString();
					}
					else
						txtDeath.Text = "";
				}
            reader.Close();
         }

      }
      catch (Exception ex)
      {
         labMsg.Text = "Error occurred accessing the database";
         labMsg.Text += "<br/>" + ex.Message;
      }
      finally
      {
         if (conn != null)
            conn.Close();
      }
   }
}
