Conditionally Insert (Update) data in DataList using jQuery AJAX in ASP.Net

Last Reply on Oct 11, 2017 04:10 AM By AnandM

Posted on Oct 06, 2017 04:03 AM

I have a datalist that shows images of many colors of bags. The images of these bags are stored in a table called Favorite. When a user logs in the user will see many bags of many colors with their names, descriptions and IDs. Under the bags there will be three button icons of glyphicon example- glyphicon glyphicon-heat-o - Like , glyphicon glyphicon-plus - ReadyToBuy, glyphicon glyphicon-ok- NotReady. Now the user can click any of these buttons to add his favorite bag into table, example if John clicks button glyphicon glyphicon-heart johns username and choice will be inserted into table below, and so on Now the table looks like this

ID        UserName         Color1    Color2    Color3         
--------------------------------------------------------------------------
1          mic22            1                                         
-------------------------------------------------------------------------
2          mary33                       1
-------------------------------------------------------------------------
3         stella22          1
--------------------------------------------------------------------------
4          Brown55                                  1
-------------------------------------------------------------------------

Progress for yes

-------------------------------------

<div class="progress-group">
                        
<span class="progress-text">Visit Premium Page</span>
                     
   <span class="progress-number"><b>480</b>/800</span>
                       
 <div class="progress sm">
                         
 <div class="progress-bar progress-bar-green" style="width: 80%"></div>
             
    </div>
 </div>

progress for No

-------------------------------------

 <div class="progress-group">
                        <span class="progress-text">Visit Premium Page</span>
                        <span class="progress-number"><b>480</b>/800</span>
                        <div class="progress sm">
                          <div class="progress-bar progress-bar-red" style="width: 80%"></div>
                        </div>
                      </div>

 progress html

<asp:DataList ID="DataList1" runat="server">
                    <ItemTemplate>
<asp:LinkButton ID="LinkBtnColor1" runat="server">Select Color1</asp:LinkButton>
<asp:LinkButton ID="LinkBtnColor2" runat="server">Select Color2</asp:LinkButton>
<asp:LinkButton ID="LinkBtnColor3" runat="server">Select Color3</asp:LinkButton>
 
 <a href="#">
                          <h6>
                                     <asp:Label ID="Label62" runat="server" Text="" CssClass="" ForeColor="#CC3300"></asp:Label><i class=""  style="margin-right:2px;  color: #CCCCCC;" ></i><asp:Label ID="Label66" runat="server" Text="Color1" ForeColor="#CC3300"></asp:Label><small class="pull-right">5%</small> </h6><div class="progress xs">
                            <div class="progress-bar progress-bar-red" style="width:5%" role="progressbar" aria-valuenow="20" aria-valuemin="0" aria-valuemax="100">
                              <span class="sr-only">20% Complete</span> </div></div></a>
                                  
                                  
                                  
                                  
                                 <a href="#"><h6>
                                <asp:Label ID="Label63" runat="server" Text="" CssClass="" ForeColor="#FF9900"></asp:Label><i class=""  style="margin-right:2px;  color: #CCCCCC;" ></i><asp:Label ID="Label65" runat="server" Text="Color2" ForeColor="#FF9900"></asp:Label><small class="pull-right">01%</small> </h6><div class="progress xs">
                            <div class="progress-bar progress-bar-yellow" style="width: 01%" role="progressbar" aria-valuenow="50" aria-valuemin="0" aria-valuemax="100">
                              <span class="sr-only">01% Complete</span> </div></div></a>
                                  
                                  
                                 <a href="#"><h6>
 
                                 <asp:Label ID="Label64" runat="server" Text="" CssClass=""></asp:Label><i class=""  style="margin-right:2px;  color: #CCCCCC;" ></i><asp:Label ID="Label92" runat="server" Text="Colo3"></asp:Label><small class="pull-right">70%</small> </h6><div class="progress xs">
                            <div class="progress-bar progress-bar-aqua" style="width: 70%" role="progressbar" aria-valuenow="70" aria-valuemin="0" aria-valuemax="100">
                              <span class="sr-only">70% Complete</span> </div></div></a>
 
 
 
</ItemTemplate>
                </asp:DataList>

please below i have a code i used to do similar thing but this very code is only uses one button to add and delet, can you please use this method for this solution above

<script type="text/javascript">
               $(function () {
                   $('[id*=btnAdd]').click(function () {
                       var obj = {};
                       $('[id*=hfUserName]').val('<%= Session["userName"].ToString() %>');
                   obj.userName = $('[id*=hfUserName]').val();
                   obj.postId = $(this).closest('tr').find('[id*=hfPostId]').val();
                   obj.icon = $(this)[0].className;
                   obj.IDD = $(this).closest('tr').find('[id*=lblId]').html();
                   obj.favouriteId = $(this).closest('tr').find('[id*=ID]').html() != "" ? $(this).closest('tr').find('[id*=ID]').html() : "0";
                   var rowIndex = $(this).closest('tr')[0].rowIndex;
                   $.ajax({
                       type: "POST",
                       url: "Timeline.aspx/Onfavorites",
                       data: JSON.stringify(obj),
                       contentType: "application/json; charset=utf-8",
                       dataType: "json",
                       success: function (response) {
                           var className = response.d[0];
                           var bookCount = response.d[1];
                           $('[id*=GetMergedAll] tr').eq(rowIndex).find('[id*=lblCOLORCOUNT]').html(bookCount);
                           $('[id*=GetMergedAll] tr').eq(rowIndex).find('[id*=btnAdd]')[0].className = className;
                           $('[id*=GetMergedAll] tr').eq(rowIndex).find('[id*=btnAdd2]')[0].className = className;
                       },
                       failure: function (response) {
                           alert(response.d);
                       },
                       error: function (response) {
                           alert(response.responseText);
                       }
                   });
                   return false;
               });
           });
</script>

 

//ADDBUTTON HERE
    [System.Web.Services.WebMethod]
    public static List<string> OnFavoriteBook(string icon, int favouriteId, string userName, string postId)
    {
        string bookIcon = "fa fa-star";
        List<string> values = new List<string>();
        string lblbookCount = string.Empty;
        string message = string.Empty;
        using (SqlConnection con = new SqlConnection(constring4))
        {
            if (icon.ToUpper() == "FA FA-PLUS")
            {
                using (SqlCommand cmd = new SqlCommand("INSERT INTO Dim_favorite values(@PostId,@UserName,@BookCount)", con))
                {
                    cmd.Parameters.AddWithValue("@PostId", postId);
                    // cmd.Parameters.AddWithValue("@UserName", Session["userName"]);
                    cmd.Parameters.AddWithValue("@UserName", HttpContext.Current.Session["userName"]);
                    cmd.Parameters.AddWithValue("@FriendUserName", userName);
                    cmd.Parameters.AddWithValue("@BookCount", 1);
 
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                lblbookCount = GetBookCounts(postId, lblbookCount, con);
                bookIcon = "fa fa-ok";
            }
            else
            {
                using (SqlCommand cmd = new SqlCommand("DELETE FROM Dim_favorite WHERE UserName = @UserName AND PostId = @PostId", con))
                {
                    cmd.Parameters.AddWithValue("@PostId", postId);
                    cmd.Parameters.AddWithValue("@UserName", HttpContext.Current.Session["userName"].ToString());
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                lblbookCount = GetBookCounts(postId, lblbookCount, con);
                bookIcon = "fa fa-plus";
            }
        }
        values.Add(bookIcon);
        values.Add(lblbookCount);
        return values;
    }

 

You are viewing reply posted by: AnandM on Oct 11, 2017 04:10 AM.
Posted on Oct 11, 2017 04:10 AM Modified on on Oct 11, 2017 04:12 AM

Hi micah,

I have gone through your Sql tables but as per your requirement your sql table structure is not proper and hence need to modify it and as well as your Datalist structure is not proper you need to modify it.

I have created a sample which full fill your requirement you need to modify it according to your need.

I have used Users table for login from your previous solution.

Play video from file path stored in database using jQuery jPlayer plugin in ASP.Net

SQL

CREATE TABLE [dbo].[Items](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](50) NULL,
    [Item] [nvarchar](MAX) NULL,
    [Discription] [nvarchar](MAX) NULL,
    [Images] [image] NULL,
 CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-----------------------------------------------------

CREATE TABLE [dbo].[FavoriteTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](50) NULL,
    [Like] [int] NULL,
    [NotReady] [int] NULL,
    [Ready] [int] NULL,
    [ItemName] [nvarchar](255) NULL,
     
 CONSTRAINT [PK_FavoriteTable] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] 
-----------------------------------------------------
 
INSERT INTO Items(UserName,Item,Discription)
VALUES('John1','Alarm Clock','An alarm clock (or sometimes just an alarm) is a clock that is designed to alert an individual or group of individuals at specified time.')

INSERT INTO Items(UserName,Item,Discription)
VALUES('Suzanne@2','DVD Player','A DVD player is a device that plays DVD discs produced under both the DVD-Video and DVD-Audio technical standards, two different and incompatible standards.')

INSERT INTO Items(UserName,Item,Discription)
VALUES('Rob@564','Piano','A piano is a large musical instrument with a row of black and white keys.')

INSERT INTO Items(UserName,Item,Discription)
VALUES('Luke123','Washing Machine','A washing machine (laundry machine, clothes washer, or washer) is a machine used to wash laundry, such as clothing and sheets.')

INSERT INTO Items(UserName,Item,Discription)
VALUES('Lokesh785','Torch','A torch is a stick with combustible material at one end, which is ignited and used as a light source.')

Login.aspx

<div>
    <table>
        <tr>
            <td>
                UserName:
            </td>
            <td>
                <asp:TextBox ID="txtUserName" runat="server" />
            </td>
        </tr>
        <tr>
            <td>
                Password:
            </td>
            <td>
                <asp:TextBox ID="txtPassword" runat="server" />
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:Button ID="Button1" Text="Login" OnClick="ValidateLogin" runat="server" />
            </td>
        </tr>
    </table>
</div>

Login.aspx.cs

protected void ValidateLogin(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        string query = "SELECT UserName FROM Users WHERE UserName = @Username AND Password = @Password";
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            if (!string.IsNullOrEmpty(txtUserName.Text.Trim()) && !string.IsNullOrEmpty(txtPassword.Text.Trim()))
            {
                cmd.Parameters.AddWithValue("@Username", txtUserName.Text.Trim());
                cmd.Parameters.AddWithValue("@Password", txtPassword.Text.Trim());
                con.Open();
                string userName = cmd.ExecuteScalar().ToString();
                Session["UserName"] = userName;
                con.Close();
                if (!string.IsNullOrEmpty(userName))
                {
                    Response.Redirect("Home.aspx");
                }
                else
                {
                    ClientScript.RegisterClientScriptBlock(this.GetType(), "Alert", "alert('Invalid UserName or Password!')", true);
                }
            }
            else
            {
                ClientScript.RegisterClientScriptBlock(this.GetType(), "Alert", "alert('UserName and Password Required!')", true);
            }
        }
    }
}

Home.aspx

<div>
    <asp:DataList ID="GetMergedAll" runat="server" DataKeyName="Id" OnItemDataBound="GetMergedAll_ItemDataBound"
        Width="550px" Font-Bold="True">
        <HeaderTemplate>
            <table>
                <tr>
                    <th>
                        Image
                    </th>
                    <th>
                        Item
                    </th>
                    <th>
                        Description
                    </th>
                    <th>
                    </th>
                    <th>
                    </th>
                    <th>
                    </th>
                </tr>
        </HeaderTemplate>
        <ItemTemplate>
            <tr>
                <td>
                    <asp:Image ID="ImageSHARED" runat="server" class="image" alt='<%#Eval("UserName")%>' />
                </td>
                <td>
                    <asp:Label ID="lblItemName" Text='<%#Eval("Item")%>' runat="server" />
                </td>
                <td>
                    <asp:Label ID="lblDescription" Text='<%#Eval("Discription")%>' runat="server" />
                </td>
                <td>
                    <asp:LinkButton ID="btnLike" CssClass="glyphicon glyphicon-heart" role="saveValue"
                        runat="server" Font-Size="20px" ForeColor="" />
                </td>
                <td>
                    <asp:LinkButton ID="btnNotReady" CssClass="glyphicon glyphicon-gift" role="saveValue"
                        runat="server" Font-Size="20px" ForeColor="" />
                </td>
                <td>
                    <asp:LinkButton ID="btnReady" CssClass="glyphicon glyphicon-ok-circle" role="saveValue"
                        runat="server" Font-Size="20px" ForeColor="" />
                </td>
            </tr>
        </ItemTemplate>
        <FooterTemplate>
            </table>
        </FooterTemplate>
    </asp:DataList>
</div>
<div>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script type="text/javascript" src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
    <script type="text/javascript">
        $(function () {
            $('a[role="saveValue"]').click(function () {
                var obj = {};
                obj.btnId = $(this).attr('id').split('_')[1].split('_')[0];
                obj.itemName = $(this).closest('tr').find('[id*=lblItemName]').html();
                $.ajax({
                    type: "POST",
                    url: "Home.aspx/SaveFavorites",
                    data: JSON.stringify(obj),
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        window.location.reload();
                    },
                    failure: function (response) {
                        alert(response.responseText);
                    },
                    error: function (response) {
                        alert(response.responseText);
                    }
                });
                return false;
            });
        });
    </script>
    <style>
        .Red
        {
            color: Red;
        }
    </style>
</div>

Home.aspx.cs

protected void Page_Load(object sender, EventArgs e)
{        
    if (!IsPostBack)
    {
        BindDataList();
    }
}

protected void GetMergedAll_ItemDataBound(object sender, DataListItemEventArgs e)
{
    if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
    {
        LinkButton lnkLike = (e.Item.FindControl("btnLike") as LinkButton);
        Label lblItemName = (e.Item.FindControl("lblItemName") as Label);
        LinkButton lnkNotReady = (e.Item.FindControl("btnNotReady") as LinkButton);
        LinkButton lnkReady = (e.Item.FindControl("btnReady") as LinkButton);
        HiddenField hfUserName = (e.Item.FindControl("hfUserName") as HiddenField);
        DataTable dt = ChangeGlyphicons(Session["UserName"].ToString(), lblItemName.Text);
        if (dt.Rows.Count > 0)
        {
            if (!string.IsNullOrEmpty(dt.Rows[0]["Like"].ToString()) && dt.Rows[0]["Like"].ToString() == "1")
            {
                lnkLike.CssClass = "glyphicon glyphicon-heart Red";
                lnkNotReady.CssClass = "glyphicon glyphicon-gift";
                lnkReady.CssClass = "glyphicon glyphicon-ok-circle";
            }
            if (!string.IsNullOrEmpty(dt.Rows[0]["NotReady"].ToString()) && dt.Rows[0]["NotReady"].ToString() == "1")
            {
                lnkLike.CssClass = "glyphicon glyphicon-heart";
                lnkNotReady.CssClass = "glyphicon glyphicon-ban-circle";
                lnkReady.CssClass = "glyphicon glyphicon-ok-circle";
            }
            if (!string.IsNullOrEmpty(dt.Rows[0]["Ready"].ToString()) && dt.Rows[0]["Ready"].ToString() == "1")
            {
                lnkLike.CssClass = "glyphicon glyphicon-heart";
                lnkNotReady.CssClass = "glyphicon glyphicon-gift";
                lnkReady.CssClass = "glyphicon glyphicon-ok-sign";
            }
        }
    }
}

public void BindDataList()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        string query = "SELECT * FROM Items";
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);
                GetMergedAll.DataSource = dt;
                GetMergedAll.DataBind();
            }
        }
    }
}

public DataTable ChangeGlyphicons(string userName, string itemName)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        string query = "SELECT * FROM FavoriteTable WHERE UserName = @UserName AND ItemName = @ItemName";
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.AddWithValue("@UserName", userName);
            cmd.Parameters.AddWithValue("@ItemName", itemName);
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }
        }
    }
}

[WebMethod]
public static string SaveFavorites(string btnId, string itemName)
{
    string userName = HttpContext.Current.Session["UserName"].ToString();
    bool valueExists = RowExists(userName, itemName);
    string query = string.Empty;
    if (valueExists)
    {
        if (btnId == "btnLike")
        {
            query = "UPDATE FavoriteTable SET [Like] = 1,NotReady = 0,Ready = 0 WHERE UserName = '" + userName + "' AND ItemName = '" + itemName + "'";
        }
        if (btnId == "btnNotReady")
        {
            query = "UPDATE FavoriteTable SET [Like] = 0,NotReady = 1,Ready = 0 WHERE UserName = '" + userName + "' AND ItemName = '" + itemName + "'";
        }
        if (btnId == "btnReady")
        {
            query = "UPDATE FavoriteTable SET [Like] = 0,NotReady = 0,Ready = 1 WHERE UserName = '" + userName + "' AND ItemName = '" + itemName + "'";
        }
    }
    else
    {
        if (btnId == "btnLike")
        {
            query = "INSERT INTO FavoriteTable VALUES('" + userName + "',1,0,0,'" + itemName + "')";
        }
        if (btnId == "btnNotReady")
        {
            query = "INSERT INTO FavoriteTable VALUES('" + userName + "',0,1,0,'" + itemName + "')";
        }
        if (btnId == "btnReady")
        {
            query = "INSERT INTO FavoriteTable VALUES('" + userName + "',0,0,1,'" + itemName + "')";
        }
    }

    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    return "successfully";
}

public static bool RowExists(string userName, string itemName)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "SELECT * FROM FavoriteTable WHERE UserName = @UserName AND ItemName = @ItemName";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.AddWithValue("@UserName", userName);
            cmd.Parameters.AddWithValue("@ItemName", itemName);
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
    }
}

ScreenShot