<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
 
 <script language=C# runat=server>
 

private void Page_Load(object sender, System.EventArgs e) 
{ 
 if (!Page.IsPostBack) { 
   BindDataList(); 
   BindAreaList(); 
 } 
}
 

private string ConnectionString() 
{ 
 string servername; 
 string username; 
 string password; 
 string databasename; 
 servername = "<deleted>"; 
 username = "<deleted>"; 
 password = "<deleted>"; 
 databasename = "spastoredirectory"; 
 string conString; 
conString = string.Format("server={0};User ID={1};Password={2};Database={3};", servername, username, password, databasename);
 return conString; 
}
 

//--------------------------------------------- 
// name: BindAreaList() 
//--------------------------------------------- 
void BindAreaList() 
{ 
              string strConnect;   
              System.Data.SqlClient.SQLConnection objConnect = new System.Data.SqlClient.SQLConnection(ConnectionString());
              System.Data.SqlClient.SQLCommand objCommand = new System.Data.SqlClient.SQLCommand(); 
              string strSQL; 
              System.Data.SqlClient.SQLDataAdapter dtaArea = new System.Data.SqlClient.SQLDataAdapter(); 
              DataSet dtsArea = new DataSet(); 
              string strChosenState; 
              string FullState;
  
     
              objConnect.Open(); 
              
       
      //Get incoming querystring values 
         strChosenState = Request.QueryString["State"];
     
              //Start SQL statement 
              strSQL = "Select * From Area, State"; 
              strSQL = strSQL + " where Area.State = State.State"; 
              strSQL = strSQL + " and Area.State = '" + strChosenState + "'"; 
              strSQL = strSQL + " Order By AreaName";
   
        
        //Set the Command Object properties 
        objCommand.Connection = objConnect; 
        objCommand.CommandType = CommandType.Text; 
        objCommand.CommandText = strSQL;
        //Create a new DataAdapter object 
        dtaArea.SelectCommand = objCommand; 
        //Get the data from the database and 
        //put it into a DataTable object named dttSpa in the DataSet object 
        dtaArea.Fill(dtsArea, "dttArea"); 
        //Set the DataSource property of the DataGrid 
        dtlArea.DataSource = dtsArea; 
  
  //Set module level variable for page title display 
        FullState = dtsArea.Tables(0).Rows(0).Item("StateName");
          
        //Bind all the controls on the page 
        dtlArea.DataBind(); 
  
        //Display chosen StateName 
        lblState.Text = "Find a Spa Store in  " & FullState;
 
        //Show chosen category in our navigation breadcrumb 
        lblChosenState.Text = FullState; 
 
        //Display category choice in page title 
        litTitle.Text = "Find a Spa Store in " & FullState; 
        
        objCommand.ExecuteNonQuery();
        
        //this is the way to close commands
        objCommand.Connection.Close();
        
        objConnect.Close();

}

string[3] params;
 params[0] = servername;
 params[1] = username;
 params[2] = password;
 params[3] = databasename;  
 servername = ""; 
 username = ""; 
 password = ""; 
 databasename = "";
 string conString;  
 conString = string.Format("server={0};User ID={1};Password={2};Database={3};", params);
 return conString;