By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 801 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 17 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Wing wrote:
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

Nov 17 '05 #2

P: n/a
Jeff Barnes wrote:
<snip>
SqlCommand AddQuantity = thisConnection.CreateCommand();
AddQuantity.CommandText = "UPDATE ShoppingCart SET Quantity =
'"+newQuantity+"' WHERE RecordID = '"+rowNum+"'";


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

P: n/a
> 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
Nov 17 '05 #4

P: n/a
Jeff Barnes wrote:
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
Nov 17 '05 #5

P: n/a
Thanks for all you guy help. very appreciated.
I will change my code accordingly
Thanks again!!

Wing

Nov 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.