how to populate a dropdownlist based on selection of another dropdownlist in a gridview

Question

I have a gridview with it's own sqlDataSource. In the footer row, I have an Insert field to create a new row. However, that footer row is quite complex and I'm trying to figure out how to make it work.

What I would like to see happen, is that a user Selects the OTypeName ddl. Based on that selection, the OSpecies ddl is enabled and populates values based on the OTypeName ddl. The same can be said in relation to OSpecies and OVariety.

The user then inputs their own information into OAge, OYields, OPlantDate, and OPlantFrom.

The rest of the fields in the footer row are labels that will be populated based on the final OVariety selection.

The Insert button then adds all these columns to the gridview.

I only need help with populating one ddl based on the ddl selection of another and how to populate a label based on a ddl selection.

Here's my code in the aspx file:

<%@ Page Title="" Language="C#" MasterPageFile="~/Permaculture.Master" AutoEventWireup="true" CodeBehind="OrchardMainWebForm.aspx.cs" Inherits="PermacultureOrganizer.OrchardMainWebForm" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="Content" runat="server" >
<asp:GridView ID="gvOrchardData" runat="server" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="OUserOrchardID" DataSourceID="dsOrchardDatabase" CellPadding="4" CellSpacing="4" ForeColor="#333333" GridLines="Vertical" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" ShowFooter="True" Font-Bold="False">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:TemplateField HeaderText="OUserOrchardID" InsertVisible="False" SortExpression="OUserOrchardID">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("OUserOrchardID") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("OUserOrchardID") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:LinkButton ID="lbInsert"
runat="server"
OnClick="lbInsert_Click" ForeColor="White">Insert</asp:LinkButton>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="OTypeName" SortExpression="OTypeName">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1"
runat="server"
AutoPostBack="True"
DataSourceID="dsTypeName"
DataTextField="OrchardTypeName"
DataValueField="OrchardTypeID" >
</asp:DropDownList>
<asp:SqlDataSource ID="dsTypeName" runat="server" ConnectionString="<%$ ConnectionStrings:DB_9DE518_PermacultureConnectionString %>" SelectCommand="SELECT * FROM [tblOrchardType]"></asp:SqlDataSource>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("OTypeName") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="ddlTypeName"
runat="server"
AutoPostBack="True"
DataSourceID="dsTypeName"
DataTextField="OrchardTypeName"
DataValueField="OrchardTypeID"
>
</asp:DropDownList>
<asp:SqlDataSource ID="dsTypeName" runat="server" ConnectionString="<%$ ConnectionStrings:DB_9DE518_PermacultureConnectionString %>" SelectCommand="SELECT * FROM [tblOrchardType]"></asp:SqlDataSource>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="OSpecies" SortExpression="OSpecies">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList2"
runat="server"
SelectedValue='<%# Bind("OSpecies") %>'
AutoPostBack="True"
DataSourceID="dsSpecies"
DataTextField="Species"
DataValueField="OrchardTypeID">
<asp:ListItem>Select Species</asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="dsSpecies" runat="server" ConnectionString="<%$ ConnectionStrings:DB_9DE518_PermacultureConnectionString %>" SelectCommand="SELECT [Species], [OrchardTypeID] FROM [tblOrchardItem]"></asp:SqlDataSource>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("OSpecies") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="ddlSpecies"
runat="server"
AutoPostBack="True"
DataSourceID="dsSpecies"
DataTextField="Species"
DataValueField="OrchardTypeID"
>
<asp:ListItem>Select Species</asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="dsSpecies" runat="server" ConnectionString="<%$ ConnectionStrings:DB_9DE518_PermacultureConnectionString %>" SelectCommand="SELECT [Species], [OrchardTypeID] FROM [tblOrchardItem]">
</asp:SqlDataSource>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="OVariety" SortExpression="OVariety">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList3"
runat="server"
AutoPostBack="True"
DataSourceID="dsVariety"
DataTextField="Variety"
DataValueField="OrchardTypeID">
</asp:DropDownList>
<asp:SqlDataSource ID="dsVariety" runat="server" ConnectionString="<%$ ConnectionStrings:DB_9DE518_PermacultureConnectionString %>" SelectCommand="SELECT [Variety], [OrchardTypeID] FROM [tblOrchardItem]"></asp:SqlDataSource>
</EditItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="ddlVariety"
runat="server"
AutoPostBack="True"
DataSourceID="dsVariety"
DataTextField="Variety"
DataValueField="OrchardTypeID"
>
</asp:DropDownList>
<asp:SqlDataSource ID="dsVariety" runat="server" ConnectionString="<%$ ConnectionStrings:DB_9DE518_PermacultureConnectionString %>" SelectCommand="SELECT [Variety], [OrchardTypeID] FROM [tblOrchardItem]"></asp:SqlDataSource>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("OVariety") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="OAge" SortExpression="OAge">
<EditItemTemplate>
<asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("OAge") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtBxAge" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("OAge") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="OYields" SortExpression="OYields">
<EditItemTemplate>
<asp:TextBox ID="TextBox5" runat="server" Text='<%# Bind("OYields") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtBxYields" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%# Bind("OYields") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="OPlantDate" SortExpression="OPlantDate">
<EditItemTemplate>
<asp:TextBox ID="TextBox6" runat="server" Text='<%# Bind("OPlantDate") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtBxPlantDate" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label7" runat="server" Text='<%# Bind("OPlantDate") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="OPlantFrom" SortExpression="OPlantFrom">
<EditItemTemplate>
<asp:TextBox ID="TextBox7" runat="server" Text='<%# Bind("OPlantFrom") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtBxPlantFrom" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label8" runat="server" Text='<%# Bind("OPlantFrom") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="OTreeSpacing" SortExpression="OTreeSpacing">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("OTreeSpacing") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTreeSpacing" runat="server"></asp:Label>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label9" runat="server" Text='<%# Bind("OTreeSpacing") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="OFertilizingTimes" SortExpression="OFertilizingTimes">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("OFertilizingTimes") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblFertilizingTimes" runat="server"></asp:Label>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label10" runat="server" Text='<%# Bind("OFertilizingTimes") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="OWateringNeeds" SortExpression="OWateringNeeds">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("OWateringNeeds") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblWateringNeeds" runat="server"></asp:Label>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label11" runat="server" Text='<%# Bind("OWateringNeeds") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="OPollination" SortExpression="OPollination">
<EditItemTemplate>
<asp:TextBox ID="TextBox8" runat="server" Text='<%# Bind("OPollination") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblPollination" runat="server"></asp:Label>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label12" runat="server" Text='<%# Bind("OPollination") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="OFertilizer" SortExpression="OFertilizer">
<EditItemTemplate>
<asp:TextBox ID="TextBox9" runat="server" Text='<%# Bind("OFertilizer") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblFertilizer" runat="server"></asp:Label>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label13" runat="server" Text='<%# Bind("OFertilizer") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="OPesticide" SortExpression="OPesticide">
<EditItemTemplate>
<asp:TextBox ID="TextBox10" runat="server" Text='<%# Bind("OPesticide") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblPesticide" runat="server"></asp:Label>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label14" runat="server" Text='<%# Bind("OPesticide") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<EditRowStyle BackColor="#7C6F57" />
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="Black" />
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#E3EAEB" />
<SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F8FAFA" />
<SortedAscendingHeaderStyle BackColor="#246B61" />
<SortedDescendingCellStyle BackColor="#D4DFE1" />
<SortedDescendingHeaderStyle BackColor="#15524A" />
</asp:GridView>
<asp:SqlDataSource ID="dsOrchardDatabase" runat="server"
ConnectionString="<%$ ConnectionStrings:DB_9DE518_PermacultureConnectionString %>"
SelectCommand="SELECT * FROM [tblUserOrchard]" DeleteCommand="DELETE FROM [tblUserOrchard] WHERE [OUserOrchardID] = @OUserOrchardID" InsertCommand="INSERT INTO [tblUserOrchard] ([OTypeName], [OSpecies], [OVariety], [OAge], [OYields], [OPlantDate], [OPlantFrom], [OTreeSpacing], [OFertilizingTimes], [OPruningTimes], [OWateringNeeds], [OPollination], [OFertilizer], [OPesticide]) VALUES (@OTypeName, @OSpecies, @OVariety, @OAge, @OYields, @OPlantDate, @OPlantFrom, @OTreeSpacing, @OFertilizingTimes, @OPruningTimes, @OWateringNeeds, @OPollination, @OFertilizer, @OPesticide)" UpdateCommand="UPDATE [tblUserOrchard] SET [OTypeName] = @OTypeName, [OSpecies] = @OSpecies, [OVariety] = @OVariety, [OAge] = @OAge, [OYields] = @OYields, [OPlantDate] = @OPlantDate, [OPlantFrom] = @OPlantFrom, [OTreeSpacing] = @OTreeSpacing, [OFertilizingTimes] = @OFertilizingTimes, [OPruningTimes] = @OPruningTimes, [OWateringNeeds] = @OWateringNeeds, [OPollination] = @OPollination, [OFertilizer] = @OFertilizer, [OPesticide] = @OPesticide WHERE [OUserOrchardID] = @OUserOrchardID">
<DeleteParameters>
<asp:Parameter Name="OUserOrchardID" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="OTypeName" Type="String" />
<asp:Parameter Name="OSpecies" Type="String" />
<asp:Parameter Name="OVariety" Type="String" />
<asp:Parameter Name="OAge" Type="Int32" />
<asp:Parameter Name="OYields" Type="String" />
<asp:Parameter Name="OPlantDate" Type="String" />
<asp:Parameter Name="OPlantFrom" Type="String" />
<asp:Parameter Name="OTreeSpacing" Type="String" />
<asp:Parameter Name="OFertilizingTimes" Type="String" />
<asp:Parameter Name="OPruningTimes" Type="String" />
<asp:Parameter Name="OWateringNeeds" Type="String" />
<asp:Parameter Name="OPollination" Type="String" />
<asp:Parameter Name="OFertilizer" Type="String" />
<asp:Parameter Name="OPesticide" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="OTypeName" Type="String" />
<asp:Parameter Name="OSpecies" Type="String" />
<asp:Parameter Name="OVariety" Type="String" />
<asp:Parameter Name="OAge" Type="Int32" />
<asp:Parameter Name="OYields" Type="String" />
<asp:Parameter Name="OPlantDate" Type="String" />
<asp:Parameter Name="OPlantFrom" Type="String" />
<asp:Parameter Name="OTreeSpacing" Type="String" />
<asp:Parameter Name="OFertilizingTimes" Type="String" />
<asp:Parameter Name="OPruningTimes" Type="String" />
<asp:Parameter Name="OWateringNeeds" Type="String" />
<asp:Parameter Name="OPollination" Type="String" />
<asp:Parameter Name="OFertilizer" Type="String" />
<asp:Parameter Name="OPesticide" Type="String" />
<asp:Parameter Name="OUserOrchardID" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="dsTypeName" runat="server" ConnectionString="<%$ ConnectionStrings:DB_9DE518_PermacultureConnectionString %>" SelectCommand="SELECT * FROM [tblOrchardType]"></asp:SqlDataSource>
</asp:Content>

I'm not sure what to put in the aspx.cs file but here's what I have so far concerning the Insert button:

public void lbInsert_Click(object sender, EventArgs e)
{
dsOrchardDatabase.InsertParameters["OTypeName"].DefaultValue =
((DropDownList)gvOrchardData.FooterRow.FindControl("ddlTypeName")).SelectedItem.ToString();
dsOrchardDatabase.InsertParameters["OSpecies"].DefaultValue =
((DropDownList)gvOrchardData.FooterRow.FindControl("ddlSpecies")).SelectedItem.ToString();
dsOrchardDatabase.InsertParameters["OVariety"].DefaultValue =
((DropDownList)gvOrchardData.FooterRow.FindControl("ddlVariety")).SelectedItem.ToString();
dsOrchardDatabase.InsertParameters["OAge"].DefaultValue =
((TextBox)gvOrchardData.FooterRow.FindControl("txtBxAge")).Text;
dsOrchardDatabase.InsertParameters["OYields"].DefaultValue =
((TextBox)gvOrchardData.FooterRow.FindControl("txtBxYields")).Text;
dsOrchardDatabase.InsertParameters["OPlantDate"].DefaultValue =
((TextBox)gvOrchardData.FooterRow.FindControl("txtBxPlantDate")).Text;
dsOrchardDatabase.InsertParameters["OPlantFrom"].DefaultValue =
((TextBox)gvOrchardData.FooterRow.FindControl("txtBxPlantFrom")).Text;
dsOrchardDatabase.InsertParameters["OTreeSpacing"].DefaultValue =
((Label)gvOrchardData.FooterRow.FindControl("lblTreeSpacing")).Text;
dsOrchardDatabase.InsertParameters["OFertilizingTimes"].DefaultValue =
((Label)gvOrchardData.FooterRow.FindControl("lblFertilizingTimes")).Text;
dsOrchardDatabase.InsertParameters["OWateringNeeds"].DefaultValue =
((Label)gvOrchardData.FooterRow.FindControl("lblWateringNeeds")).Text;
dsOrchardDatabase.InsertParameters["OPollination"].DefaultValue =
((Label)gvOrchardData.FooterRow.FindControl("lblPollination")).Text;
dsOrchardDatabase.InsertParameters["OFertilizer"].DefaultValue =
((Label)gvOrchardData.FooterRow.FindControl("lblFertilizer")).Text;
dsOrchardDatabase.InsertParameters["OPesticide"].DefaultValue =
((Label)gvOrchardData.FooterRow.FindControl("lblPesticide")).Text;
dsOrchardDatabase.Insert();
}

Any help would be much appreciated!

Example of Orchard Gridview

Answer 1

Just to give you an idea, you should handle this on your code-behind (I noticed that the AutoPostBack property is set to true)..

Let's say on ddl1 value change event or similar:

var ddl1Value = ddl1.SelectedValue;
ddl2.ItemSource = ddl2DS.Where(item => item.Field1 = ddl1Value);
ddl3.ItemSource = ddl3DS.Where(item => item.Field1 = ddl1Value);

Basically you will just filter the item source of the other drop down lists as the value of your first drop down changes.

If you want you can also use javascript to perform this exact same concept to avoid postbacks.

give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime

Answer 2

Here is the solution I found to work:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;

namespace PermacultureOrganizer
{
    public partial class OrchardMainWebForm : System.Web.UI.Page
    {
        //Make the SQL connections
        SqlConnection con2 = new SqlConnection(@"Data Source=SQL5018.SmarterASP.net;Initial Catalog=DB_9DE518_Permaculture;Persist Security Info=True;User ID=DB_9DE518_Permaculture_admin;Password=*****");
        public string con = WebConfigurationManager.ConnectionStrings["PermaCultureConnection"].ConnectionString;

    public void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //Create EventHandlers
            LinkButton btnInsert = new LinkButton();
            btnInsert.Click += new EventHandler(btnInsert_Click);

            DropDownList ddlTypeName = new DropDownList();
            ddlTypeName.SelectedIndexChanged += new EventHandler(ddlTypeName_SelectedIndexChanged);

            DropDownList ddlSpecies = new DropDownList();
            ddlSpecies.SelectedIndexChanged += new EventHandler(ddlSpecies_SelectedIndexChanged);

            DropDownList ddlVariety = new DropDownList();
            ddlVariety.SelectedIndexChanged += new EventHandler(ddlVariety_SelectedIndexChanged);
        }
    }

    protected void btnViewData_Click(object sender, EventArgs e)
    {
        //Go to OrchardViewer Page
        Response.Redirect("OrchardViewer.aspx");
    }

    protected void btnSearchData_Click(object sender, EventArgs e)
    {
        //Go to OrchardSearch Page
        Response.Redirect("OrchardSearch.aspx");
    }

    protected void ddlTypeName_SelectedIndexChanged(object sender, EventArgs e)
    {
        //Populate ddlSpecies based on TypeName selection
        ddlSpecies.DataSource = RetrieveSpecies(ddlTypeName.SelectedValue);
        ddlSpecies.DataBind();
        ddlSpecies.Items.Insert(0, "Select Species");
    }

    private DataTable RetrieveSpecies(string OrchardTypeID)
    {
        //Use OrchardTypeID from TypeName selection to filter Species
        string connString = ConfigurationManager.ConnectionStrings["PermaCultureConnection"].ConnectionString;
        string sql = @"SELECT OrchardItemID, OSpecies FROM tblOrchardItem WHERE OrchardTypeID = " + OrchardTypeID;
        DataTable dtSpecies = new DataTable();

        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Fill the result set
                adapter.Fill(dtSpecies);
            }
        }
        return dtSpecies;
    }

    protected void ddlSpecies_SelectedIndexChanged(object sender, EventArgs e)
    {
        //Populate ddlVariety based on Species selection
        ddlVariety.DataSource = RetrieveVariety(ddlSpecies.SelectedValue);
        ddlVariety.DataBind();
        ddlVariety.Items.Insert(0, "Select Variety");
    }

    private DataTable RetrieveVariety(string OrchardItemID)
    {
        //Use OrchardTypeID from Species selection to filter Variety
        string connString = ConfigurationManager.ConnectionStrings["PermaCultureConnection"].ConnectionString;
        string sql = @"SELECT OrchardItemID, OVariety FROM tblOrchardItem WHERE OrchardItemID = " + OrchardItemID;
        DataTable dtVariety = new DataTable();

        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Fill the result set
                adapter.Fill(dtVariety);
            }
        }
        return dtVariety;
    }

    protected void ddlVariety_SelectedIndexChanged(object sender, EventArgs e)
    {
        //Gather additional data based on Variety selection
        int OrchardItemID = Convert.ToInt32(ddlVariety.SelectedIndex);
        BindOrchardGrid(OrchardItemID);

        //Display picture next based on Variety selection
        string Variety = ddlVariety.SelectedItem.ToString();
        switch (Variety)
        {
            case "Bladen":
                imageTree.ImageUrl = "Images/blueberry-vine 300x200.jpg";
                break;
            case "Bartlett":
                imageTree.ImageUrl = "Images/Pear-Tree 300x200.jpg";
                break;
            case "Babcook":
                imageTree.ImageUrl = "Images/peach-tree-w-fruit 300x200.jpg";
                break;
            case "Abbuoto":
                imageTree.ImageUrl = "Images/grape-vine 300x200.jpg";
                break;
            case "Carya":
                imageTree.ImageUrl = "Images/pecans 300x200.jpg";
                break;
        }
    }

    private void BindOrchardGrid(int OrchardItemID)
    {
        DataSet dtLabels;
        SqlDataAdapter sda = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand("SELECT * FROM tblOrchardItem WHERE OrchardItemID = " + OrchardItemID, con2);
        sda.SelectCommand = cmd;
        dtLabels = new DataSet("OrchardItem");
        sda.Fill(dtLabels, "OrchardItem");

        foreach (DataRow row in dtLabels.Tables["OrchardItem"].Rows)
        {
            //Gather other pertinent data based on Variety selected
            lblTreeSpacing.Text = Convert.ToString(row["OTreeSpacing"]);
            lblFertilizingTimes.Text = Convert.ToString(row["OFertilizingTimes"]);
            lblPruningTimes.Text = Convert.ToString(row["OPruningTimes"]);
            lblWateringNeeds.Text = Convert.ToString(row["OWateringNeeds"]);
            lblPollination.Text = Convert.ToString(row["OPollination"]);

            int FertilizerID = Convert.ToInt32(row["FertilizerID"]);
            int PesticideID = Convert.ToInt32(row["PesticideID"]);

            DataSet dtFert;
            SqlDataAdapter sdaF = new SqlDataAdapter();
            SqlCommand cmdF = new SqlCommand("SELECT * FROM tblFertilizer WHERE FertilizerID = " + FertilizerID, con2);
            sdaF.SelectCommand = cmdF;
            dtFert = new DataSet("Fert");
            sdaF.Fill(dtFert, "Fert");

            foreach (DataRow rowF in dtFert.Tables["Fert"].Rows)
            {
                lblFertilizer.Text = Convert.ToString(rowF["FertilizerName"]);
            }

            DataSet dtPest;
            SqlDataAdapter sdaP = new SqlDataAdapter();
            SqlCommand cmdP = new SqlCommand("SELECT * FROM tblPesticide WHERE PesticideID = " + PesticideID, con2);
            sdaP.SelectCommand = cmdP;
            dtPest = new DataSet("Pest");
            sdaP.Fill(dtPest, "Pest");

            foreach (DataRow rowP in dtPest.Tables["Pest"].Rows)
            {
                lblPesticide.Text = Convert.ToString(rowP["PesticideName"]);
            }
        }
    }

    public void btnInsert_Click(object sender, EventArgs e)
    {
        //After the user inputs data, insert it into the database and then switch to OrchardViewer page
        CreateOrchard(con);
        Response.Redirect("OrchardViewer.aspx");
    }

    public DataTable CreateOrchard(string con)
    {
        DataTable dtOrchard = new DataTable();
        using (SqlConnection Connection = new SqlConnection(con))
        {
            SqlCommand sqlcmd = new SqlCommand();
            //associate command object with connection
            sqlcmd.Connection = Connection;
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.CommandText = "spInsertOrchardData";

            //Gather user input for insert into database
            sqlcmd.Parameters.Add("@UserOrchardID", SqlDbType.VarChar, 1000).Value = "";
            sqlcmd.Parameters.Add("@TypeName", SqlDbType.VarChar, 1000).Value = Convert.ToString(ddlTypeName.SelectedItem);
            sqlcmd.Parameters.Add("@Species", SqlDbType.VarChar, 1000).Value = Convert.ToString(ddlSpecies.SelectedItem);
            sqlcmd.Parameters.Add("@Variety", SqlDbType.VarChar, 1000).Value = Convert.ToString(ddlVariety.SelectedItem);
            sqlcmd.Parameters.Add("@Age", SqlDbType.Int, 1000).Value = Convert.ToInt32(txtBxAge.Text);
            sqlcmd.Parameters.Add("@Yields", SqlDbType.VarChar, 1000).Value = Convert.ToInt32(txtBxYields.Text);
            sqlcmd.Parameters.Add("@PlantDate", SqlDbType.VarChar, 1000).Value = Convert.ToString(txtBxPlantDate.Text);
            sqlcmd.Parameters.Add("@PlantFrom", SqlDbType.VarChar, 1000).Value = Convert.ToString(txtBxPlantFrom.Text);
            sqlcmd.Parameters.Add("@TreeSpacing", SqlDbType.VarChar, 1000).Value = Convert.ToString(lblTreeSpacing.Text);
            sqlcmd.Parameters.Add("@FertilizingTimes", SqlDbType.VarChar, 1000).Value = Convert.ToString(lblFertilizingTimes.Text);
            sqlcmd.Parameters.Add("@PruningTimes", SqlDbType.VarChar, 1000).Value = Convert.ToString(lblPruningTimes.Text);
            sqlcmd.Parameters.Add("@WateringNeeds", SqlDbType.VarChar, 1000).Value = Convert.ToString(lblWateringNeeds.Text);
            sqlcmd.Parameters.Add("@Pollination", SqlDbType.VarChar, 1000).Value = Convert.ToString(lblPollination.Text);
            sqlcmd.Parameters.Add("@Fertilizer", SqlDbType.VarChar, 1000).Value = Convert.ToString(lblFertilizer.Text);
            sqlcmd.Parameters.Add("@Pesticide", SqlDbType.VarChar, 1000).Value = Convert.ToString(lblPesticide.Text);

            using (SqlDataAdapter sda = new SqlDataAdapter(sqlcmd))
            {
                DataSet dataset = new DataSet();
                sda.Fill(dataset);
            }
        }
        return dtOrchard;
    }
}

}