Update function problem !

Question

When I call some values and update them,  then all the values which already existed (already present)  in the database change to this update. 

 

I have 2 tables "Members" and "Payments" and I have 8 different members and there details in the database, I call John Doe's data and change address and update it.

It successfully updates it. 

 

When I check the database all the 8 different members all converted to John Doe and the values I changed.

 

I am quite confused ! 

using System;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;

namespace dss
{
    public partial class Form1 : Form

    {
        SqlConnection con = new SqlConnection("Data Source=JAKHAN-PC\\sqlexpress;Initial Catalog=JG_Test;Integrated Security=True");
        string connString = "Data Source=JAKHAN-PC\\sqlexpress;Initial Catalog=JG_Test;Integrated Security=True";
        public Form1()
        {
            InitializeComponent();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand();
            try
            {
                string sql = "SELECT * FROM Members WHERE MemberId = '"+ tbID.Text +"' ";
                cmd.Connection = con;
                cmd.CommandText = sql;
                con.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    tbID.Text = reader["MemberId"].ToString();
                    tbName.Text = reader["Name"].ToString();
                    tbMobile.Text = reader["Mobile"].ToString();
                    tbEmail.Text = reader["Email"].ToString();
                    tbAddress.Text = reader["Address"].ToString();
                }
                con.Close();
                sql= "SELECT * FROM Payments WHERE MemberId  = '" + tbID.Text + "' ";
                cmd.Connection = con;
                cmd.CommandText = sql;
                con.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    tbID.Text = reader["MemberId"].ToString();
                    tbYear.Text = reader["Year"].ToString();
                    tbAmount.Text = reader["Amount"].ToString();

                }
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                string Sql = "INSERT INTO Members ( MemberId, Name, Address, Mobile, Email ) VALUES " + " (@Id, @name, @address, @mobile, @email)";
                using (SqlCommand cmd = new SqlCommand(Sql, con))
                {
                    cmd.CommandText = Sql;
                    cmd.Parameters.AddWithValue("@Id", tbID.Text);
                    cmd.Parameters.AddWithValue("@name", tbName.Text);
                    cmd.Parameters.AddWithValue("@address", tbAddress.Text);
                    cmd.Parameters.AddWithValue("@mobile", tbMobile.Text);
                    cmd.Parameters.AddWithValue("@email", tbEmail.Text);
                    cmd.ExecuteNonQuery();

                    Sql = "INSERT INTO Payments (MemberId, [Year], [Amount] ) VALUES " + "(@id, @year, @amount)";
                    cmd.Parameters.Clear();
                    cmd.CommandText = Sql;
                    cmd.Parameters.AddWithValue("@Id", tbID.Text);
                    cmd.Parameters.AddWithValue("@year", tbYear.Text);
                    cmd.Parameters.AddWithValue("@amount", tbAmount.Text);
                    cmd.ExecuteNonQuery();

                    MessageBox.Show("Data Added");
                    tbID.Clear();tbName.Clear();tbAddress.Clear();tbMobile.Clear();tbEmail.Clear();tbYear.Clear();tbAmount.Clear();
                    con.Close();
                }
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                string Sql = "UPDATE Members SET MemberId = '" + tbID.Text +"', Name = '" + tbName.Text + "', Address = '" + tbAddress.Text + "', Mobile = '" + tbMobile.Text + "', Email = '" + tbEmail.Text + "'";
                cmd.CommandText = Sql;
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();

                Sql = "UPDATE Payments SET MemberId = '" + tbID.Text + "', Year = '" + tbYear.Text + "', Amount = '" + tbAmount.Text + "'";
                cmd.CommandText = Sql;
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();

                MessageBox.Show("Data Updated");
                tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbMobile.Clear(); tbEmail.Clear(); tbYear.Clear(); tbAmount.Clear();
                con.Close();
            }
            catch (Exception error)
            {
                MessageBox.Show(error.ToString());
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {

            //tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbMobile.Clear(); tbEmail.Clear(); tbYear.Clear(); tbAmount.Clear();
        }

        private void btnReset_Click(object sender, EventArgs e)
        {
             tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbMobile.Clear(); tbEmail.Clear(); tbYear.Clear(); tbAmount.Clear();
        }


        private void btnExit_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
    }
}

Answer 1

Hello! JG Reason behind is If you don't provide the Where condition in update command then all values in table will update So provide some condition suing the where clause then try to update the data....

You can selected any attribute or combination of atttributes that make each record unique in table.

Answer 2

Pankaj is right.

Query Sample:

Without Where:

string Sql = "UPDATE Members SET MemberId = '" + tbID.Text +"', Name = '" + tbName.Text + "', Address = '" + tbAddress.Text + "', Mobile = '" + tbMobile.Text + "', Email = '" + tbEmail.Text + "'";

With Where Clause:

string Sql = "UPDATE Members SET MemberId = '" + tbID.Text +"', Name = '" + tbName.Text + "', Address = '" + tbAddress.Text + "', Mobile = '" + tbMobile.Text + "', Email = '" + tbEmail.Text + "'" + "WHERE MemberId = '" + tbID.Text + "' ";