TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Jahangir Khan
NA
27
5.9k
Funtion's for ComboBox
May 17 2016 8:20 AM
This program uses fileds there is no data grid. what I am struggling with or you can say I want to add some smart functions to this is that.
If a user enters ID "1234" and this data already exists in the SQL Database instead of the program crashing or through exception, shows a pop up alert that the data already exists.
The filed Year is a combo box instead of a text box, where we can see years like in a drop down list and if user chooses ID "1234" and chooses 2016 for this only the corresponding amount should appear in the amount text box, or if there are other years for this user ID can be checked.
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
Form1_Load(
object
sender, EventArgs e)
{
this
.paymentsTableAdapter.Fill(
this
.jG_TestDataSet2.Payments);
cmbYear.SelectedIndex = -1;
}
private
void
btnSearch_Click(
object
sender, EventArgs e)
{
if
(String.IsNullOrEmpty(tbID.Text))
{
MessageBox.Show(
"Enter ID."
,
"Error"
, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
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();
tbCellular.Text = reader[
"Cellular"
].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();
cmbYear.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)
{
{
con.Open();
string
Sql =
"INSERT INTO Members ( MemberId, Name, Cellular, Email, Address ) VALUES "
+
" (@Id, @name, @cell, @email, @address)"
;
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(
"@cell"
, tbCellular.Text);
cmd.Parameters.AddWithValue(
"@email"
, tbEmail.Text);
cmd.Parameters.AddWithValue(
"@address"
, tbAddress.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"
, cmbYear.Text);
cmd.Parameters.AddWithValue(
"@amount"
, tbAmount.Text);
cmd.ExecuteNonQuery();
MessageBox.Show(
"Data Added"
);
tbID.Clear(); tbName.Clear(); tbCellular.Clear(); tbEmail.Clear(); tbAddress.Clear();
/*cmbYear.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 +
"', Cellular = '"
+ tbCellular.Text +
"', Email = '"
+ tbEmail.Text +
"', Address = '"
+ tbAddress.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 = '"
+ cmbYear.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(); tbCellular.Clear(); tbEmail.Clear();
/*cmbYear.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(); tbCellular.Clear(); tbEmail.Clear();
/*cmbYear.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(); tbCellular.Clear(); tbEmail.Clear();
/*tbYear.Clear();*/
tbAmount.Clear();
}
private
void
btnExit_Click(
object
sender, EventArgs e)
{
Application.Exit();
}
private
void
cmbYear_SelectedIndexChanged(
object
sender, EventArgs e)
{
}
}
}
Reply
Answers (
2
)
How to Loop and Update SQL Table Summing any existing Values
print all values in a single excell sheet?