2 questions in asp net...

[Zpixel]

I write this sql query to update a column by 1 unit.

strsql="update products set qty=qty+1 where id=" + id + "";

when i look at database, i see an increase by 2.
example:

qty is an integer column in a table.
before query : qty=5
i expect : qty=6 after query but
after query: qty=7

what's wrong?
the query is runned just one time.

------------------ second question

inside one query, i want to run another query. but compiler gives error and says that i should

close the previous datareader first. i can't close the former datareader because the query result is being read through a while loop.

first query
while(result.read()){
....
id=convert.ToInt32(result[0]);
...

second query using id

}

How can i run one query inside another one?

thanks.

[Old Pedant]

(1)
when i look at database, i see an increase by 2.
what's wrong?
the query is runned just one time.
Sorry. I don't believe you. Obviously the query runs TWO times. You probably have a coding mistake that is causing it to run two times when you wanted only one.

No code, no help. We can't guess what mistake you made.

*************

(2) Sorry, but that's a limitation of DataReader...you can only have one DataReader open PER CONNECTION. You might try opening a second connection.

But the other thing to do is try to find a better way to code this. MOST of the time, when you are executing one query inside of a loop on another query, it means you are doing the wrong then. Most of the time you should change your SQL so that you use only one query.

Again, no code, no help.

[Zpixel]

you could answer my question without taking a look at source. i needed a new sql connection.

//open database connection
string strCon, strSql;
SqlConnection con,con1;
strCon = "Data Source=.\\SQLEXPRESS;AttachDbFilename='H:\\Documents and Settings\\Administrator\\My Documents\\xshop.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True";
con = new SqlConnection(strCon);
con.Open();
con1 = new SqlConnection(strCon);
con1.Open();


strSql = "Select * From products " + condition + " order by id desc";
SqlCommand myCommand = new SqlCommand(strSql, con);
myCommand.ExecuteNonQuery();
SqlDataReader result = myCommand.ExecuteReader();


while (result.Read())
{
string id = Convert.ToString(result[0]);
... some other actions...


//update statics of products on each product view or click
if (action == "search" || action == "searchById")
{
strSql = "update products set viewd=viewd+1 where id=" + id + "";
SqlCommand myCommand1 = new SqlCommand(strSql, con1);
myCommand1.ExecuteNonQuery();
}

}


any suggestion to make it better?
thanks anyway.

do you write code for fun or money?