SqlDataSource and Detailsview

(ASP.NET)


Tools Used: Visual Studio 2013 Express, SQL Server 2008/2012 Express and .Netframework 4.5

The detailsview control is linked with sql datasource control. The detailsview control use sql datasource control to fire the sql query against the database and get the resultset.

The sql datasource control  select parameters is pointing to the selected value of the dropdown. So, Whenever you select the new item in the dropdown, the selected item details will be  bind to the detailsview.

The sql datasource control has one insert parameter, which will have the identity column value of newly inserted item. 

In the oninserted event of the sql datasource control, I rebind the dropdown. I set the 
selected item of dropdown to the newly added item and rebdinding the detailsview.

.aspx page
 
<div style="margin-left:30%;">
        <asp:Label ID="lbl_header" runat="server" ForeColor="Green" Font-Size="Large" Text="Sqldatasource and Detailsview example" />
        <br /><br />

        <asp:DropDownList AutoPostBack="true" Font-Size="Large" OnSelectedIndexChanged="drp_customerslist_SelectedIndexChanged" ID="drp_customerslist" runat="server">

        </asp:DropDownList>
        <br />
        <br />

        <asp:DetailsView DataKeyNames="Id" AllowPaging="True"  DataSourceID="sqlds_customers" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" AutoGenerateInsertButton="True" 
            ID="dtvw_customers" runat="server" Height="50px" Width="125px" CellPadding="4" ForeColor="#333333" GridLines="None">
            <AlternatingRowStyle BackColor="White" />
            <CommandRowStyle BackColor="#FFFFC0" Font-Bold="True" />
            <FieldHeaderStyle BackColor="#FFFF99" Font-Bold="True" />
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
            <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
        </asp:DetailsView>

          <asp:SqlDataSource OnDeleted="sqlds_customers_Deleted"  OnInserted="sqlds_customers_Inserted" OnUpdated="sqlds_customers_Updated"   
            InsertCommand="INSERT INTO Customers(CustomerID,CompanyName,ContactName,ContactTitle,City,Country,PostalCode) VALUES(@CustomerID,@CompanyName,@ContactName,
              @ContactTitle,@City,@Country,@PostalCode); SELECT @Id = SCOPE_IDENTITY()"
            SelectCommand="SELECT Id,CustomerId,CompanyName,ContactName,ContactTitle,City,Country,PostalCode FROM Customers WHERE Id = @Id" 
            UpdateCommand="UPDATE Customers SET CustomerId = @CustomerId,CompanyName = @CompanyName ,ContactName = @ContactName,ContactTitle = @ContactTitle,City = @City,Country = @Country,PostalCode = @PostalCode WHERE Id = @Id"  
            DeleteCommand="DELETE FROM Customers WHERE Id = @Id"
            ConnectionString="<%$ ConnectionStrings:CustomersConn %>" ID="sqlds_customers" runat="server">
              <SelectParameters>
                  <asp:ControlParameter ControlID="drp_customerslist" Name="Id"  DefaultValue="0" PropertyName="SelectedValue" />
              </SelectParameters>
              <InsertParameters>
                   <asp:Parameter Name="Id" Direction="Output" Type="Int32" DefaultValue="0" />
              </InsertParameters>
        </asp:SqlDataSource>
    </div>
Codebehind/.cs file 

protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                if(!Page.IsPostBack)
                {
                    BindDropDown();
                }
            }
            catch(Exception)
            { }
        }

        protected void drp_customerslist_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                dtvw_customers.DataBind();
            }
            catch(Exception)
            { }
        }


        private void BindDropDown()
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["CustomersConn"].ToString()))
                {
                    conn.Open();

                    string sqlQuery = "SELECT Id FROM Customers ORDER BY Id ASC";

                    SqlCommand cmd = new SqlCommand(sqlQuery, conn);

                    drp_customerslist.DataSource = cmd.ExecuteReader();
                    drp_customerslist.DataTextField = "Id";
                    drp_customerslist.DataValueField = "Id";
                    drp_customerslist.DataBind();
                }
            }
            catch(Exception)
            { }
        }

     

        protected void sqlds_customers_Inserted(object sender, SqlDataSourceStatusEventArgs e)
        {
            try
            {

                BindDropDown();
                drp_customerslist.SelectedValue = e.Command.Parameters["@Id"].Value.ToString();
                dtvw_customers.DataBind();
            }
            catch (Exception)
            { }
        }

        protected void sqlds_customers_Updated(object sender, SqlDataSourceStatusEventArgs e)
        {
            try
            {
                //string value = e.Command.Parameters["@Id"].Value.ToString();
               // drp_customerslist.SelectedValue = value;
                //dtvw_customers.DataBind();
            }
            catch(Exception)
            { }
        }

        protected void sqlds_customers_Deleted(object sender, SqlDataSourceStatusEventArgs e)
        {
            try
            {
                BindDropDown();
            }
            catch(Exception)
            { }
        }


Ratings


Average Rating: 0.00 by 0 users
Giri Prasad
5/19/2014
5/19/2014
Download PDF
Download Project

Comments