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!!!
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();
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);
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();
}