locked
help with dropdown list in record update page RRS feed

  • Question

  • User-897641766 posted

    Hi, I have a record update page which seems to populate all the fields, however the dropdown lists will only list the data from that particular record, it is not listing the other School Names and the Student Names. The two dropdown lists are cascaded i.e. selecting the School Name ddl should list only the Students that are from that school in the Student ddl.

    Any help on this will be much appreciated, thank you.

    Gridview code on tours.aspx

                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowPaging="true" OnPageIndexChanging="GridView1_PageIndexChanging" OnEditCommand="EditAddress" PageSize="5">
                    <Columns>
                        <asp:HyperLinkField Text="Update" DataNavigateUrlFields="TourId" DataNavigateUrlFormatString="~/abcrud1/tours_update.aspx?TourId={0}" />
                        <asp:BoundField DataField="TourId" HeaderText="TourId" HtmlEncode="false" />
                        <asp:BoundField DataField="VisitorName" HeaderText="VisitorName" HtmlEncode="false" />
                        <asp:BoundField DataField="VisitorSchoolId" HeaderText="VisitorSchoolId" HtmlEncode="false" />
                        <asp:BoundField DataField="SchName" HeaderText="SchName" HtmlEncode="false" />
                        <asp:BoundField DataField="TourDate" HeaderText="TourDate" HtmlEncode="false" />
                        <asp:BoundField DataField="StudentId" HeaderText="StudentId" HtmlEncode="false" />
                        <asp:BoundField DataField="FirstName" HeaderText="FirstName" HtmlEncode="false" />
                        <asp:BoundField DataField="LastName" HeaderText="LastName" HtmlEncode="false" />
                    </Columns>
                </asp:GridView>

    tour_update.aspx

        <form id="form1" runat="server">
            <div>
                <table style="width: 50%;">
                    <tr>
                        <td style="width: 50%;">Tour Id:</td>
                        <td style="width: 50%;">
                            <asp:TextBox ID="txttourid" runat="server"></asp:TextBox></td>
                    </tr>
                    <tr>
                        <td style="width: 50%;">Visitor Name:</td>
                        <td style="width: 50%;">
                            <asp:TextBox ID="txtvisname" runat="server"></asp:TextBox></td>
                    </tr>
                    <tr>
                        <td style="width: 50%;">Visitor School:</td>
                        <td style="width: 50%;">
                            <asp:DropDownList ID="ddlvisschool" runat="server" AutoPostBack="true"></asp:DropDownList></td>
                    </tr>
                    <tr>
                        <td style="width: 50%;">Tour Date:</td>
                        <td style="width: 50%;">
                            <asp:TextBox ID="txttourdate" runat="server"></asp:TextBox></td>
                    </tr>
                    <tr>
                        <td style="width: 50%;">Student:</td>
                        <td style="width: 50%;">
                            <asp:DropDownList ID="ddlstudent" runat="server"></asp:DropDownList></td>
                    </tr>
                    <tr>
                        <td><asp:Button ID="btnUpdate" Text="Update" runat="server"  OnClick="TourUpdate_click" /></td>
                        <td></td>
                    </tr>
                </table>
            </div>
        </form>

    tour_update.aspx.cs

    {
        public partial class tours_update : System.Web.UI.Page
        {
            string constr = ConfigurationManager.ConnectionStrings["SQLConnectionString2"].ConnectionString;
            protected void Page_Load(object sender, EventArgs e)
            {
                {
                    if (!IsPostBack)
                    {
                        this.GetTour();
                    }
                }
            }
    
            protected void TourUpdate_click(object sender, EventArgs e)
            {
                this.TourUpdate();
            }
    
            private int id
            {
                get
                {
                    return !string.IsNullOrEmpty(Request.QueryString["TourId"]) ? int.Parse(Request.QueryString["TourId"]) : 0;
                }
            }
    
            private void TourUpdate()
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand("UPDATE tblCRUD1_Tour SET VisitorName=@VisitorName, VisitorSchoolId=@VisitorSchoolId, TourDate=@TourDate, StudentId=@StudentId WHERE TourId=@TourId", con))
                    {
                        cmd.Parameters.AddWithValue("@TourId", id);
                        cmd.Parameters.AddWithValue("@VisitorName", txtvisname.Text);
                        cmd.Parameters.AddWithValue("@VisitorSchoolId", ddlvisschool.SelectedItem.Value);
                        cmd.Parameters.AddWithValue("@TourDate", txttourdate.Text);
                        cmd.Parameters.AddWithValue("@StudentId", ddlstudent.SelectedItem.Value);
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                        Response.Redirect("~/abcrud1/tours.aspx");
                    }
                }
            }
    
            private void GetTour()
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand(@"SELECT tblCRUD1_Tour.TourId, tblCRUD1_Tour.VisitorName, tblCRUD1_Tour.VisitorSchoolId, tblCRUD1_School.SchName, tblCRUD1_Tour.TourDate, tblCRUD1_Tour.StudentId, tblCRUD1_Student.FirstName, tblCRUD1_Student.LastName 
                                                            FROM tblCRUD1_Tour
                                                            INNER JOIN tblCRUD1_Student ON tblCRUD1_Tour.StudentId = tblCRUD1_Student.StudentId
                                                            INNER JOIN tblCRUD1_School ON tblCRUD1_Student.LastSchoolId = tblCRUD1_School.SchId
                                                            WHERE tblCRUD1_Tour.TourId=@TourId", con))
                    {
                        cmd.Parameters.AddWithValue("@TourId", id);
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            DataTable dt = new DataTable();
                            da.Fill(dt);
                            foreach (DataRow dr in dt.Rows)
                            {
    
                                this.txttourid.Text = dr["TourId"].ToString();
                                this.txtvisname.Text = dr["VisitorName"].ToString();
                                this.txttourdate.Text = dr["TourDate"].ToString();
    
                                ddlvisschool.DataSource = dt;
                                ddlvisschool.DataTextField = "SchName";
                                ddlvisschool.DataValueField = "VisitorSchoolId";
                                ddlvisschool.DataBind();
    
                                ddlstudent.DataSource = dt;
                                ddlstudent.DataTextField = "LastName";
                                ddlstudent.DataValueField = "StudentId";
                                ddlstudent.DataBind();
                            }
                        }
                    }
                }
            }
        }
    }

    tours_add.aspx.cs

    {
        public partial class tours_add : System.Web.UI.Page
        {
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnectionString2"].ConnectionString);
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    Bindddlvisschool();
                    ddlstudent.Items.Insert(0, " Select Pupil ");
                }
            }
    
            private void Bindddlvisschool()
            {
                con.Open();
                string str = @"Select * FROM tblCRUD1_School ORDER BY SchName ASC";
                SqlCommand cmd = new SqlCommand(str, con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                ddlvisschool.DataSource = dt;
                ddlvisschool.DataTextField = "SchName";
                ddlvisschool.DataValueField = "SchId";
                ddlvisschool.DataBind();
                ddlvisschool.Items.Insert(0, new ListItem(" Choose Last School ", "0"));
                ddlvisschool.SelectedIndex = 0;
                con.Close();
            }
    
            protected void TourAdd_click(object sender, EventArgs e)
            {
                con.Open();
                string str = @"INSERT INTO tblCRUD1_Tour (VisitorName,VisitorSchoolId,TourDate,StudentId) VALUES (@VisitorName,@VisitorSchoolId,@TourDate,@StudentId)";
                SqlCommand cmd = new SqlCommand(str, con);
                cmd.Parameters.AddWithValue("@VisitorName", txtvisname.Text);
                cmd.Parameters.AddWithValue("@VisitorSchoolId", ddlvisschool.Text);
                cmd.Parameters.AddWithValue("@TourDate", txttourdate.Text);
                cmd.Parameters.AddWithValue("@StudentId", ddlstudent.Text);
                cmd.ExecuteNonQuery();
                lblmessage1.Text = "Tour added successfully";
                con.Close();
                Response.AddHeader("REFRESH", "3;URL=tours.aspx");
            }
    
            protected void ddlvisschool_SelectedIndexChanged(object sender, EventArgs e)
            {
                string get_SchId;
                string get_SchName;
                get_SchId = ddlvisschool.SelectedValue.ToString();
                get_SchName = ddlvisschool.SelectedItem.Text;
    
                if (get_SchId != "0")
                {
                    con.Open();
                    SqlDataAdapter da;
                    DataSet ds = new DataSet();
                    string query;
    
                    query = "SELECT tblCRUD1_Tour.TourId, tblCRUD1_Tour.VisitorName, tblCRUD1_Tour.VisitorSchoolId, tblCRUD1_School.SchName, tblCRUD1_Tour.TourDate, tblCRUD1_Tour.StudentId, tblCRUD1_Student.FirstName, tblCRUD1_Student.LastName "
                            + "FROM tblCRUD1_Tour "
                            + "INNER JOIN tblCRUD1_Student ON tblCRUD1_Tour.StudentId = tblCRUD1_Student.StudentId "
                            + "INNER JOIN tblCRUD1_School ON tblCRUD1_Student.LastSchoolId = tblCRUD1_School.SchId "
                            + "WHERE tblCRUD1_Tour.VisitorSchoolId='" + get_SchId.ToString() + "' ORDER BY tblCRUD1_Student.LastName ASC";
    
                    da = new SqlDataAdapter(query, con);
                    da.Fill(ds);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        ddlstudent.DataSource = ds;
                        ddlstudent.DataTextField = "LastName";
                        ddlstudent.DataValueField = "StudentId";
                        ddlstudent.DataBind();
                        //ddlstudent.Items.Insert(0, new ListItem(get_SchName.ToString(), "0"));
                        ddlstudent.SelectedIndex = 0;
                    }
                    else
                    {
                        ddlstudent.Items.Insert(0, "No Student");
                        ddlstudent.DataBind();
                    }
                }
            }
        }
    }

    Wednesday, March 28, 2018 8:24 PM

All replies

  • User-1716253493 posted

    Your requirement is clear

    In not ispostback page_load, get all available schools to populate ddl1 items, select one that match to related current record, populate ddl2 students, select one that match current record

    Thursday, March 29, 2018 12:46 AM
  • User-897641766 posted

    Hi, Would you mind sharing the code please, I am a learner on this and trying to get hands on this college project, I learned few bits here n there from YouTube.

    Thursday, March 29, 2018 10:21 AM