I'm creating a Reservation System and I set a default price for every product and here's the code:
const double price_fried = 120;
const double price_bbq = 100;
const double price_tbone = 150;
const double price_cf = 120;
const double price_coke = 60;
const double price_sprite = 60;
const double price_royal = 60;
const double price_fries = 80;
const double price_onion = 90;
I can already change the price in the DataGridView, the only problem is how can I get the data from that dgv so that when I change the value in the dgv the prices above changes too?
Here is the DataGridView:
public void LoadProducts()
{
int i = 0;
dgvProducts.Rows.Clear();
cm = new SqlCommand("SELECT ProductID,Price,Description FROM tbl_Products", con);
con.Open();
dr = cm.ExecuteReader();
while (dr.Read())
{
i ;
dgvProducts.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString());
}
dr.Close();
con.Close();
}
CodePudding user response：
I couldn't understand you very well but if you want to change the prices of the meals. Here is the code:
SqlConnection con;
SqlCommand com;
con = new SqlConnection(""); /You have to paste the database path between the quotation marks.
com = new SqlCommand;
con.Open;
com.Connection = con;
com.CommandText("Update tbl_Products Set PRICE = '200' where PRODUCTID = '1'");
In the last line you can change the Set PRICE ='...'(For Example you can make it Set PRICE = '300') and you can select the which meal's price will be changed from "where PRODUCTID = '...'"(For example you can make it where PRODUCTID = '2', so you can change the price of t-bone)
I hope I was explanatory. If you have any questions you can ask.
CodePudding user response：
I'd avoid going the route you're currently exploring. As John has said in the comments you're creating a rod for your own back; you don't realize it now, perhaps because it's all new and programming code that picks data out of a database line by line, column by column is still fun for you but it's a lot of work for little gain, and gets tedious quick when you've done it for the hundredth time. Fortunately we can get VS to write it all
The following needs to be done in a .net framework project. The designer has bugs in .net Core/5 so at the time of writing Microsoft have disabled it. You can use the designer in a .net fw project and run the code it generates in core if you want to import it
- Add a new DataSet type of file to your project. Give it a sensible name (not DataSet1)
- open it. Right click the surface of the designer and choose Add TableAdapter
- Go through the wizard adding a query of
SELECT ProductID,Price,Description FROM tbl_Products - Save the dataset
- Switch to the form designer, delete the existing DataGridView
- Activate the Data Sources panel (View menu>>Other Windows)
- Drag the node representing tbl_Products onto the form
- Run the app; everything is done - the data loads, you can edit the values, you can click save and the database gets updated
If you want to see the code VS wrote for this, it's in the DataSetNameHere.Designer.cs - it's long; you're better off just leaving it there and adding more queries to your program via the designer. In essence though, telling VS what query you want makes it generate a DataAdapter loaded with a SELECT query, a custom datatable that has exactly the same columns as the query returns and the related update/insert/delete queries to send data back to the database. These are all bundled up inside a class the designer creates called a TableAdapter . When you dragged the data sources node to the form it created a DataGridView, a BindingSource, a TableAdapter, a DataSet and a BindingNavigator. The grid is connected to the table inside the dataset via the BindingSource. The BindingSource does useful things like remember position (which is the current row) so that next and previous operations are possible and it ensures events are raised that cause the grid to update when the datatable inside the dataset is filled with data or its data is altered. The TableAdapter fills the data into the datatable, the grid updates to show it, the user edits the data which puts changes into the datatable and clicking Save icon invokes the tableadapter to send the changes back to the db.
Absolutely core to this whole concept is a programming notion called MVC; you have a Model that stores your data (the DataTable), you fill data into it and show the user a View of it (the grid) and you give the user some Control of it (also the grid, in edit mode). Keep those notions clear in your mind; if you want to do something programatically to the data you do it directly to the model. You don't write code that pulls it out of the View:
//yes
foreach(var row in yourDataSet.Products)
row.Price *= 1.1; //increase price by 10%
//no
foreach(var row in ProductsDataGridView.Rows) ...
You edit the model, the grid will refresh itself when the user clicks the increase prices by 10% button
The model of data becomes the price list you're asking about - throw all those consts away. If you want to know in code what price something is, get it from the datatable inside the dataset. If you were doing this as part of a grid that was making an Order entry with reference to the Product table prices you could put a DataGridView with a column that is a combo box that shows the name and price of the product but pushes the product ID into the ProductId column of the Orders table when the user selects the product they want
When designing a dataset, mostly I recommend you should add queries that have a WHERE clause. Each tableadapter can have multiple queries but they should select a consistent set of columns like:
SELECT * FROM person WHERE Id = @idSELECT * FROM person WHERE Name like @name
I should probably also point out that even though this dataset thing is a wonderful productivity boost, it's quite old tech now and we see it being used ever more rarely. Modern database aware apps tend to use entity framework, but the dataset designer does still work particularly well for technologies like Windows forms