why update operation can perform to all my records?

Question

User-1026236167 posted

hello

in my code there is single button submit i can perform insertion and updation in updation when i update record number of records updated but i require single record update

(without passing as where tableid) 

css

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;

namespace WebApplication14
{
public partial class WebForm56 : System.Web.UI.Page
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = new SqlConnection();
string connection = System.Configuration.ConfigurationManager.AppSettings["con"].ToString();

public void EstablishConnection(string storeprocedure)
{
con.ConnectionString = connection;
cmd.Connection = con;
cmd.Connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storeprocedure;
}

public void CloseConnection()
{
cmd.Connection.Close();
cmd.Connection.Dispose();
con.Close();
}

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString );

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

public void BT_submit_Click(object sender, EventArgs e)
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;

//{
// SqlDataAdapter adp = new SqlDataAdapter("select * from mann", connection);
// DataTable DT = new DataTable();
// adp.Fill(DT);
// cmd.CommandType = CommandType.Text;
//}
if (uniqueEmail() == true)
{
cmd.CommandText = "update mann set benefited_leaves1='" + txt3.Text + "', basic_allowance1='" + txt2.Text + "' where benefited_leaves1='" + txt3.Text + "'";
}
else
{
cmd.CommandText = "insert into mann values('" + txt3.Text + "', '" + txt2.Text + "')";
}

cmd.ExecuteNonQuery();
conn.Close();
//display the modification on database
BindGridView();
}

public bool uniqueEmail()
{
string stremail;
string querye = "select count(benefited_leaves1) as benefited_leaves1 from mann where benefited_leaves1 = '" + txt3.Text + "'";
SqlCommand cmd = new SqlCommand(querye, conn);
SqlDataReader dr;
dr = cmd.ExecuteReader();
while (dr.Read())
{
try
{
stremail = dr["benefited_leaves1"].ToString();
return (stremail != "0");
//You already return the result so the below code would not work anymore, delete them
//if (stremail != "0")
//{
// //errlblemail.Text = "email already exist";
// return false;
//}

}
catch (Exception e)
{
string message = "error";
message += e.Message;
}
finally
{
dr.Close();
}
}
return true;
}

public void BindGridView()
{
string sql = "SELECT * FROM [mann]";
GridView1.DataSource = SelectFromDatabase(sql, null);
GridView1.DataBind();
}

public static DataTable SelectFromDatabase(string sql, SqlParameter[] parameters)
{
string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{

using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}

using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
}
}
}
}

aspx

<%@ Page Title="" Language="C#" MasterPageFile="~/Site1.Master" AutoEventWireup="true" CodeBehind="WebForm56.aspx.cs" Inherits="WebApplication14.WebForm56" %>
<asp:Content ID="Content1" ContentPlaceHolderID="title" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="contentbody" runat="server">

<div>
<table>
<tr>
<td>benefited allowance1
<asp:TextBox runat="server" ID="txt2" Width="220px"></asp:TextBox>
</td>
<td>benefited leaves1
<asp:TextBox runat="server" ID="txt3" Width="220px"></asp:TextBox>
</td>

<td>
<br />
<span style="margin-left: 50px">
<asp:Button ID="BT_submit" runat="server" Text="sumit"
OnClick="BT_submit_Click" Height="35px" Width="135px" BackColor="#3366CC" ForeColor="White"
Style="border-radius: 8px" /></span></td>

</tr>

</table>
<br />
<br />
<asp:GridView ID="GridView1" runat="server"></asp:GridView>



</div>

</asp:Content>

Answer 1

User303363814 posted

Your code asks to update all records where

benefited_leaves1='" + txt3.Text + "'";

SO, if there are many records where benfited_leaves1 is equal to the contents of the text box then they will all be updated.  Is this what is happening?  What do you want to happen?  If you only want one record to be updated then you need to uniquely specify one record.

Answer 2

User1535942433 posted

Hi prabhjot1313,

Accroding to your description,I suggest you could add a checkbox and the uniquely identifies such as id.When you update the record,you need the check if the checkbox is checked and get the id.Then in the update operation,you need to check if it is the current selected row.

More details,you could refer to below codes:

<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
    $(function () {
        $("[id*=GridView1] input[type=checkbox]").click(function () {
            if ($(this).is(":checked")) {
                $("[id*=GridView1] input[type=checkbox]").removeAttr("checked");
                $(this).attr("checked", "checked");
            }
        });
    });
</script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="contentbody" runat="server">
    <div>
        <table>
            <tr>
                <td>Basic allownace
                <asp:TextBox runat="server" ID="txt2" Width="220px"></asp:TextBox>
                </td>
                <td>benefited leaves1
                <asp:TextBox runat="server" ID="txt3" Width="220px"></asp:TextBox>
                </td>

                <td>
                    <br />
                    <span style="margin-left: 50px">
                        <asp:Button ID="BT_submit" runat="server" Text="sumit" OnClick="BT_submit_Click" Height="35px" Width="135px" BackColor="#3366CC" ForeColor="White"
                            Style="border-radius: 8px" /></span></td>
            </tr>

        </table>
        <br />
        <br />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
              <Columns>
                  <asp:TemplateField HeaderText="id" Visible="false">
                   <ItemTemplate>
                       <asp:Label ID="Label1" runat="server" Text='<%# Eval("id") %>'></asp:Label>
                   </ItemTemplate>
               </asp:TemplateField> 
                <asp:BoundField HeaderText="benefited_leaves1" DataField="benefited_leaves1" />
                <asp:BoundField HeaderText="basic_allowance1" DataField="basic_allowance1" />
               <asp:TemplateField>
                   <ItemTemplate>
                       <asp:CheckBox ID="CheckBox1" runat="server"/>
                   </ItemTemplate>
               </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </div>
</asp:Content>

Code-behind:

  protected void BT_submit_Click(object sender, EventArgs e)
        {

            string str = System.Configuration.ConfigurationManager.ConnectionStrings["aspnet-TestApplicationWithDatabase-20190820030542"].ConnectionString;
            SqlConnection con = new SqlConnection(str);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            
            if (uniqueEmail() == true)
            {
                foreach (GridViewRow row in GridView1.Rows)
                {
                    if (row.RowType == DataControlRowType.DataRow)
                    {
                        CheckBox chkRow = (row.Cells[3].FindControl("CheckBox1") as CheckBox);
                        if (chkRow.Checked)
                        {
                            string id = ((Label)row.Cells[0].FindControl("Label1")).Text;
                            cmd.CommandText = "update mann set benefited_leaves1='" + txt3.Text + "', basic_allowance1='" + txt2.Text + "' where benefited_leaves1='" + txt3.Text + "' and id='"+ id +"'";
                            
                        }
                    }
                }
            }
            else
            {
                cmd.CommandText = "insert into mann values('" + txt3.Text + "', '" + txt2.Text + "')";
            }
            con.Open();
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
            con.Close();
            BindGridView();
        }

Result:

Best regards,

Yijing Sun