Same Data recorded if entered many times !

Jahangir Khan

Now this program works great. BUT data gets entered twice without any problem. same ID same name, address , and amount & year.

Any suggestion to fix this ?

using System;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace dss
{
public partial class Form1 : Form
{
SqlConnection con = new SqlConnection("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();
if (string.IsNullOrWhiteSpace(tbID.Text))
{
MessageBox.Show("ID Required");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
{
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 + "' WHERE MemberId = '" + tbID.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 + "' WHERE MemberId = '" + tbID.Text + "' ";
cmd.CommandText = Sql;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Data Updated");
tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbMobile.Clear(); tbEmail.Clear(); tbYear.Clear(); tbAmount.Clear();
}
catch (Exception error)
{
MessageBox.Show(error.ToString());
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
try
{
SqlCommand cmd = new SqlCommand();
string Sql = "DELETE FROM Members WHERE MemberId = '" + tbID.Text + "' ";
cmd.CommandText = Sql;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Sql = "DELETE FROM Payments WHERE MemberId = '" + tbID.Text + "' ";
cmd.CommandText = Sql;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbMobile.Clear(); tbEmail.Clear(); tbYear.Clear(); tbAmount.Clear();
MessageBox.Show("Data Deleted");
con.Close();
}
catch (Exception error)
{
MessageBox.Show(error.ToString());
}
}
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();
}
}
}

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jahangir Khan10y ago
Hello Ekrem,
Thank you so much for your time and effort to assist me, Please look at this final code for the program, it works as it should.
I have som other programs with Data grid view, where its easy to look at the data and double click on it edit it and add & delete it.
usingSystem;
usingSystem.Windows.Forms;
usingSystem.Data.SqlClient;
namespacedss
{
publicpartialclassForm1:Form
{
SqlConnectioncon=newSqlConnection("DataSource=JAKHAN-PC\\sqlexpress;InitialCatalog=JG_Test;IntegratedSecurity=True");
publicForm1()
{
InitializeComponent();
}
privatevoidbtnSearch_Click(objectsender,EventArgse)
{
if(String.IsNullOrEmpty(tbID.Text))
{
MessageBox.Show("EnterID.","Error",MessageBoxButtons.OK,MessageBoxIcon.Warning);
}
SqlDataReaderreader;
SqlCommandcmd=newSqlCommand();
try
{
stringsql="SELECT*FROMMembersWHEREMemberId='"+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();
tbCellular.Text=reader["Cellular"].ToString();
tbEmail.Text=reader["Email"].ToString();
tbAddress.Text=reader["Address"].ToString();
}
con.Close();
sql="SELECT*FROMPaymentsWHEREMemberId='"+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(Exceptionex)
{
MessageBox.Show(ex.Message.ToString());
}
}
privatevoidbtnAdd_Click(objectsender,EventArgse)
{
{
con.Open();
stringSql="INSERTINTOMembers(MemberId,Name,Cellular,Email,Address)VALUES"+"(@Id,@name,@cell,@email,@address)";
using(SqlCommandcmd=newSqlCommand(Sql,con))
{
cmd.CommandText=Sql;
cmd.Parameters.AddWithValue("@Id",tbID.Text);
cmd.Parameters.AddWithValue("@name",tbName.Text);
cmd.Parameters.AddWithValue("@cell",tbCellular.Text);
cmd.Parameters.AddWithValue("@email",tbEmail.Text);
cmd.Parameters.AddWithValue("@address",tbAddress.Text);
cmd.ExecuteNonQuery();
Sql="INSERTINTOPayments(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("DataAdded");
tbID.Clear();tbName.Clear();tbCellular.Clear();tbEmail.Clear();tbAddress.Clear();tbYear.Clear();tbAmount.Clear();
con.Close();
}
}
}
privatevoidbtnUpdate_Click(objectsender,EventArgse)
{
try
{
SqlCommandcmd=newSqlCommand();
stringSql="UPDATEMembersSETMemberId='"+tbID.Text+"',Name='"+tbName.Text+"',Cellular='"+tbCellular.Text+"',Email='"+tbEmail.Text+"',Address='"+tbAddress.Text+"'WHEREMemberId='"+tbID.Text+"'";
cmd.CommandText=Sql;
cmd.Connection=con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Sql="UPDATEPaymentsSETMemberId='"+tbID.Text+"',Year='"+tbYear.Text+"',Amount='"+tbAmount.Text+"'WHEREMemberId='"+tbID.Text+"'";
cmd.CommandText=Sql;
cmd.Connection=con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("DataUpdated");
tbID.Clear();tbName.Clear();tbAddress.Clear();tbCellular.Clear();tbEmail.Clear();tbYear.Clear();tbAmount.Clear();
}
catch(Exceptionerror)
{
MessageBox.Show(error.ToString());
}
}
privatevoidbtnDelete_Click(objectsender,EventArgse)
{
try
{
SqlCommandcmd=newSqlCommand();
stringSql="DELETEFROMMembersWHEREMemberId='"+tbID.Text+"'";
cmd.CommandText=Sql;
cmd.Connection=con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Sql="DELETEFROMPaymentsWHEREMemberId='"+tbID.Text+"'";
cmd.CommandText=Sql;
cmd.Connection=con;
con.Open();
cmd.ExecuteNonQuery();
tbID.Clear();tbName.Clear();tbAddress.Clear();tbCellular.Clear();tbEmail.Clear();tbYear.Clear();tbAmount.Clear();
MessageBox.Show("DataDeleted");
con.Close();
}
catch(Exceptionerror)
{
MessageBox.Show(error.ToString());
}
}
privatevoidbtnReset_Click(objectsender,EventArgse)
{
tbID.Clear();tbName.Clear();tbAddress.Clear();tbCellular.Clear();tbEmail.Clear();tbYear.Clear();tbAmount.Clear();
}
privatevoidbtnExit_Click(objectsender,EventArgse)
{
Application.Exit();
}
}
}
This program uses fileds there is no data grid. what I am struggling with or you can say want to add some smart functions to this is that, a: if a user enters ID "1234" and that data for that it already exists in the SQL Database instead of the program crashing or through exception, shows a pop up alert that the data already exists. b: may the filed Year should be a combo box instead of a text box where we can see years like in a drop down list and if lets say user chooses 2016 the only the corresponding amount should appear in the amount text box. That's it, do you think its logical and makes sense or its not a good idea.
0
Ekrem Tapan10y ago
Hi Jahangir
I'm sorry reply to u very late,
U can use this query, its working succesfull
IF NOT EXISTS
(SELECT * FROM dbo.Members WHERE Name=@Name AND Address=@Address and Mobile=@Mobile andEmail =@Email )
BEGIN INSERT INTO dbo.Members
(MemberId, Name, Address , Mobile,Email )
VALUES
(@MemberId,@Name,@Address, @Mobile, @Email)
END
0
Jahangir Khan10y ago
New corrected statement :-
string Sql = "insert into Members ( MemberId, Name, Address, Mobile, Email ) VALUES (@Id, @name, @address, @mobile, @email) WHERE not exists(select * from MemberId=@Id and Name=@name and Email=@Email )";
Gives this error !
{"Incorrect syntax near the keyword 'WHERE'.\r\nIncorrect syntax near '='."}
0
Ekrem Tapan10y ago
insert into Members( MemberId, Name, Address, Mobile, Email )VALUES (@Id, @name, @address, @mobile, @email) WHERE not exists(select * from Members Where MemberId=@Id and Name=@name and Email=@Email )
u can try again
just im wrong at red line
insert into Members( MemberId, Name, Address, Mobile, Email )VALUES (@Id, @name, @address, @mobile, @email) WHERE not exists(select * from Memberswhere MemberId=@Id and Name=@name and Email=@Email )
0
Jahangir Khan10y ago
Ekrem,
the new statement according to you in line 65:-
string Sql = "INSERT INTO Members(MemberId, Name, Address, Mobile, Email) VALUES (@Id, @name, @address, @mobile, @email) WHERE NOT EXISTS (SELECT * FROM Members WHERE MemberId=@Id and Name=@name and Email=@Email)";
Then I get an error :- {"Incorrect syntax near the keyword 'WHERE'."}
I am kind of lost !
0
Ekrem Tapan10y ago
insert into Members( MemberId, Name, Address, Mobile, Email )VALUES (@Id, @name, @address, @mobile, @email) WHERE not exists(select * from Memberswhere MemberId=@Id and Name=@name and Email=@Email )
0
Jahangir Khan10y ago
You mean in line 65 :-
string Sql = "INSERT INTO Members ( MemberId, Name, Address, Mobile, Email ) VALUES " + " (@Id, @name, @address, @mobile, @email)";
something like this :-
string Sql = "INSERT INTO Members ( MemberId, Name, Address, Mobile, Email ) VALUES " + " (@Id, @name, @address, @mobile, @email) WHERE ( MemberId = '1') ";
OR
string Sql = "SELECT Members WHERE( MemberId = '1') ( Name, Address, Mobile, Email ) VALUES " + " (@Id) , (@name, @address, @mobile, @email)";
0
Ekrem Tapan10y ago
change insert query insert into tablename (code) Select '1448523' Where not exists(select * from tablename where code='1448523')
0