/// <summary>
        /// display author details and assign author id for navigation
        /// </summary>
        private void LoadAuthorDetails()
        {
            errProvider.Clear();

            string sqlAuthorById = $"SELECT * FROM Author WHERE AuthorId = '{currentAuthorId}' ORDER BY FirstName, LastName";

            string sqlNav = $@"
                                SELECT 
	                                (SELECT TOP(1) AuthorId FROM Author ORDER BY FirstName, LastName) AS FirstID,
	                                (SELECT TOP(1) AuthorId FROM Author ORDER BY FirstName DESC, LastName DESC) AS LastID,
	                                q.PreviousID,
	                                q.NextID,
	                                q.RowNumber,
	                                (SELECT COUNT(*) FROM Author) AS NumOfAuthors
                                FROM 
	                                (SELECT AuthorId, 
			                                FirstName,
                                            LastName,
			                                LEAD(AuthorId) OVER (ORDER BY FirstName, LastName) AS NextID,
			                                LAG(AuthorId) OVER (ORDER BY FirstName, LastName) AS PreviousID,
			                                ROW_NUMBER() OVER (ORDER BY FirstName, LastName) As RowNumber
		                                FROM Author
	                                ) AS q
                                WHERE q.AuthorId = '{currentAuthorId}'
                                ORDER BY FirstName, LastName";

            sqlNav = DataAccess.SQLCleaner(sqlNav);

            string[] sqlStatements = new string[] { sqlAuthorById, sqlNav };
            DataSet  ds            = DataAccess.GetData(sqlStatements);

            DataRow selectedAuthor = ds.Tables["Table"].Rows[0];

            txtFirstName.Text     = selectedAuthor["FirstName"].ToString();
            txtLastName.Text      = selectedAuthor["LastName"].ToString();
            txtMiddleName.Text    = selectedAuthor["MiddleName"].ToString();
            txtContactNumber.Text = selectedAuthor["ContactNumber"].ToString();

            switch (selectedAuthor["Gender"])
            {
            case "M":
                rdoMale.Checked = true;
                break;

            case "F":
                rdoFemale.Checked = true;
                break;

            default:
                rdoNonBinary.Checked = true;
                break;
            }

            txtNumOfBooks.Text            = selectedAuthor["NumOfBooks"].ToString();
            cmbMainCategory.SelectedValue = selectedAuthor["MainCategory"];
            txtAward.Text = selectedAuthor["Award"].ToString();


            numberOfAuthors  = Convert.ToInt32(ds.Tables["Table1"].Rows[0]["NumOfAuthors"]);
            firstAuthorId    = Convert.ToInt32(ds.Tables["Table1"].Rows[0]["FirstID"]);
            lastAuthorId     = Convert.ToInt32(ds.Tables["Table1"].Rows[0]["LastID"]);
            previousAuthorId = ds.Tables["Table1"].Rows[0]["PreviousID"] != DBNull.Value ? Convert.ToInt32(ds.Tables["Table1"].Rows[0]["PreviousID"]) : (int?)null;
            nextAuthorId     = ds.Tables["Table1"].Rows[0]["NextID"] != DBNull.Value ? Convert.ToInt32(ds.Tables["Table1"].Rows[0]["NextID"]) : (int?)null;
            currentRecord    = Convert.ToInt32(ds.Tables["Table1"].Rows[0]["RowNumber"]);

            GetLatestBooks();
            DisplayCurrentPosition();
        }
コード例 #2
0
ファイル: returned.cs プロジェクト: BryanOrtega/Sample
        private void btn_borrow_Click(object sender, EventArgs e)
        {
            //button for borrowing
            String sqlconstring = "Data Source = LOCALHOST; Initial Catalog = it_2d; username = root; password = ''";
            MySqlConnection sqlconnect = new MySqlConnection(sqlconstring);

            MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter();
            DataSet DS = new DataSet();
            sqlconnect.Open();
            //check if the user already borrowed an item that needed to be returned
            MySqlCommand check_User_Name = new MySqlCommand("SELECT * FROM tbl_borrow WHERE (`b_no` = '" + txt_bnum.Text + "') ", sqlconnect);
            if (txt_bnum.Text == "" || txt_id.Text == "" || txt_num.Text == "")
            {
                MessageBox.Show("Incomplete Fields", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else {
                if (check_User_Name.ExecuteScalar() != null)
                {
                    string UserExist = check_User_Name.ExecuteScalar().ToString();

                    if (UserExist != null)
                    {

                        DateTime time = DateTime.Now;
                        string format = "yyyy-MM-dd HH:mm:ss";
                        var mytime = time.ToString(format);
                        MySqlCommand sqlcommand = new MySqlCommand();
                        //update the copies deduction
                        sqlcommand.CommandText = "UPDATE `tbl_itemlist` SET `b_copies` = `b_copies` + 1 where `b_no` = '" + txt_bnum.Text + "'";
                        sqlcommand.CommandType = CommandType.Text;
                        sqlcommand.Connection = sqlconnect;
                        sqlcommand.ExecuteNonQuery();
                        sqlDataAdapter.SelectCommand = sqlcommand;
                        //update tbl_penalty for record
                        sqlcommand.CommandText = "update tbl_penalty set d_returned='" + this.dtp_returned.Text + "' where b_no='" + txt_bnum.Text + "'AND `u_un` = '" + txt_num.Text + "';";
                        sqlcommand.CommandType = CommandType.Text;
                        sqlcommand.Connection = sqlconnect;
                        sqlcommand.ExecuteNonQuery();
                        sqlDataAdapter.SelectCommand = sqlcommand;
                        //insert the transaction to return
                        sqlcommand.CommandText = "INSERT INTO `it_2d`.`tbl_return` (`b_no`, `u_un`,`d_returned`) VALUES ('" + txt_bnum.Text + "' ,'" + txt_num.Text + "','" + this.dtp_returned.Text + "');";
                        sqlcommand.CommandType = CommandType.Text;
                        sqlcommand.Connection = sqlconnect;
                        sqlcommand.ExecuteNonQuery();
                        sqlDataAdapter.SelectCommand = sqlcommand;
                        //delete the user account
                        sqlcommand.CommandText = "DELETE FROM `tbl_borrow` WHERE `u_un` = ('" + txt_num.Text + "') ";
                        sqlcommand.CommandType = CommandType.Text;
                        sqlcommand.Connection = sqlconnect;
                        sqlcommand.ExecuteNonQuery();
                        sqlDataAdapter.SelectCommand = sqlcommand;
                        //display and refresh the datagridview
                        sqlcommand.CommandText = "SELECT `b_no` as 'Accession No.', `b_title` as 'Title', `b_author` as 'Author', `u_un` as 'Account No.', `d_borrowed` as 'Date Borrowed' FROM `tbl_borrow` WHERE 1 ";
                        sqlcommand.CommandType = CommandType.Text;
                        sqlcommand.Connection = sqlconnect;
                        sqlcommand.ExecuteNonQuery();
                        sqlDataAdapter.SelectCommand = sqlcommand;
                        sqlDataAdapter.Fill(DS, "KAIBIGAN");
                        dataGridView1.DataSource = DS;
                        dataGridView1.DataMember = "KAIBIGAN";
                        //insert the transaction to activitylog
                        sqlcommand.CommandText = "INSERT INTO `it_2d`.`tbl_activitylog` (`librarian_id`, `activity`,`account_no`,`date_activity`) VALUES ('" + txt_id.Text + "' ,' Return Item','" + txt_num.Text + "','" + mytime + "');";
                        sqlcommand.CommandType = CommandType.Text;
                        sqlcommand.Connection = sqlconnect;
                        sqlcommand.ExecuteNonQuery();
                        sqlDataAdapter.SelectCommand = sqlcommand;

                        MessageBox.Show("Item Returned", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);

                        txt_num.Clear();
                        txt_bnum.Clear();
                        txt_borrowed.Clear();


                    }

                    else
                    {
                        //accession no. doesnt exist
                        MessageBox.Show("Invalid accession no.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    }
                }

            }
        }