Saturday, August 31, 2013

Update to the database was only read the first value in program

Hai everyone, i have a problem, the program just read the first entered `Quantity` value in the program and apply it to all rows in database and the value of first and second row are same, even though in the beginning, first and second row value are different. First of all, my database is like this (Screenshot 1). The screenshot 1 until screenshot 5 are working properly, i just want to show you to not confuse you later. My problem is on screenshot 6 and 7.



Screenshot 1:

https://www.dropbox.com/s/5q8pyztqy7ejupy/Capture.PNG



And when i run the program and entered the first "Product Code" in first row and want to change the first `Quantity` value to 25, so i just enter 75 in `Quantity` in the program like the screenshot below (Screenshot 2):



Screenshot 2:

https://www.dropbox.com/s/wd9oz7wdi7s4l0k/Capture_1.PNG



And when i click update, the database changed to this (Screenshot 3):



Screenshot 3:

https://www.dropbox.com/s/9i0zc285netfrg3/Capture2.PNG



When i change the first "Product Code" in first row to second "Product Code" in first row and want to change the second `Quantity` value to 100, so i just enter 50 in `Quantity` in the program like the screenshot below (Screenshot 4):



Screenshot 4:

https://www.dropbox.com/s/khsvh5bf1dc4c8u/Capture_2.PNG



And when i click update, the database changed to this (Screenshot 5):



Screenshot 5:

https://www.dropbox.com/s/kdsyd2csccim63w/Capture3.PNG



But, when i enter the first "Product Code" in first row and second "Product code" in second row in my program like the screenshot below (Screenshot 6):



Screenshot 6:

https://www.dropbox.com/s/7hmsoe4ba5n18re/Capture_3.PNG



And when i click update, the database changed to this (Screenshot 7):



Screenshot 7:

https://www.dropbox.com/s/1mmtkz71f8s4u2h/Capture4.PNG.


I have been wondering, why it become like this?



Here is the code:



private void UpdateQuantity()
{
int codeValue = 0;
int index = 0;

List<int> integers = new List<int>();

foreach (var tb in textBoxCodeContainer)
{
if (int.TryParse(tb.Text, out codeValue))
{
integers.Add(codeValue);
}
}

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
string commandSelect = "SELECT [Quantity], [Description], [Price] FROM [Seranne] WHERE [Code] = @Code";
string commandUpdate = "UPDATE [Seranne] SET [Quantity] = @Quantity WHERE [Code] IN(" + string.Join(", ", integers) + ")";

using (OleDbCommand cmdSelect = new OleDbCommand(commandSelect, conn))
using (OleDbCommand cmdUpdate = new OleDbCommand(commandUpdate, conn))
{
cmdSelect.Parameters.Add("Code", System.Data.OleDb.OleDbType.Integer);
cmdSelect.Parameters["Code"].Value = this.textBoxCodeContainer[index].Text;

cmdUpdate.Parameters.Add("Quantity", System.Data.OleDb.OleDbType.Integer);

using (OleDbDataReader dReader = cmdSelect.ExecuteReader())
{
while (dReader.Read())
{
if (textBoxQuantityContainer[index].Value != 0)
{
newVal = Convert.ToInt32(dReader["Quantity"].ToString()) - textBoxQuantityContainer[index].Value;
cmdUpdate.Parameters["Quantity"].Value = newVal;
int numberOfRows = cmdUpdate.ExecuteNonQuery();
}
}

index += 1;

dReader.Close();
}
}

conn.Close();
}
}

Could you guys help me out? Thanks a bunch!


No comments:

Post a Comment