Can variable be used in SQL UPDATE statement in C# code?

[Wing]

Hi all,
I am writing a function that can change the value "Quantity" in the
selected row of MS SQL table "shoppingCart", my code is showing below

------------------------------------------------------
static void addQuantity(int rowNum, int oldQuantity)
{
int newQuantity = oldQuantity++;
SqlConnection thisConnection = new SqlConnection
(@"Data Source=(local);Integrated Security=SSPI;" +
"Initial Catalog=PhotoDB");
thisConnection.Open();
SqlCommand AddQuantity = thisConnection.CreateCommand();
AddQuantity.CommandText = "UPDATE ShoppingCart SET Quantity =
'"+newQuantity+"' WHERE RecordID = '"+rowNum+"'";
AddQuantity.ExecuteReader();
thisConnection.Close();
}

after I call this function, no running error occured, but the Quantity
value hasn't increased by 1, the sql server doesn't recognize the
variable "newQuantity" in my UPDATE statement. However, when I replace
the variable "newQuantity" with number (eg 3), the Quantity value does
change to 3 after calling the function.

Does the UPDATE statement accept the variable like WHERE statement
does???
If UPDATE statement does accept variable, what should I change in the
statement?
If UPDATE statement doesn't accept variable, is there any other way to
solve?

thank for your time.

wing

 

[Jeff Barnes]

Instead of using oldQuantity++, try using ++oldQuantity.

When the increment operator is used after the variable, it returns the
value of the variable before it has been incremented. When the
increment operator is used before the variable, it returns the value of
the variable after is has been incremented.

You can refer to the MSDN help files for more details.

Also, it appears that you are not expecting a resultset to be returned
from the sql. If this is true, then you should consider using the
ExecuteNonQuery of the SqlCommand object. It will provide better
performance than the ExecuteReader method since you don't actually
require a SqlDataReader.

I hope this helps.


Regards,

Jeff Barnes
Microsoft Certified Application Developer

 

[John B]

Jeff Barnes wrote:

Furthermore.
It appears you are treating Quantity and RecordId as strings by wrapping
them in '' (single quote)
Is this correct?
<snip>

 

[Wessel Troost]

Furthermore.

It appears you are treating Quantity and RecordId as strings by wrapping
them in '' (single quote)

In addition to what Jeff and John said: there's no need to call
ExecuteReader() for an update statement. Just call ExecuteNonQuery().

And if you do call ExecuteReader(), be sure to close the returned reader
afterwards.

Greetings,
Wessel

 

[Hans Kesting]

Instead of using oldQuantity++, try using ++oldQuantity.

When the increment operator is used after the variable, it returns the
value of the variable before it has been incremented. When the
increment operator is used before the variable, it returns the value
of the variable after is has been incremented.

You can refer to the MSDN help files for more details.

And in addition to this, both oldQuantity++ and ++oldQuantity
increase the value of oldQuantity. This might be an unwanted
"side-effect".
OK, as you aren't using oldQuantity anymore, it doesn't affect
this particular method, but it's something to watch out for.

Better would be to use
newQuantity = oldQuantity + 1;

Hans Kesting

 

[Wing]

Thanks for all you guy help. very appreciated.
I will change my code accordingly
Thanks again!!

Wing