Click here to Skip to main content
15,885,855 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
here's the code and query i had used for Borrowing items System,
here you can borrow an items form database.TableA.
note: the borrowing items System is working

C#
//and here you can put a value to subtract the value of stock from database.TableA 
and the value u use will be inserting directly to stock.database.tableB

private void brwBtn_Click(object sender, EventArgs e)
        {
            try
            {
                int brValue = 0;
                brValue = int.Parse(stockTxt.Text) - int.Parse(brStockTxt.Text);

                string modConnection = "datasource=localhost;port=3306;username=root;password=1234";
                string modQuery = "UPDATE Database.tableA SET Id = '" + this.idTxt.Text + "',Items = '" + this.itemsTxt.Text + "',Stock = '" + brValue + "' Where Id = '" +this.idTxt.Text + "';";
                    
                MySqlConnection modData = new MySqlConnection(modConnection);
                MySqlCommand modCommandData = new MySqlCommand(modQuery, modData);
                MySqlDataReader MyReader;

                modData.Open();
                MyReader = modCommandData.ExecuteReader();
                MessageBox.Show("Registered Data..");

                this.Hide();
                while (MyReader.Read())
                {

                }
  
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            ////////////////////////////////////// Inserting New Items in database.tableB from database.tableA.
            string regString = "datasource=localhost;port=3306;username=root;password=1234";
            string regQuery = "INSERT INTO databse.tableB(Id,Items,Stock) VALUES ('" + this.idTxt.Text + "','" + this.itemsTxt.Text + "','" + this.brStockTxt.Text + "');";
            MySqlConnection regData = new MySqlConnection(regString);
            MySqlCommand regCommandData = new MySqlCommand(regQuery, regData);
            MySqlDataReader regReader;

            try
            {
                regData.Open();
                regReader = regCommandData.ExecuteReader();
                MessageBox.Show("Registered Data..");
                this.Hide();
                while (regReader.Read())
                {
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


and here was the Library Return Items System should works. but the problem is i couldn't figure out, what code should will be use. anyone would like to help me?

C#
//here's the another form called return form
 public Form3()
        {
            InitializeComponent();
            load_table();
        }
        //load_table to automatically loaded a data in datagridview from database.TableB 
         void load_table()
        {
            string regString = "datasource=localhost;port=3306;username=root;password=1234";
            MySqlConnection regData = new MySqlConnection(regString);
            MySqlCommand regCommandData = new MySqlCommand("Select * From database.tableB;", regData);

            try
            {
                MySqlDataAdapter sda = new MySqlDataAdapter();
                sda.SelectCommand = regCommandData;
                DataTable dbDatabase = new DataTable();
                sda.Fill(dbDatabase);
                BindingSource bSource = new BindingSource();

                bSource.DataSource = dbDatabase;
                dataGridView1.DataSource = bSource;
                sda.Update(dbDatabase);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

    //If you choose or selecting an items from datagridview it will show the data directly in a given textbox

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            this.rIdTxt.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
            this.rItemsTxt.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
            this.rStockTxt.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
        }

//this is the return items button
//i added a delete button so if you click the return button the items you chosen from the datagridview that came from database.TableB will be deleted

        private void rBtn_Click(object sender, EventArgs e)
        {
            try
            {
                string modConnection = "datasource=localhost;port=3306;username=root;password=1234";
                string modQuery = "Delete from database.tableB Where Id = '" + this.rIdTxt.Text + "';";

                MySqlConnection modData = new MySqlConnection(modConnection);
                MySqlCommand modCommandData = new MySqlCommand(modQuery, modData);
                MySqlDataReader MyReader;

                modData.Open();
                MyReader = modCommandData.ExecuteReader();
                MessageBox.Show("Registered Data..");

                this.Hide();
                while (MyReader.Read())
                {

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            //Here's the problem what code should i use to perform a addition
the value i return from database.tableB to add it to database.TableA??

//lets say there's 3 available stock for BookA borrow(database.TableA) and i borrow 2 stocks from it, so the borrow(database.tableA) stock remaining will be 1, and the 2 stocks i borrowed will be added in return(database.tableB) with name BookA and the 2 stocks value i borrowed, and from return(database.tableB) i return the BookA with 2stocks that i borrowed, and added it again database.tableA

//can anyone help me please???

            try
            {
                string modConnection = "datasource=localhost;port=3306;username=root;password=1234";
                string modQuery = "UPDATE database.tableA SET Id = '" + this.rIdTxt.Text + "',Items = '" + this.rItemsTxt.Text + "',Stock = '" + (rStockTxt.Text+??????) + "' Where Id = '" + this.rIdTxt.Text + "';";

                MySqlConnection modData = new MySqlConnection(modConnection);
                MySqlCommand modCommandData = new MySqlCommand(modQuery, modData);
                MySqlDataReader MyReader;

                modData.Open();
                MyReader = modCommandData.ExecuteReader();
                MessageBox.Show("Registered Data..");

                this.Hide();
                while (MyReader.Read())
                {

                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }



        }
Posted
Updated 11-Jun-15 19:08pm
v3
Comments
Sinisa Hajnal 12-Jun-15 2:44am    
Either do the addition before deleting or remember the quantity to return before deleting and then add it to A.

In both cases, it is CRITICAL to do it within a transaction, otherwise you will get in a situation where you will perform one operation while the other will fail (for whatever reason) and you will have wrong quantities in both tables.

I would do all operations in a single stored procedure so the code would just have to send ID into it and it performs all the necessary steps.

1 solution

Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

Then...change your table names: "tableA" and "tableB" are not descriptive of what the tables contain, which makes it a lot harder to work out what is happening.
Consider having three tables:
Books
Id         can be INT, IDENTITY
ISBN
Title
Author

Users
ID         can be INT, IDENTITY
Name
Address

Loans
ID         can be INT, IDENTITY
BookID
UserID
LoanDate   DATETIME
DueDate    DATETIME
ReturnDate DATETIME NULLABLE

Each book has a unique ID, but "identical books" share an ISBN - so you track the individual book rather than a generic "one of these" type stack.

From this, you can work out everything you need using simple JOIN statements - and you don't need to delete records: you just update them so you can "trace the history" of a book as well. So if I borrow a book and find the middle section has been torn out, you can look back in the records to find out who to blame!

Have a think about it - it's a lot easier to use than you might think, and while it means throwing away your existing code and starting again I have to say that looking at your code that wouldn't be a bad idea...
Set up a single static connection string - that way if it changes (and it does) you don't have to change it in many places (the best way is to use a configuration file so you app doesn't have to be changed when the connections string does).
Close and Dispose of your Connections, Commands, Readers and so forth. use a using block is the simplest way.
Don't use ExecuteReader for UPDATE commands - they don't return anything. USe ExecuteNonQuery instead.
Be consistent in your naming conventions: modData and modCommandData are ok (though most people tend to use com and cmd respectively) but then you go with MyReader which doesn't fit.
Why are you Hiding a form? This is an unusual thing to do...particularly for a library based system...

I know this is your homework, and that you are a beginner - and that is sounds like I am being harsh on you - but this doesn't look like you sat down and thought about what you have to do - it looks like you leapt in and started coding from the beginning instead! :laugh: If you do stop and think, you will get a system that is easier to use and easier to code - honest!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900