void Button1Click(object sender, EventArgs e)
{
sambung = new Koneksi();
cek = "SELECT COUNT(nomor_anggota) FROM `peminjaman_buku` WHERE nomor_anggota =" + noAgt.SelectedItem + " " +
"AND tanggal_pengembalian is NULL";
try{
sambung.buka();
query = new MySqlCommand(cek, sambung.koneksi);
dr = query.ExecuteReader();
dr.Read();
if (Convert.ToInt16(dr[0].ToString()) >= 3)
{
MessageBox.Show("Maksimum 3 peminjaman", "information..!!");
}
else
{
dr.Close();
cek = "SELECT COUNT(nomor_anggota) FROM `peminjaman_buku` WHERE nomor_anggota =" + noAgt.SelectedItem + " " +
"AND tanggal_pengembalian is NULL AND kode_buku = '" + kodebuku.SelectedItem + "'";
query = new MySqlCommand(cek, sambung.koneksi);
dr = query.ExecuteReader();
dr.Read();
if (Convert.ToInt16(dr[0].ToString()) >= 1)
{
MessageBox.Show("Buku Ini Telah Di pinjam Oleh Anggota Ini", "information..!!");
}
else
{
dr.Close();
sql = "insert into peminjaman_buku (nomor_anggota,kode_buku,tanggal_peminjaman,userid) " +
"values(" + noAgt.SelectedItem + ", '" + kodebuku.SelectedItem + "', '" + dateTimePicker1.Value.ToString("yyyyMMdd") + "', 1)";
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
updatestok = "update buku set stock = stock - 1";
query = new MySqlCommand(updatestok, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
TampilDalamTable();
bersihkan();
MessageBox.Show("Data Berhasil Disimpan !!", "information");
}
}
} catch (Exception er) {
MessageBox.Show(er.Message);
}
sambung.tutup();
}
void TextBox1KeyPress(object sender, KeyPressEventArgs e)
{
sambung = new Koneksi();
if (e.KeyChar == (char)Keys.Enter)
{
sql =
"select biodata_mahasiswa.nama from anggota_perpustakaan " +
"inner join biodata_mahasiswa on biodata_mahasiswa.stb = anggota_perpustakaan.nomor " +
"where anggota_perpustakaan.nomor =" + textBox1.Text;
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
dr = query.ExecuteReader();
if (dr.Read() == true)
{
button1.Enabled = false;
MessageBox.Show("Anda Telah Terdaftar sebagai Anggota");
textBox2.Text = dr[0].ToString();
}
else if (dr.Read() == false)
{
sql = "select nama from biodata_mahasiswa where stb=" + textBox1.Text;
dr.Close();
query = new MySqlCommand(sql, sambung.koneksi);
dr = query.ExecuteReader();
if (dr.Read() == true)
{
button1.Enabled = true;
MessageBox.Show("STB " + textBox1.Text + " Tidak Terdaftar sebagai Anggota");
textBox2.Text = dr[0].ToString();
}
else
{
button1.Enabled = false;
textBox2.Clear();
MessageBox.Show("Anda Tidak Terdaftar sebagai Mahasiswa");
}
}
sambung.tutup();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
}
}
void EditClick(object sender, EventArgs e)
{
sambung = new Koneksi();
sql = "UPDATE mahasiswa SET nama ='Mimi Amelia' where id=4 ";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
MessageBox.Show("Data Berhasil Diubah !!", "information");
TampilDalamTable();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
sambung.tutup();
}
void Button1Click(object sender, EventArgs e)
{
sambung = new Koneksi();
sql = "insert into anggota_perpustakaan (nomor, tanggal_masuk,userid)" +
"values (" + textBox1.Text + ",'" + dateTimePicker1.Value.ToString("yyyyMMdd") + "',1)";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
MessageBox.Show("Data Berhasil Disimpan !!", "information");
TampilDalamTable();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
sambung.tutup();
}
void ComboBox1SelectedIndexChanged(object sender, EventArgs e)
{
sambung = new Koneksi();
sql = "SELECT nama FROM kategori_buku where kode_kategori ='" + comboBox1.SelectedItem + "' ";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
dr = query.ExecuteReader();
dr.Read();
textBox2.Text = dr[0].ToString();
sambung.tutup();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
}
void HapusClick(object sender, EventArgs e)
{
sambung = new Koneksi();
sql = "DELETE FROM mahasiswa where stb='" + stb.Text + "' ";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
MessageBox.Show("Data Berhasil Dihapus !!", "information");
bersihkan();
TampilDalamTable();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
sambung.tutup();
}
public DataTable bacasemua()
{
sambung = new Koneksi();
sql = "Select * From Mahasiswa";
tabel = new DataTable();
try {
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
adapter.Fill(tabel);
} catch (Exception er) {
MessageBox.Show(er.Message);
}
sambung.tutup();
return(tabel);
}
public DataTable tampilpencarian(string cari)
{
sambung = new Koneksi();
sql = "SELECT * FROM mahasiswa where nama LIKE '%" + cari + "%'";
tabel = new DataTable();
try {
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
adapter.Fill(tabel);
} catch (Exception er) {
MessageBox.Show(er.Message);
}
sambung.tutup();
return(tabel);
}
void Button1Click(object sender, EventArgs e)
{
sambung = new Koneksi();
sql = "INSERT INTO anggota_perpustakaan (nomor, tanggal_masuk, last_updated, userid)" +
"VALUES ('" + stb.Text + "', NOW(), 'NULL', 1)";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
MessageBox.Show("Data Berhasil Disimpan !!", "information");
} catch (Exception er) {
MessageBox.Show(er.Message);
}
sambung.tutup();
}
public void kode_buku()
{
sambung = new Koneksi();
sql = "SELECT kode_buku FROM buku where stock > 0";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
dr = query.ExecuteReader();
while (dr.Read())
{
kodebuku.Items.Add(dr[0].ToString());
}
sambung.tutup();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
}
public void nomor_anggota()
{
sambung = new Koneksi();
sql = "SELECT nomor FROM anggota_perpustakaan";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
dr = query.ExecuteReader();
while (dr.Read())
{
noAgt.Items.Add(dr[0].ToString());
}
sambung.tutup();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
}
void KodebukuSelectedIndexChanged(object sender, EventArgs e)
{
sambung = new Koneksi();
sql = "SELECT judul, stock FROM buku where kode_buku ='" + kodebuku.SelectedItem + "' ";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
dr = query.ExecuteReader();
if (dr.Read())
{
kdbuku.Text = dr[0].ToString();
stok.Text = dr[1].ToString();
}
sambung.tutup();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
}
public void nomor_anggota()
{
sambung = new Koneksi();
sql = "SELECT distinct nomor_anggota FROM peminjaman_buku where tanggal_pengembalian is Null ";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
dr = query.ExecuteReader();
while (dr.Read())
{
comboBox1.Items.Add(dr[0].ToString());
}
sambung.tutup();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
}
void kode_kategori()
{
sambung = new Koneksi();
sql = "SELECT kode_kategori FROM kategori_buku";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
dr = query.ExecuteReader();
while (dr.Read())
{
comboBox1.Items.Add(dr[0].ToString());
}
sambung.tutup();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
}
void EditClick(object sender, EventArgs e)
{
sambung = new Koneksi();
sql = "UPDATE mahasiswa SET nama ='" + nama.Text + "', alamat ='" + alamat.Text + "', " +
"nilai_tugas=" + nt.Value + ", nilai_mid=" + nm.Value + ", " +
"nilai_final=" + nf.Value + ", nilai_akhir='" + na.Text + "', nilai_huruf='" + nh.Text + "', " +
"keterangan='" + keterangan.Text + "' where stb='" + stb.Text + "' ";
try {
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
MessageBox.Show("Data Berhasil Diubah !!", "information");
TampilDalamTable();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
sambung.tutup();
}
public void kodeprodi()
{
sambung = new Koneksi();
sql = "SELECT id FROM program_studi";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
dr = query.ExecuteReader();
while (dr.Read())
{
prodi.Items.Add(dr[0].ToString());
}
sambung.tutup();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
}
public void kode_buku()
{
sambung = new Koneksi();
sql = "SELECT kode_buku from peminjaman_buku where nomor_anggota = " + comboBox1.SelectedItem + " AND tanggal_pengembalian is NULL";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
dr = query.ExecuteReader();
while (dr.Read())
{
comboBox2.Items.Add(dr[0].ToString());
}
sambung.tutup();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
}
public DataTable anggota_perpustakaan()
{
sambung = new Koneksi();
sql = "Select anggota_perpustakaan.nomor, biodata_mahasiswa.nama, anggota_perpustakaan.tanggal_masuk " +
"From anggota_perpustakaan inner join biodata_mahasiswa " +
"on anggota_perpustakaan.nomor = biodata_mahasiswa.stb";
tabel = new DataTable();
try {
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
adapter.Fill(tabel);
} catch (Exception er) {
MessageBox.Show(er.Message);
}
sambung.tutup();
return(tabel);
}
public DataTable databuku()
{
sambung = new Koneksi();
sql = "Select buku.kode_buku, kategori_buku.nama, buku.judul, buku.jumlah, " +
"buku.stock, buku.pengarang, buku.penerbit, buku.tahun_terbit, buku.tahun_pengadaan, " +
"buku.sumber, buku.rak, buku.ISBN, buku.foto from buku " +
"inner join kategori_buku on kategori_buku.kode_kategori = buku.kode_kategori ";
tabel = new DataTable();
try {
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
adapter.Fill(tabel);
} catch (Exception er) {
MessageBox.Show(er.Message);
}
sambung.tutup();
return(tabel);
}
void SimpanClick(object sender, EventArgs e)
{
// InputDataMhs inputdatamhs = new InputDataMhs();
// inputdatamhs.ShowDialog();
sambung = new Koneksi();
sql = "INSERT INTO mahasiswa (stb, nama, alamat, nilai_tugas, nilai_mid, nilai_final, nilai_akhir, " +
"nilai_huruf, keterangan) VALUES ('172298', 'Brian', 'Citra Sudiang Indah', 67, 87, 77, 87, 'A', " +
"'Sangat Baik')";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
MessageBox.Show("Data Berhasil Disimpan !!", "information");
TampilDalamTable();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
sambung.tutup();
}
void NoAgtSelectedIndexChanged(object sender, EventArgs e)
{
sambung = new Koneksi();
sql = "SELECT biodata_mahasiswa.nama FROM anggota_perpustakaan " +
"inner join biodata_mahasiswa on anggota_perpustakaan.nomor = biodata_mahasiswa.stb " +
"where anggota_perpustakaan.nomor =" + noAgt.SelectedItem;
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
dr = query.ExecuteReader();
while (dr.Read())
{
nomoragt.Text = dr[0].ToString();
}
sambung.tutup();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
}
void StbKeyPress(object sender, KeyPressEventArgs e)
{
sambung = new Koneksi();
if (e.KeyChar == (char)Keys.Enter)
{
sql = "SELECT * FROM mahasiswa where stb='" + stb.Text + "' ";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
// dr = new MySqlDataReader();
dr = query.ExecuteReader();
if (dr.Read())
{
MessageBox.Show("Data Sudah Ada", "information");
simpan.Enabled = false;
edit.Enabled = true;
hapus.Enabled = true;
nama.Text = dr[1].ToString();
alamat.Text = dr[2].ToString();
nt.Value = Convert.ToInt32(dr[3]);
nm.Value = Convert.ToInt32(dr[4]);
nf.Value = Convert.ToInt32(dr[5]);
na.Text = dr[6].ToString();
nh.Text = dr[7].ToString();
keterangan.Text = dr[8].ToString();
}
else
{
simpan.Enabled = true;
nama.Select();
}
sambung.tutup();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
}
}
public DataTable cari_peminjaman(string cari)
{
sambung = new Koneksi();
sql = "Select peminjaman_buku.nomor_anggota, biodata_mahasiswa.nama,buku.kode_buku ,buku.judul, peminjaman_buku.tanggal_peminjaman " +
"From peminjaman_buku " +
"inner join biodata_mahasiswa on peminjaman_buku.nomor_anggota = biodata_mahasiswa.stb " +
"inner join buku on peminjaman_buku.kode_buku= buku.kode_buku " +
"where peminjaman_buku.nomor_anggota LIKE '%" + cari + "%' or biodata_mahasiswa.nama LIKE '%" + cari + "%' ";
tabel = new DataTable();
try {
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
adapter.Fill(tabel);
} catch (Exception er) {
MessageBox.Show(er.Message);
}
sambung.tutup();
return(tabel);
}
void Button1Click(object sender, EventArgs e)
{
sambung = new Koneksi();
sql = "INSERT INTO biodata_mahasiswa (stb, nama, kode_program_studi, kode_agama, tempat_lahir," +
"tanggal_lahir, jenis_kelamin, alamat, kota, kode_propinsi, kode_pos, telpon," +
"handphone, hobi, wali, alamat_wali, telpon_wali, tahun_masuk, userid) " +
"VALUES (" + stb.Text + ", " +
"'" + nama.Text + "', " +
" " + prodi.SelectedItem + ", " +
" " + agama.SelectedItem + ", " +
"'" + tempatlahir.Text + "', " +
"'" + dateTimePicker1.Value.ToString("yyyyMMdd") + "', " +
"'" + jkel.SelectedItem + "'," +
"'" + alamat.Text + "', " +
"'" + kota.Text + "', " +
" " + propinsi.SelectedItem + " , " +
"'" + kodepos.Text + "', " +
"'" + telepon.Text + "', " +
"'" + hp.Text + "', " +
"'" + hobi.Text + "', " +
"'" + wali.Text + "', " +
"'" + alamatwali.Text + "', " +
"'" + telwali.Text + "', " +
" " + tahunmasuk.SelectedItem + ", " +
" 1 ) ";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
MessageBox.Show("Data Berhasil Disimpan !!", "information");
// bersihkan();
// TampilDalamTable();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
sambung.tutup();
}
void SimpanClick(object sender, EventArgs e)
{
sambung = new Koneksi();
sql = "INSERT INTO mahasiswa (stb, nama, alamat, nilai_tugas, nilai_mid, nilai_final, nilai_akhir, " +
"nilai_huruf, keterangan) " +
"VALUES ('" + stb.Text + "', '" + nama.Text + "', '" + alamat.Text + "', " + nt.Value + ", " + nm.Value + ", " + nf.Value + ", '" + na.Text + "', '" + nh.Text + "', " +
"'" + keterangan.Text + "')";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
query.ExecuteNonQuery();
MessageBox.Show("Data Berhasil Disimpan !!", "information");
bersihkan();
TampilDalamTable();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
sambung.tutup();
}
void StbKeyPress(object sender, KeyPressEventArgs e)
{
sambung = new Koneksi();
if (e.KeyChar == (char)Keys.Enter)
{
sql = "SELECT * FROM anggota_perpustakaan where nomor='" + stb.Text + "' ";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
dr = query.ExecuteReader();
if (dr.Read())
{
MessageBox.Show("Data Sudah Ada", "information");
nama.Text = dr[2].ToString();
}
else
{
dr.Close();
sql = "SELECT * FROM biodata_mahasiswa where stb='" + stb.Text + "' ";
query = new MySqlCommand(sql, sambung.koneksi);
adapter = new MySqlDataAdapter(query);
cek = query.ExecuteReader();
if (cek.Read())
{
MessageBox.Show("Anda Tidak ", "information");
}
else
{
MessageBox.Show("Anda Tidak Terdaftar Sebagai Mahasiswa", "information");
}
}
sambung.tutup();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
}
}
void ComboBox1SelectedIndexChanged(object sender, EventArgs e)
{
sambung = new Koneksi();
comboBox2.Items.Clear();
comboBox2.Text = "";
textBox2.Clear();
kode_buku();
sql = "SELECT nama from biodata_mahasiswa where stb = '" + comboBox1.SelectedItem + "' ";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
dr = query.ExecuteReader();
if (dr.Read())
{
textBox1.Text = dr[0].ToString();
}
sambung.tutup();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
}
void ComboBox2SelectedIndexChanged(object sender, EventArgs e)
{
sambung = new Koneksi();
sql = "SELECT buku.judul, peminjaman_buku.tanggal_peminjaman FROM peminjaman_buku " +
"inner join buku on buku.kode_buku = peminjaman_buku.kode_buku " +
"where nomor_anggota =" + comboBox1.SelectedItem + " and peminjaman_buku.kode_buku = '" + comboBox2.SelectedItem + "' " +
"and peminjaman_buku.tanggal_pengembalian is null";
try{
sambung.buka();
query = new MySqlCommand(sql, sambung.koneksi);
dr = query.ExecuteReader();
if (dr.Read())
{
textBox2.Text = dr[0].ToString();
dateTimePicker1.Text = dr[1].ToString();
}
sambung.tutup();
} catch (Exception er) {
MessageBox.Show(er.Message);
}
}