private void gridload()
{
try
{
string query = "SELECT tblbook.bookid AS ID , tblbook.oldaccessionno AS AccNo, tblbook.City AS CAN,tblbook.Typeid AS CallNo,tblbook.Regid AS CupboardNo, tblbook.Name AS Book_Name, tblauthor.name AS Author_Name, tblpublisher.name AS Publisher_Name, tblbook.Edition, tblcatagory.Desc AS Book_Catagory,tblbook.noofbooks AS Quantity, tblbook.Status FROM ((tblauthor INNER JOIN tblbook ON tblauthor.authorid = tblbook.authorid) INNER JOIN tblcatagory ON tblbook.catagoryid = tblcatagory.catagoryid) INNER JOIN tblpublisher ON tblbook.pubid = tblpublisher.pubid;";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
DataTable dt = new DataTable();
SQLiteDataAdapter da = new SQLiteDataAdapter(myCommand);
da.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.EnableHeadersVisualStyles = false;
dataGridView1.ColumnHeadersDefaultCellStyle.BackColor = Color.LightGray;
dataGridView1.Columns[0].Width = 25;
dataGridView1.Columns[1].Width = 40;
dataGridView1.Columns[2].Width = 40;
dataGridView1.Columns[3].Width = 40;
dataGridView1.Columns[4].Width = 45;
dataGridView1.Columns[5].Width = 180;
dataGridView1.Columns[6].Width = 110;
dataGridView1.Columns[7].Width = 100;
dataGridView1.Columns[8].Width = 50;
dataGridView1.Columns[9].Width = 50;
dataGridView1.Columns[10].Width = 50;
dataGridView1.Columns[11].Width = 50;
db.CloseConnection();
// MessageBox.Show("Rows Added: {0}", result.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void gridload()
{
try
{
string query = "SELECT tblemp.empid AS EmpID, tblemp.name AS Employee_Name, lbldept.deptname AS Department,tbldesignation.designation AS Designation FROM tbldesignation INNER JOIN (lbldept INNER JOIN tblemp ON lbldept.deptid = tblemp.deptid) ON tbldesignation.desigid = tblemp.desigid;";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
DataTable dt = new DataTable();
SQLiteDataAdapter da = new SQLiteDataAdapter(myCommand);
da.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.EnableHeadersVisualStyles = false;
dataGridView1.ColumnHeadersDefaultCellStyle.BackColor = Color.LightGray;
dataGridView1.Columns[0].Width = 10;
dataGridView1.Columns[1].Width = 25;
dataGridView1.Columns[2].Width = 15;
dataGridView1.Columns[3].Width = 80;
db.CloseConnection();
// MessageBox.Show("Rows Added: {0}", result.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private bool checkForCAN(String canNo)
{
try
{
database db = new database();
string query = "select * from tblbook where city='" + canNo + "'";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
string result = Convert.ToString(myCommand.ExecuteScalar());
db.CloseConnection();
//MessageBox.Show("Rows Added: {0}", result.ToString());
if (String.IsNullOrEmpty(result))
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
//save button
private void button1_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(textBox1.Text) || String.IsNullOrEmpty(textBox2.Text) || String.IsNullOrEmpty(textBox3.Text) || String.IsNullOrEmpty(textBox4.Text) || String.IsNullOrEmpty(textBox8.Text) || String.IsNullOrEmpty(textBox5.Text) || String.IsNullOrEmpty(textBox6.Text) || String.IsNullOrEmpty(textBox7.Text) || String.IsNullOrEmpty(textBox11.Text) || String.IsNullOrEmpty(textBox9.Text) || String.IsNullOrEmpty(textBox18.Text) || String.IsNullOrEmpty(textBox16.Text) || String.IsNullOrEmpty(textBox12.Text) || String.IsNullOrEmpty(textBox13.Text) || String.IsNullOrEmpty(textBox15.Text))
{
MessageBox.Show("Fields can not be empty", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
// MessageBox.Show("Fields can not be empty");
}
else
{
if (checkForCAN(textBox1.Text))
{
try
{
database db = new database();
string query = "INSERT INTO tblbook ( Name, Edition, City, Country,Typeid,pubid,catagoryid,authorid,Regid, noofbooks, purchasedate, oldaccessionno, language, Publishedyear, volume, pages, price, currancy, translatdby ) VALUES ( '" + textBox9.Text + "', '" + textBox3.Text + "', '" + textBox1.Text + "', '" + comboBox3.Text + "','" + textBox4.Text + "', '" + publisherID(textBox8.Text) + "', '" + categoryID(textBox6.Text) + "','" + authorID(textBox16.Text) + "','" + textBox15.Text + "', '" + textBox5.Text + "', '" + dateTimePicker1.Text + "', '" + textBox2.Text + "', '" + comboBox2.Text + "', '" + textBox7.Text + "', '" + textBox18.Text + "', '" + textBox13.Text + "', '" + textBox11.Text + "', '" + comboBox1.Text + "', '" + textBox12.Text + "' );";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
db.CloseConnection();
MessageBox.Show("Succesfull!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("CAN already exists!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public void check()
{
try
{
database db = new database();
string status = "\"Issued\"";
string query = "SELECT tblbook.bookid FROM tblbook,issue1 where issue1.bookid=tblbook.bookid and issue1.Status=" + status + "";
db.OpenConnection();
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
SQLiteDataReader rdr = myCommand.ExecuteReader();
while (rdr.Read())
{
bidIssue = Convert.ToInt32((rdr.GetValue(0).ToString()));
foreach (DataGridViewRow row in dataGridView1.Rows)
{
//DataGridViewRow row = dataGridView1.SelectedRows[0];
bookId = Convert.ToInt32(row.Cells[0].Value.ToString());
if (bidIssue == bookId)
{
row.DefaultCellStyle.BackColor = Color.Red;
row.DefaultCellStyle.ForeColor = Color.White;
}
}
}
db.CloseConnection();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//find borrower
private void toolStripButton9_Click(object sender, EventArgs e)
{
string name = null;
string date = null;
DataGridViewRow row = dataGridView1.SelectedRows[0];
bookId = Convert.ToInt32(row.Cells[0].Value.ToString());
try
{
database db = new database();
string query = "SELECT name,issueddaate FROM issue1 where bookid='" + bookId + "' and Status=\"Issued\"";
db.OpenConnection();
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
SQLiteDataReader rdr = myCommand.ExecuteReader();
while (rdr.Read())
{
name = (rdr.GetValue(0).ToString());
date = (rdr.GetValue(1).ToString());
}
db.CloseConnection();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (name != null && date != null)
{
MessageBox.Show("This Book has been issued to " + name + " on " + date, "Library Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("This Book has not been issued and it is available for issuance.", "Library Management System", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
//save
private void button1_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(textBox2.Text) || String.IsNullOrEmpty(textBox4.Text) || String.IsNullOrEmpty(textBox10.Text) || String.IsNullOrEmpty(textBox9.Text) || String.IsNullOrEmpty(textBox7.Text) || String.IsNullOrEmpty(textBox3.Text) || String.IsNullOrEmpty(textBox8.Text) || String.IsNullOrEmpty(textBox6.Text))
{
MessageBox.Show("Fields can not be empty", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
// MessageBox.Show("Fields can not be empty");
}
else
{
try
{
database db = new database();
string query = "INSERT INTO tblemp ( name, address, nic, age, hiredate, status, deptid, desigid, contactNo, basicpay ) VALUES ( '" + textBox2.Text + "', '" + textBox4.Text + "', '" + textBox10.Text + "', '" + textBox3.Text + "', '" + dateTimePicker1.Text + "', '" + comboBox1.Text + "', '" + depsecID(textBox6.Text) + "', '" + designationID(textBox8.Text) + "', '" + textBox9.Text + "', '" + textBox7.Text + "' );";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
db.CloseConnection();
MessageBox.Show("Rows Added: {0}", result.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
//save
private void button1_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(textBox1.Text) || String.IsNullOrEmpty(textBox2.Text) || String.IsNullOrEmpty(textBox10.Text) || String.IsNullOrEmpty(dateTimePicker1.Text) || String.IsNullOrEmpty(dateTimePicker2.Text) || String.IsNullOrEmpty(comboBox1.Text))
{
MessageBox.Show("Fields can not be empty", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
// MessageBox.Show("Fields can not be empty");
}
else
{
try
{
database db = new database();
string query;
string returnedDate = "xxxxxx";
if (checkBox1.Checked)
{
query = "INSERT INTO tblissued ( bookid, issueddaate, empid, returndate, qty, status ) VALUES ( '" + bookId + "', '" + dateTimePicker1.Text + "', '" + EmployeeID(textBox1.Text) + "', '" + returnedDate + "', '" + textBox10.Text + "', '" + comboBox1.Text + "' );";
}
else
{
query = "INSERT INTO tblissued ( bookid, issueddaate, empid, returndate, qty, status ) VALUES ( '" + bookId + "', '" + dateTimePicker1.Text + "', '" + EmployeeID(textBox1.Text) + "', '" + dateTimePicker2.Text + "', '" + textBox10.Text + "', '" + comboBox1.Text + "' );";
}
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
db.CloseConnection();
MessageBox.Show("Succesfull!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
//save
private void button1_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(textBox2.Text) || String.IsNullOrEmpty(textBox4.Text) || String.IsNullOrEmpty(textBox10.Text) || String.IsNullOrEmpty(textBox9.Text) || String.IsNullOrEmpty(textBox7.Text) || String.IsNullOrEmpty(textBox3.Text) || String.IsNullOrEmpty(textBox8.Text) || String.IsNullOrEmpty(textBox6.Text))
{
MessageBox.Show("Fields can not be empty", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
// MessageBox.Show("Fields can not be empty");
}
else
{
try
{
database db = new database();
dateTimePicker1.Format = DateTimePickerFormat.Custom;
dateTimePicker1.CustomFormat = "dd-MMM-yy";
string query = "UPDATE tblemp SET name = '" + textBox2.Text + "', address = '" + textBox4.Text + "', nic = '" + textBox10.Text + "', age = '" + textBox3.Text + "', hiredate = '" + dateTimePicker1.Text + "', status = '" + comboBox1.Text + "', deptid = '" + depsecID(textBox6.Text) + "', desigid = '" + designationID(textBox8.Text) + "', contactNo = '" + textBox9.Text + "', basicpay = '" + textBox7.Text + "' WHERE empid = '" + employeeID + "';";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
db.CloseConnection();
MessageBox.Show("Succesfull!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
this.Close();
}
}
//save button
private void button1_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(textBox1.Text) || String.IsNullOrEmpty(textBox2.Text) || String.IsNullOrEmpty(textBox3.Text) || String.IsNullOrEmpty(textBox4.Text) || String.IsNullOrEmpty(textBox8.Text) || String.IsNullOrEmpty(textBox5.Text) || String.IsNullOrEmpty(textBox6.Text) || String.IsNullOrEmpty(textBox7.Text) || String.IsNullOrEmpty(textBox11.Text) || String.IsNullOrEmpty(textBox9.Text) || String.IsNullOrEmpty(textBox18.Text) || String.IsNullOrEmpty(textBox16.Text) || String.IsNullOrEmpty(textBox12.Text) || String.IsNullOrEmpty(textBox13.Text) || String.IsNullOrEmpty(textBox15.Text))
{
MessageBox.Show("Fields can not be empty", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
// MessageBox.Show("Fields can not be empty");
}
else
{
try
{
database db = new database();
dateTimePicker1.Format = DateTimePickerFormat.Custom;
dateTimePicker1.CustomFormat = "dd-MMM-yy";
string query = "UPDATE tblbook SET Name = '" + textBox9.Text + "', Edition = '" + textBox3.Text + "', City = '" + textBox1.Text + "', Country = '" + comboBox3.Text + "', Typeid = '" + textBox4.Text + "', pubid = '" + publisherID(textBox8.Text) + "', catagoryid = '" + categoryID(textBox6.Text) + "', authorid = '" + authorID(textBox16.Text) + "', Regid = '" + textBox15.Text + "', noofbooks = '" + textBox5.Text + "', purchasedate = '" + dateTimePicker1.Text + "', oldaccessionno = '" + textBox2.Text + "', language = '" + comboBox2.Text + "', Publishedyear = '" + textBox7.Text + "', volume = '" + textBox18.Text + "', pages = '" + textBox13.Text + "', price = '" + textBox11.Text + "', currancy = '" + comboBox1.Text + "', translatdby = '" + textBox12.Text + "' WHERE bookid = '" + bookId2 + "';";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
db.CloseConnection();
MessageBox.Show("Succesfull!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
this.Close();
}
}
public void gridload()
{
try
{
string query = "SELECT tblissued.issuedid AS ID, tblissued.issueddaate AS IssueDate, tblissued.returndate AS ReturnDate, tblissued.qty AS Quantity, tblbook.city AS CAN, tblbook.Name AS BookName, tblbook.Edition AS Edition, tblemp.name AS Employee, lbldept.deptname AS Department, tblissued.status AS Status, tblcatagory.Desc AS Category, tblbook.oldaccessionno AS AccNo, tblbook.bookid AS BookID FROM lbldept INNER JOIN ((tblemp INNER JOIN (tblbook INNER JOIN tblissued ON tblbook.bookid=tblissued.bookid) ON tblemp.empid=tblissued.empid) INNER JOIN tblcatagory ON tblbook.catagoryid=tblcatagory.catagoryid) ON lbldept.deptid=tblemp.deptid;";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
dt = new DataTable();
da = new SQLiteDataAdapter(myCommand);
da.Fill(start, 75, dt);
dataGridView1.DataSource = dt;
Back.Enabled = false;
toolStripButton4.Enabled = false;
recordText.Text = "Record " + (start + 1) + " - " + (start + 75) + " of " + rowsCount();
dataGridView1.EnableHeadersVisualStyles = false;
dataGridView1.ColumnHeadersDefaultCellStyle.BackColor = Color.LightGray;
dataGridView1.Columns[0].Width = 30;
dataGridView1.Columns[1].Width = 50;
dataGridView1.Columns[2].Width = 50;
dataGridView1.Columns[3].Width = 30;
dataGridView1.Columns[4].Width = 40;
dataGridView1.Columns[5].Width = 150;
dataGridView1.Columns[6].Width = 50;
dataGridView1.Columns[7].Width = 100;
dataGridView1.Columns[8].Width = 70;
dataGridView1.Columns[9].Width = 70;
dataGridView1.Columns[10].Width = 70;
db.CloseConnection();
// MessageBox.Show("Rows Added: {0}", result.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void grid_load()
{
try
{
string query = "SELECT pubid AS PublisherID, name AS Name, country AS Country FROM tblpublisher;";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
DataTable dt = new DataTable();
SQLiteDataAdapter da = new SQLiteDataAdapter(myCommand);
da.Fill(dt);
dataGridView1.DataSource = dt;
db.CloseConnection();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//load data
private void selectAuthorForm_Load(object sender, EventArgs e)
{
try
{
string query = "SELECT authorid AS AuthorID, name AS Name, country AS Country FROM tblauthor;";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
DataTable dt = new DataTable();
SQLiteDataAdapter da = new SQLiteDataAdapter(myCommand);
da.Fill(dt);
dataGridView1.DataSource = dt;
db.CloseConnection();
// MessageBox.Show("Rows Added: {0}", result.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void gridload()
{
try
{
string query = "SELECT desigid As DesignationID, designation As Designation, descreiption As Description FROM tbldesignation;";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
DataTable dt = new DataTable();
SQLiteDataAdapter da = new SQLiteDataAdapter(myCommand);
da.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.Columns[0].Width = 80;
dataGridView1.Columns[1].Width = 120;
db.CloseConnection();
// MessageBox.Show("Rows Added: {0}", result.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void gridload()
{
try
{
string query = "SELECT tblemp.empid AS EmployeeID, tblemp.name AS Name, tblemp.address AS Address, tblemp.nic AS NIC, tblemp.age AS Age,tblemp.hiredate AS HireDate, tblemp.status AS Status, tblemp.contactNo AS ContactNo, tbldesignation.designation AS Designation, lbldept.deptname AS Section FROM tbldesignation INNER JOIN (lbldept INNER JOIN tblemp ON lbldept.deptid = tblemp.deptid) ON tbldesignation.desigid = tblemp.desigid;";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
dt = new DataTable();
da = new SQLiteDataAdapter(myCommand);
da.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.EnableHeadersVisualStyles = false;
dataGridView1.ColumnHeadersDefaultCellStyle.BackColor = Color.LightGray;
dataGridView1.Columns[0].Width = 45;
dataGridView1.Columns[1].Width = 130;
dataGridView1.Columns[2].Width = 50;
dataGridView1.Columns[3].Width = 50;
dataGridView1.Columns[4].Width = 50;
dataGridView1.Columns[5].Width = 50;
dataGridView1.Columns[6].Width = 50;
dataGridView1.Columns[7].Width = 50;
dataGridView1.Columns[8].Width = 95;
dataGridView1.Columns[9].Width = 85;
db.CloseConnection();
// MessageBox.Show("Rows Added: {0}", result.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//get EmployeeID
private string EmployeeID(string employeeText)
{
try
{
database db = new database();
string query = "SELECT empid FROM tblemp where name='" + employeeText + "'";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
string result = Convert.ToString(myCommand.ExecuteScalar());
db.CloseConnection();
//MessageBox.Show("Rows Added: {0}", result.ToString());
return(result);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
//get designname
private string designationName(int designationID)
{
try
{
database db = new database();
string query = "SELECT designation FROM tbldesignation where desigid='" + designationID + "'";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
string result = Convert.ToString(myCommand.ExecuteScalar());
db.CloseConnection();
//MessageBox.Show("Rows Added: {0}", result.ToString());
return(result);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
private void deleteBook(int bookID)
{
try
{
database db = new database();
string query = "delete from tblbook where bookid='" + bookID + "'";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
// string result = Convert.ToString(myCommand.ExecuteScalar());
myCommand.ExecuteScalar();
db.CloseConnection();
//MessageBox.Show("Rows Added: {0}", result.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//get CAN
private string lastCAN()
{
try
{
database db = new database();
string query = "select city from tblbook order by bookid desc limit 1";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
string result = Convert.ToString(myCommand.ExecuteScalar());
db.CloseConnection();
//MessageBox.Show("Rows Added: {0}", result.ToString());
return(result);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
private void ReportPublisherWise_Load(object sender, EventArgs e)
{
try
{
database db = new database();
string query = "SELECT name FROM tblpublisher;";
db.OpenConnection();
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
SQLiteDataReader rdr = myCommand.ExecuteReader();
while (rdr.Read())
{
comboBox1.Items.Add(rdr.GetValue(0).ToString());
}
db.CloseConnection();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
comboBox1.SelectedIndex = 0;
}
private int rowsCount()
{
try
{
database db = new database();
string query = "SELECT Count(*) FROM tblissued";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
int result = Convert.ToInt32(myCommand.ExecuteScalar());
db.CloseConnection();
//MessageBox.Show("Rows Added: {0}", result.ToString());
return(result);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(0);
}
}
//search
private void textBox1_KeyUp(object sender, KeyEventArgs e)
{
try
{
string query = "SELECT desigid As DesignationID, designation As Designation, descreiption As Description FROM tbldesignation WHERE designation like('%" + textBox1.Text + "%')";
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
DataTable dt = new DataTable();
SQLiteDataAdapter da = new SQLiteDataAdapter(myCommand);
da.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.Columns[0].Width = 80;
dataGridView1.Columns[1].Width = 130;
db.CloseConnection();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void ReportAuthorWise_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'databaseDataSet.tblauthor' table. You can move, or remove it, as needed.
this.tblauthorTableAdapter.Fill(this.databaseDataSet.tblauthor);
try
{
database db = new database();
string query = "SELECT name FROM tblauthor";
db.OpenConnection();
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
SQLiteDataReader rdr = myCommand.ExecuteReader();
while (rdr.Read())
{
comboBox1.Items.Add(rdr.GetValue(0).ToString());
}
db.CloseConnection();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
comboBox1.SelectedIndex = 0;
}
//search button
private void button1_Click(object sender, EventArgs e)
{
try
{
string query;
if (comboBox1.SelectedIndex == 0)//issueId
{
query = "SELECT tblissued.issuedid AS ID, tblissued.issueddaate AS IssueDate, tblissued.returndate AS ReturnDate, tblissued.qty AS Quantity, tblbook.city AS CAN, tblbook.Name AS BookName, tblbook.Edition AS Edition, tblemp.name AS Employee, lbldept.deptname AS Department, tblissued.status AS Status, tblcatagory.Desc AS Category, tblbook.oldaccessionno AS AccNo, tblbook.bookid AS BookID FROM lbldept INNER JOIN ((tblemp INNER JOIN (tblbook INNER JOIN tblissued ON tblbook.bookid=tblissued.bookid) ON tblemp.empid=tblissued.empid) INNER JOIN tblcatagory ON tblbook.catagoryid=tblcatagory.catagoryid) ON lbldept.deptid=tblemp.deptid WHERE tblissued.issuedid ";
}
else if (comboBox1.SelectedIndex == 1)//bookName
{
query = "SELECT tblissued.issuedid AS ID, tblissued.issueddaate AS IssueDate, tblissued.returndate AS ReturnDate, tblissued.qty AS Quantity, tblbook.city AS CAN, tblbook.Name AS BookName, tblbook.Edition AS Edition, tblemp.name AS Employee, lbldept.deptname AS Department, tblissued.status AS Status, tblcatagory.Desc AS Category, tblbook.oldaccessionno AS AccNo, tblbook.bookid AS BookID FROM lbldept INNER JOIN ((tblemp INNER JOIN (tblbook INNER JOIN tblissued ON tblbook.bookid=tblissued.bookid) ON tblemp.empid=tblissued.empid) INNER JOIN tblcatagory ON tblbook.catagoryid=tblcatagory.catagoryid) ON lbldept.deptid=tblemp.deptid WHERE tblbook.Name ";
}
else if (comboBox1.SelectedIndex == 2)//accNo
{
query = "SELECT tblissued.issuedid AS ID, tblissued.issueddaate AS IssueDate, tblissued.returndate AS ReturnDate, tblissued.qty AS Quantity, tblbook.city AS CAN, tblbook.Name AS BookName, tblbook.Edition AS Edition, tblemp.name AS Employee, lbldept.deptname AS Department, tblissued.status AS Status, tblcatagory.Desc AS Category, tblbook.oldaccessionno AS AccNo, tblbook.bookid AS BookID FROM lbldept INNER JOIN ((tblemp INNER JOIN (tblbook INNER JOIN tblissued ON tblbook.bookid=tblissued.bookid) ON tblemp.empid=tblissued.empid) INNER JOIN tblcatagory ON tblbook.catagoryid=tblcatagory.catagoryid) ON lbldept.deptid=tblemp.deptid WHERE tblbook.oldaccessionno ";
}
else if (comboBox1.SelectedIndex == 3)//can
{
query = "SELECT tblissued.issuedid AS ID, tblissued.issueddaate AS IssueDate, tblissued.returndate AS ReturnDate, tblissued.qty AS Quantity, tblbook.city AS CAN, tblbook.Name AS BookName, tblbook.Edition AS Edition, tblemp.name AS Employee, lbldept.deptname AS Department, tblissued.status AS Status, tblcatagory.Desc AS Category, tblbook.oldaccessionno AS AccNo, tblbook.bookid AS BookID FROM lbldept INNER JOIN ((tblemp INNER JOIN (tblbook INNER JOIN tblissued ON tblbook.bookid=tblissued.bookid) ON tblemp.empid=tblissued.empid) INNER JOIN tblcatagory ON tblbook.catagoryid=tblcatagory.catagoryid) ON lbldept.deptid=tblemp.deptid WHERE tblbook.city ";
}
else if (comboBox1.SelectedIndex == 4)//employeeName
{
query = "SELECT tblissued.issuedid AS ID, tblissued.issueddaate AS IssueDate, tblissued.returndate AS ReturnDate, tblissued.qty AS Quantity, tblbook.city AS CAN, tblbook.Name AS BookName, tblbook.Edition AS Edition, tblemp.name AS Employee, lbldept.deptname AS Department, tblissued.status AS Status, tblcatagory.Desc AS Category, tblbook.oldaccessionno AS AccNo, tblbook.bookid AS BookID FROM lbldept INNER JOIN ((tblemp INNER JOIN (tblbook INNER JOIN tblissued ON tblbook.bookid=tblissued.bookid) ON tblemp.empid=tblissued.empid) INNER JOIN tblcatagory ON tblbook.catagoryid=tblcatagory.catagoryid) ON lbldept.deptid=tblemp.deptid WHERE tblemp.name ";
}
else //for 5 status
{
query = "SELECT tblissued.issuedid AS ID, tblissued.issueddaate AS IssueDate, tblissued.returndate AS ReturnDate, tblissued.qty AS Quantity, tblbook.city AS CAN, tblbook.Name AS BookName, tblbook.Edition AS Edition, tblemp.name AS Employee, lbldept.deptname AS Department, tblissued.status AS Status, tblcatagory.Desc AS Category, tblbook.oldaccessionno AS AccNo, tblbook.bookid AS BookID FROM lbldept INNER JOIN ((tblemp INNER JOIN (tblbook INNER JOIN tblissued ON tblbook.bookid=tblissued.bookid) ON tblemp.empid=tblissued.empid) INNER JOIN tblcatagory ON tblbook.catagoryid=tblcatagory.catagoryid) ON lbldept.deptid=tblemp.deptid WHERE tblissued.status ";
}
string filterString;
switch (comboBox2.SelectedIndex)
{
case 0: //contains
filterString = "like('%" + textBox1.Text + "%')";
break;
case 1: //is equal to
filterString = "= '" + textBox1.Text + "'";
break;
case 2: //not equal to
filterString = "<> '" + textBox1.Text + "'";
break;
case 3: //is greater than
filterString = "> '" + textBox1.Text + "'";
break;
case 4: //is greater than or equal to
filterString = ">= '" + textBox1.Text + "'";
break;
case 5: //is less than
filterString = "< '" + textBox1.Text + "'";
break;
default: // 6 //is less than or equal to
filterString = "<= '" + textBox1.Text + "'";
break;
}
ManageIssue missue = (ManageIssue)Application.OpenForms["ManageIssue"];
query = query + filterString;
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
//mbooks.dataGridView1.DataSource = null;
// mbooks.dataGridView1.Rows.Clear();
DataTable dt = new DataTable();
SQLiteDataAdapter da = new SQLiteDataAdapter(myCommand);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
missue.dataGridView1.DataSource = dt;
missue.recordText.Text = "";
this.Close();
}
else
{
MessageBox.Show("No Records Found", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.Close();
}
db.CloseConnection();
// MessageBox.Show(Convert.ToString(result));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//search
private void button1_Click(object sender, EventArgs e)
{
try
{
string query;
if (comboBox1.SelectedIndex == 0)//bookId
{
query = "SELECT tblbook.bookid AS ID , tblbook.oldaccessionno AS AccNo, tblbook.City AS CAN,tblbook.Typeid AS CallNo,tblbook.Regid AS CupboardNo, tblbook.Name AS Book_Name, tblauthor.name AS Author_Name, tblpublisher.name AS Publisher_Name, tblbook.Edition, tblcatagory.Desc AS Book_Catagory,tblbook.noofbooks AS Quantity, tblbook.Status FROM ((tblauthor INNER JOIN tblbook ON tblauthor.authorid = tblbook.authorid) INNER JOIN tblcatagory ON tblbook.catagoryid = tblcatagory.catagoryid) INNER JOIN tblpublisher ON tblbook.pubid = tblpublisher.pubid WHERE tblbook.bookid ";
}
else if (comboBox1.SelectedIndex == 1)//bookName
{
query = "SELECT tblbook.bookid AS ID , tblbook.oldaccessionno AS AccNo, tblbook.City AS CAN,tblbook.Typeid AS CallNo,tblbook.Regid AS CupboardNo, tblbook.Name AS Book_Name, tblauthor.name AS Author_Name, tblpublisher.name AS Publisher_Name, tblbook.Edition, tblcatagory.Desc AS Book_Catagory,tblbook.noofbooks AS Quantity, tblbook.Status FROM ((tblauthor INNER JOIN tblbook ON tblauthor.authorid = tblbook.authorid) INNER JOIN tblcatagory ON tblbook.catagoryid = tblcatagory.catagoryid) INNER JOIN tblpublisher ON tblbook.pubid = tblpublisher.pubid WHERE tblbook.Name ";
}
else if (comboBox1.SelectedIndex == 2)//accNo
{
query = "SELECT tblbook.bookid AS ID , tblbook.oldaccessionno AS AccNo, tblbook.City AS CAN,tblbook.Typeid AS CallNo,tblbook.Regid AS CupboardNo, tblbook.Name AS Book_Name, tblauthor.name AS Author_Name, tblpublisher.name AS Publisher_Name, tblbook.Edition, tblcatagory.Desc AS Book_Catagory,tblbook.noofbooks AS Quantity, tblbook.Status FROM ((tblauthor INNER JOIN tblbook ON tblauthor.authorid = tblbook.authorid) INNER JOIN tblcatagory ON tblbook.catagoryid = tblcatagory.catagoryid) INNER JOIN tblpublisher ON tblbook.pubid = tblpublisher.pubid WHERE tblbook.oldaccessionno ";
}
else if (comboBox1.SelectedIndex == 3)//can
{
query = "SELECT tblbook.bookid AS ID , tblbook.oldaccessionno AS AccNo, tblbook.City AS CAN,tblbook.Typeid AS CallNo,tblbook.Regid AS CupboardNo, tblbook.Name AS Book_Name, tblauthor.name AS Author_Name, tblpublisher.name AS Publisher_Name, tblbook.Edition, tblcatagory.Desc AS Book_Catagory,tblbook.noofbooks AS Quantity, tblbook.Status FROM ((tblauthor INNER JOIN tblbook ON tblauthor.authorid = tblbook.authorid) INNER JOIN tblcatagory ON tblbook.catagoryid = tblcatagory.catagoryid) INNER JOIN tblpublisher ON tblbook.pubid = tblpublisher.pubid WHERE tblbook.City ";
}
else if (comboBox1.SelectedIndex == 4)//callNo
{
query = "SELECT tblbook.bookid AS ID , tblbook.oldaccessionno AS AccNo, tblbook.City AS CAN,tblbook.Typeid AS CallNo,tblbook.Regid AS CupboardNo, tblbook.Name AS Book_Name, tblauthor.name AS Author_Name, tblpublisher.name AS Publisher_Name, tblbook.Edition, tblcatagory.Desc AS Book_Catagory,tblbook.noofbooks AS Quantity, tblbook.Status FROM ((tblauthor INNER JOIN tblbook ON tblauthor.authorid = tblbook.authorid) INNER JOIN tblcatagory ON tblbook.catagoryid = tblcatagory.catagoryid) INNER JOIN tblpublisher ON tblbook.pubid = tblpublisher.pubid WHERE tblbook.Typeid ";
}
else if (comboBox1.SelectedIndex == 5)//cupboard
{
query = "SELECT tblbook.bookid AS ID , tblbook.oldaccessionno AS AccNo, tblbook.City AS CAN,tblbook.Typeid AS CallNo,tblbook.Regid AS CupboardNo, tblbook.Name AS Book_Name, tblauthor.name AS Author_Name, tblpublisher.name AS Publisher_Name, tblbook.Edition, tblcatagory.Desc AS Book_Catagory,tblbook.noofbooks AS Quantity, tblbook.Status FROM ((tblauthor INNER JOIN tblbook ON tblauthor.authorid = tblbook.authorid) INNER JOIN tblcatagory ON tblbook.catagoryid = tblcatagory.catagoryid) INNER JOIN tblpublisher ON tblbook.pubid = tblpublisher.pubid WHERE tblbook.Regid ";
}
else if (comboBox1.SelectedIndex == 6)//author
{
query = "SELECT tblbook.bookid AS ID , tblbook.oldaccessionno AS AccNo, tblbook.City AS CAN,tblbook.Typeid AS CallNo,tblbook.Regid AS CupboardNo, tblbook.Name AS Book_Name, tblauthor.name AS Author_Name, tblpublisher.name AS Publisher_Name, tblbook.Edition, tblcatagory.Desc AS Book_Catagory,tblbook.noofbooks AS Quantity, tblbook.Status FROM ((tblauthor INNER JOIN tblbook ON tblauthor.authorid = tblbook.authorid) INNER JOIN tblcatagory ON tblbook.catagoryid = tblcatagory.catagoryid) INNER JOIN tblpublisher ON tblbook.pubid = tblpublisher.pubid WHERE tblauthor.name ";
}
else if (comboBox1.SelectedIndex == 7)//publisher
{
query = "SELECT tblbook.bookid AS ID , tblbook.oldaccessionno AS AccNo, tblbook.City AS CAN,tblbook.Typeid AS CallNo,tblbook.Regid AS CupboardNo, tblbook.Name AS Book_Name, tblauthor.name AS Author_Name, tblpublisher.name AS Publisher_Name, tblbook.Edition, tblcatagory.Desc AS Book_Catagory,tblbook.noofbooks AS Quantity, tblbook.Status FROM ((tblauthor INNER JOIN tblbook ON tblauthor.authorid = tblbook.authorid) INNER JOIN tblcatagory ON tblbook.catagoryid = tblcatagory.catagoryid) INNER JOIN tblpublisher ON tblbook.pubid = tblpublisher.pubid WHERE tblpublisher.name ";
}
else if (comboBox1.SelectedIndex == 8)//edition
{
query = "SELECT tblbook.bookid AS ID , tblbook.oldaccessionno AS AccNo, tblbook.City AS CAN,tblbook.Typeid AS CallNo,tblbook.Regid AS CupboardNo, tblbook.Name AS Book_Name, tblauthor.name AS Author_Name, tblpublisher.name AS Publisher_Name, tblbook.Edition, tblcatagory.Desc AS Book_Catagory,tblbook.noofbooks AS Quantity, tblbook.Status FROM ((tblauthor INNER JOIN tblbook ON tblauthor.authorid = tblbook.authorid) INNER JOIN tblcatagory ON tblbook.catagoryid = tblcatagory.catagoryid) INNER JOIN tblpublisher ON tblbook.pubid = tblpublisher.pubid WHERE tblbook.Edition ";
}
else if (comboBox1.SelectedIndex == 9)//category
{
query = "SELECT tblbook.bookid AS ID , tblbook.oldaccessionno AS AccNo, tblbook.City AS CAN,tblbook.Typeid AS CallNo,tblbook.Regid AS CupboardNo, tblbook.Name AS Book_Name, tblauthor.name AS Author_Name, tblpublisher.name AS Publisher_Name, tblbook.Edition, tblcatagory.Desc AS Book_Catagory,tblbook.noofbooks AS Quantity, tblbook.Status FROM ((tblauthor INNER JOIN tblbook ON tblauthor.authorid = tblbook.authorid) INNER JOIN tblcatagory ON tblbook.catagoryid = tblcatagory.catagoryid) INNER JOIN tblpublisher ON tblbook.pubid = tblpublisher.pubid WHERE tblcatagory.Desc ";
}
else if (comboBox1.SelectedIndex == 10)//quantity
{
query = "SELECT tblbook.bookid AS ID , tblbook.oldaccessionno AS AccNo, tblbook.City AS CAN,tblbook.Typeid AS CallNo,tblbook.Regid AS CupboardNo, tblbook.Name AS Book_Name, tblauthor.name AS Author_Name, tblpublisher.name AS Publisher_Name, tblbook.Edition, tblcatagory.Desc AS Book_Catagory,tblbook.noofbooks AS Quantity, tblbook.Status FROM ((tblauthor INNER JOIN tblbook ON tblauthor.authorid = tblbook.authorid) INNER JOIN tblcatagory ON tblbook.catagoryid = tblcatagory.catagoryid) INNER JOIN tblpublisher ON tblbook.pubid = tblpublisher.pubid WHERE tblbook.noofbooks ";
}
else //for 11 status
{
query = "SELECT tblbook.bookid AS ID , tblbook.oldaccessionno AS AccNo, tblbook.City AS CAN,tblbook.Typeid AS CallNo,tblbook.Regid AS CupboardNo, tblbook.Name AS Book_Name, tblauthor.name AS Author_Name, tblpublisher.name AS Publisher_Name, tblbook.Edition, tblcatagory.Desc AS Book_Catagory,tblbook.noofbooks AS Quantity, tblbook.Status FROM ((tblauthor INNER JOIN tblbook ON tblauthor.authorid = tblbook.authorid) INNER JOIN tblcatagory ON tblbook.catagoryid = tblcatagory.catagoryid) INNER JOIN tblpublisher ON tblbook.pubid = tblpublisher.pubid WHERE tblbook.Status ";
}
string filterString;
switch (comboBox2.SelectedIndex)
{
case 0: //contains
filterString = "like('%" + textBox1.Text + "%')";
break;
case 1: //is equal to
filterString = "= '" + textBox1.Text + "'";
break;
case 2: //not equal to
filterString = "<> '" + textBox1.Text + "'";
break;
case 3: //is greater than
filterString = "> '" + textBox1.Text + "'";
break;
case 4: //is greater than or equal to
filterString = ">= '" + textBox1.Text + "'";
break;
case 5: //is less than
filterString = "< '" + textBox1.Text + "'";
break;
default: // 6 //is less than or equal to
filterString = "<= '" + textBox1.Text + "'";
break;
}
ManageBooks mbooks = (ManageBooks)Application.OpenForms["ManageBooks"];
query = query + filterString;
SQLiteCommand myCommand = new SQLiteCommand(query, db.myConnection);
db.OpenConnection();
var result = myCommand.ExecuteNonQuery();
//mbooks.dataGridView1.DataSource = null;
// mbooks.dataGridView1.Rows.Clear();
DataTable dt = new DataTable();
SQLiteDataAdapter da = new SQLiteDataAdapter(myCommand);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
mbooks.dataGridView1.DataSource = dt;
mbooks.recordText.Text = "";
this.Close();
}
else
{
MessageBox.Show("No Records Found", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.Close();
}
db.CloseConnection();
// MessageBox.Show(Convert.ToString(result));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}