Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Good day, i got several things here:</p> <p>1) <code>cmd.Parameters["Quantity"].Value = this.newVal.ToString();</code> conversion to string is not needed here, because the Value is an object. You already defined that it should be handled as integer.</p> <p>2) Replace the <code>?</code> within you query to <code>@Quantity</code>, so it will be filled by the query execution.</p> <p>3) When updating you don't need to execute it as a reader. use the <code>int numberOfRows = cmd.ExecuteNonQuery();</code> without the loop. It will update all items.</p> <p>4) You should execute the <code>if (textBoxQuantityContainer[index].Value != 0 &amp;&amp; textBoxQuantityContainer[index].Value &gt;= Convert.ToInt32(dReader["Quantity"].ToString())) {</code> when building the integers list, this way you are only updating the right quantities.</p> <p>If you only want to update certain rows, you'll have to expand your where clause:</p> <pre><code>cmd.Parameters.Add("MinimumQuantity", System.Data.OleDb.OleDbType.Integer).Value = minimumQuantity; string command = "UPDATE [Seranne] SET [Quantity]=@Quantity WHERE [Code] IN(" + string.Join(", ", integers) + ") AND [Quantity] &gt; @MinimumQuantity </code></pre> <p>The higest <strong>risk</strong> is: You assume that the order and count of the records are the same between your textBoxCodeContainer and the database.</p> <p><em>What is the relation between a textbox and a row. How do you know what textbox links to which row?</em></p> <p>I could give you a push in the right direction, if you show me some more code (like where/how is textBoxCodeContainer defined)</p> <hr> <p>UPDATE:</p> <hr> <p>I made some code the read and manipulate your database, this is <strong>not</strong> tested since i don't have any database here.</p> <p>I would create these classes, 1 is a data class <code>Product</code> and one is a Handler class <code>ProductHandler</code>.</p> <p>The data class only contains the data (not in presentation format) The data handler knows how to read and write them.</p> <pre><code> public class Product { public int Code { get; set; } public string Description { get; set; } public int Quantity { get; set; } } public class ProductHandler { public ProductHandler(string connectionString) { ConnectionString = connectionString; } public bool AddProduct(Product product) { return AddProducts(new Product[] { product }) &gt; 0; } public int AddProducts(IEnumerable&lt;Product&gt; products) { int rowsInserted = 0; using (OleDbConnection conn = new OleDbConnection(ConnectionString)) { conn.Open(); string query = "INSERT INTO [Seranne] (Code, Description, Quantity) VALUES(@Code, @Description, @Quantity)"; using (OleDbCommand cmd = new OleDbCommand(query, conn)) { cmd.Parameters.Add("Code", OleDbType.Integer); cmd.Parameters.Add("Description", OleDbType.VarChar); cmd.Parameters.Add("Quantity", OleDbType.Integer); foreach (var product in products) { cmd.Parameters["Code"].Value = product.Code; cmd.Parameters["Description"].Value = product.Description; cmd.Parameters["Quantity"].Value = product.Quantity; rowsInserted += cmd.ExecuteNonQuery(); } } } return rowsInserted; } public bool UpdateProduct(Product product) { return UpdateProducts(new Product[] { product }) &gt; 0; } public int UpdateProducts(IEnumerable&lt;Product&gt; products) { int rowsUpdated = 0; using (OleDbConnection conn = new OleDbConnection(ConnectionString)) { conn.Open(); string query = "UPDATE [Seranne] SET Description = @Description, Quantity = @Quantity WHERE [Code] == @Code)"; using (OleDbCommand cmd = new OleDbCommand(query, conn)) { cmd.Parameters.Add("Code", OleDbType.Integer); cmd.Parameters.Add("Description", OleDbType.VarChar); cmd.Parameters.Add("Quantity", OleDbType.Integer); foreach (var product in products) { cmd.Parameters["Code"].Value = product.Code; cmd.Parameters["Description"].Value = product.Description; cmd.Parameters["Quantity"].Value = product.Quantity; rowsUpdated += cmd.ExecuteNonQuery(); } } } return rowsUpdated; } public bool DeleteProduct(Product product) { return DeleteProducts(new int[] { productCode }) &gt; 0; } public int DeleteProducts(IEnumerable&lt;Product&gt; products) { using (OleDbConnection conn = new OleDbConnection(ConnectionString)) { conn.Open(); string productCodeStr = string.Join(", ", products.Select(item =&gt; item.Code)); string query = string.Format("DELETE FROM [Seranne] WHERE [Code] in ({0})", productCodeStr); using (OleDbCommand cmd = new OleDbCommand(query, conn)) { int rowsDeleted = cmd.ExecuteNonQuery(); return rowsDeleted; } } } public IEnumerable&lt;Product&gt; ReadAllProducts() { List&lt;Product&gt; result = new List&lt;Product&gt;(); using (OleDbConnection conn = new OleDbConnection(ConnectionString)) { conn.Open(); using (OleDbCommand cmd = new OleDbCommand("SELECT [Code], [Description], [Quantity] FROM [Seranne]", conn)) using (OleDbDataReader dReader = cmd.ExecuteReader()) while (dReader.Read()) { Product product = new Product(); product.Code = Convert.ToInt32(dReader["Code"]); product.Description = Convert.ToString(dReader["Description"]); product.Quantity = Convert.ToInt32(dReader["Quantity"]); result.Add(product); } } return result; } public string ConnectionString { get; private set; } } </code></pre> <hr> <p>Some example code:</p> <pre><code> ProductHandler _productHandler = new ProductHandler("connectionstring here or from config"); public void Example() { _productList.Clear(); _productList.AddRange(_productHandler.ReadAllProducts()); // displaying foreach (var product in _productList) Trace.WriteLine(string.Format("code: {0}, description: {1}, quantity: {2}", product.Code, product.Description, product.Quantity); // updating var selectedProduct = _productList.FirstOrDefault(item =&gt; item.Code == 15); if(selectedProduct!= null) { selectedProduct.Quantity = 50; _productHandler.UpdateProduct(selectedProduct); } // deleting _productHandler.DeleteProducts(_productList.Where(item =&gt; item.Quantity &lt; 5)); } </code></pre> <hr> <p>How to link the textboxes to the right product:</p> <p>I would create a UserControl that contains a Product property and the TextBoxes and handles when textbox_changed events occurs. Those event handlers manipulate the Product instance.</p> <p>You only generate 16 controls and bind a product to it. When you press a button, you only need to save the changed products.</p>
    singulars
    1. This table or related slice is empty.
    1. POKeep stuck at the reading the data from database
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJ. van Langen
    1. USJ. van Langen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COI'm still stuck on why you're doing the If statement there, why should the quantity be bigger than the stored quantity?
      singulars
      1. PO
      1. USJ. van Langen
    2. COHi @Jeroen van Langen, thanks for replying my question. The system will recognized by the time that user entering the `Product Code`, `Product Code` is the first thing that user must do, in order to displaying all the datas including the price and it refers to the which `Product Code`. So, all the datas and price are displayed based on the `Product Code` I will show you some more code, please refer to the question again. Thank you And, i don't get it what do you mean in your answer at the number 4.
      singulars
      1. PO
      1. USFuhans Puji Saputra
    3. COi did that before i know that the value of `textBoxQuantityContainer` can be set to the maximum value. Just ignore it. And i already knew that i can set the maximum value, so just know i delete it that statement "quantity bigger than the stored quantity". Thanks
      singulars
      1. PO
      1. USFuhans Puji Saputra
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload