update values from a database

Question

0.00/5 (No votes)

See more:

Hi guys again,

I'm trying to add an application to my web site where the "old" products older than a year they appear automatically in discount (-20%),
so i have a datatable products (with colums Id,name,price,date)
thnx to my previous question digimanus gave me a query where i collect the "old products" <select name,price FROM products WHERE (DATEDIFF(m, date, GETDATE()) > 12)>

now i want to reduce the price of every "old"
so i writte the following code
the problem is that it's update the value only from the last product and the prices from the other "old" products receive the price from the last "old" product
what i should change in order to make it work?
or is any other way simpler from this one?
thnx in advance for your help!!!

C#

SqlCommand sqlCommand = new SqlCommand("select name,price FROM products WHERE (DATEDIFF(m, date, GETDATE()) > 12)", conn);
       conn.Open();
       using (SqlDataReader read = sqlCommand.ExecuteReader())
       {
           while (read.Read())
           {
               string namep = read["name"].ToString();
               string pice = read["price"].ToString(); //current price
               int discountprice = Convert.ToInt32(pice);
               updtable(Convert.ToInt32(pice));
           }

           read.Close();
           conn.Close();
       }


   }
   private void updtable(int pricenew)
   {
       SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
       int newvalue = pricenew - ((20 * pricenew) / 100); //discount ammount
       string aSQL = "UPDATE products set price= " + newvalue + "Where (DATEDIFF(m, date, GETDATE()) > 12) AND var=1";
       try
       {
           con.Open();
           SqlCommand cmd = new SqlCommand(aSQL, con);
           SqlDataReader reader = cmd.ExecuteReader();
           reader.Read();
           DataTable dataTable = new DataTable();
           dataTable.Load(reader);
       }
       finally
       {
           con.Close();
       }
       int temp = 0;
       updtable2(temp);
   }
   private void updtable2(int var)
   {
       SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
       string aSQL = "UPDATE products set var= " + var + "Where (DATEDIFF(m, date, GETDATE()) > 12)";
       try
       {
           con.Open();
           SqlCommand cmd = new SqlCommand(aSQL, con);
           SqlDataReader reader = cmd.ExecuteReader();
           reader.Read();
           DataTable dataTable = new DataTable();
           dataTable.Load(reader);
       }
       finally
       {
           con.Close();
       }

Posted 27-Nov-13 5:45am

JasonTsoum77

Updated 27-Nov-13 6:35am

Maciej Los

v3

Add a Solution

Comments

TryAndSucceed 27-Nov-13 12:16pm

What is Var here? Is it a flag for something?

JasonTsoum77 27-Nov-13 12:32pm

Yes i have it like a flag , i set it to 0 when a discount happens, in order to avoid new discound if the page loads again

Mike Meinz 27-Nov-13 13:22pm

You have some errors in your logic. You call updtable for each row retrieved and updtable updates all rows that meet the Select's Where clause selection critieria and the var = 1 criteria. See Solution 1. That is the correct way to do this task. That one SQL statement in Solution 1 does the entire task. No need for separate Select and Update statements.

JasonTsoum77 28-Nov-13 12:05pm

Hi Mike yes I see that something is going wrong , thnx to Maciej code I update my code and now it works prefect here it is
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
SqlCommand sqlCommand = new SqlCommand("UPDATE P1 SET P1.price = P2.price - (P2.price * 5/100) FROM Products AS P1 INNER JOIN Products AS P2 ON P1.Id = P2.Id WHERE DATEDIFF(m, P1.[date], GETDATE()) > 12 AND P1.var=1", conn);
conn.Open();
using (SqlDataReader read = sqlCommand.ExecuteReader())
{
while (read.Read())
{
string name = read["name"].ToString();

}
int var = 0;

read.Close();
conn.Close();
}

Thnx for your response

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-11-27T06:55:00

Solution 1

You can achieve that using only T-SQL command:

SQL

UPDATE P1
SET P1.price = P2.price - (P2.price * 20 /100)
FROM Products AS P1 INNER JOIN Products AS P2 ON P1.ProductID = P2.ProductID
WHERE DATEDIFF(m, P1.[date], GETDATE()) > 12

Posted 27-Nov-13 6:55am

Maciej Los

Comments

JasonTsoum77 27-Nov-13 13:14pm

I must generate a new table?
or just I must import as a query the code this code?

thnx in for your help!!!!

Maciej Los 27-Nov-13 13:19pm

You don't need to create another table. In above example i use a small trick. Only one table is used, but is used twice. For the first time (P1) it is used as a table to update and the second one (P2) as source to update ;)

Mike Meinz 27-Nov-13 13:23pm

My 5.
A best practice demonstrated succinctly!!!

Maciej Los 27-Nov-13 13:24pm

Thank you, Mike ;)

JasonTsoum77 27-Nov-13 13:30pm

It worked!!!!!!!!!!!!!!!!!!!!!!!
many many many thnx my friend Maciej for your help!!!!!!!
I try to find a solution for these many days!!!
Thnx again my friend for your valuable help!

Maciej Los 27-Nov-13 13:35pm

You're very welcome, Jason ;)
Call again ;)

Jörgen Andersson 28-Nov-13 4:23am

Spot on, 5

Maciej Los 28-Nov-13 4:46am

Thank you, Jörgen ;)