You're executing cmd = new SqlCommand(sqlcommand, conn); after adding your parameters, which I think will clear out the parameters. Try adding the parameters after declaring the new SqlCommand, i.e.
cmd = new SqlCommand(sqlcommand, conn);
cmd.Parameters.Add("@CustomerName", CustomerNameTextBox.Text);
cmd.Parameters.Add("@CustomerEmail", CustomerEmailTextBox.Text);
"Sometimes I think the surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us."
Marked as answer by ldechent on Apr 18, 2008 02:13 PM
Please update your command to pass also transid as a parameter. It doesn't make sense to pass @CustomerEmail and @CustomerName as parameters, and add transid dynamically to the string. Your risk for SQL Injection remains if you pass transid dynamically:
sqlcommand = "UPDATE Transactions SET CustomerName=@CustomerName WHERE transid = @transid ";
sqlcommand += "UPDATE Transactions SET CustomerEmail=@CustomerEmail WHERE transid = @transid ";
cmd.Parameters.Add("@CustomerName", CustomerNameTextBox.Text);
cmd.Parameters.Add("@CustomerEmail", CustomerEmailTextBox.Text);
cmd.Parameters.Add("@transid", thetransid.ToString().Trim());
If this answered your question, be sure to mark it as the answer. Then you give credit to people who help you, and others will know that the question is already answered.
My SQL Server blog (swedish): http://www.underlandet.com/SqlServer
in the sqlcommand -- yes, it would work. However, I wanted to use Parameters as a safety issue. If any person reading this has just started and has not yet heard about SQL Injection attack (something hackers do), google it.
Fortunately (or not), I don't really have to worry about the communication with SQL because where I work we have a standard WebService template that we use to talk to our db and all the sql goodness is abstracted away and I never have to deal with it.
So by passing in the parameters there is some sort of validation done for you?
Remember: mark posts that helped you as the answer to aid future readers
I second BlogPortalist. Also, is there any reason to have two UPDATE statements since it seems you're updating two columns in the same record. Couldn't they be combined into one UPDATE statement or have I missed something (it is Friday afternoon after all)?
"Sometimes I think the surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us."
It doesn't make sense to pass @CustomerEmail and @CustomerName as parameters, and add transid dynamically to the string. Your risk for SQL Injection remains if you pass transid dynamically:
I didn't post my entire code (didn't want to scare anyone coming to look at it), and I thought an expert glancing at what I showed would see the error less than a second if I showed the short segment.
The variable transid was generated internally by a previous operation, so a hacker would not have access to it.
However, the point is: "make sure you've parameterized (or done something) for every location in your program where something is "coming in from the outside".
It wouldn't surprise me if someone else reading this will reply to it with something like "You also think about X, or Y" with respect to safeguarding your code.
etc., and if I have 20 variables, it is easier to come in and do the "surgery" when I need to make a modification. There has to be a technical term for this (I call it vertical code).
ldechent
Contributor
6326 Points
1577 Posts
AddWithValue : Must declare the scalar variable "@…
Apr 18, 2008 01:39 PM|LINK
whatispunk
Contributor
4074 Points
876 Posts
Re: AddWithValue : Must declare the scalar variable "@…
Apr 18, 2008 01:43 PM|LINK
Does it make a difference if you add the parameters first?
cmd.Parameters.Add("@CustomerName", CustomerNameTextBox.Text);
cmd.Parameters.Add("@CustomerEmail", CustomerEmailTextBox.Text);
sqlcommand = "UPDATE Transactions SET CustomerName=@CustomerName WHERE transid='" + thetransid.ToString().Trim() + "' ";
sqlcommand += "UPDATE Transactions SET CustomerEmail=@CustomerEmail WHERE transid='" + thetransid.ToString().Trim() + "' ";
cmd = new SqlCommand(sqlcommand, conn);
cmd.ExecuteNonQuery();
conn.Close();
But the Parameters collection is likely only for calling stored procedures. Because @CustomerName and @CustomerEmail are variables, not parameters.
Try this instead:
sqlcommand = "UPDATE Transactions SET CustomerName="+CustomerNameTextBox.Text+" WHERE transid='" + thetransid.ToString().Trim() + "' ";
sqlcommand += "UPDATE Transactions SET CustomerEmail="+CustomerEmailTextBox.Text+" WHERE transid='" + thetransid.ToString().Trim() + "' ";
cmd = new SqlCommand(sqlcommand, conn);
cmd.ExecuteNonQuery();
conn.Close();
Why UpdatePanels Are Dangerous
Why You Should Not Place Your Whole Site In An UpdatePanel
ramblor
Contributor
6676 Points
1013 Posts
Re: AddWithValue : Must declare the scalar variable "@…
Apr 18, 2008 01:53 PM|LINK
You're executing cmd = new SqlCommand(sqlcommand, conn); after adding your parameters, which I think will clear out the parameters. Try adding the parameters after declaring the new SqlCommand, i.e.
BlogPortalis...
Contributor
2451 Points
345 Posts
Re: AddWithValue : Must declare the scalar variable "@…
Apr 18, 2008 02:18 PM|LINK
Please update your command to pass also transid as a parameter. It doesn't make sense to pass @CustomerEmail and @CustomerName as parameters, and add transid dynamically to the string. Your risk for SQL Injection remains if you pass transid dynamically:
My SQL Server blog (swedish): http://www.underlandet.com/SqlServer
ldechent
Contributor
6326 Points
1577 Posts
ANSWER WAS: Incorrect order of statements
Apr 18, 2008 02:18 PM|LINK
Thank you! that did it!
The question was raised, why not use
="+CustomerNameTextBox.Text+"
in the sqlcommand -- yes, it would work. However, I wanted to use Parameters as a safety issue. If any person reading this has just started and has not yet heard about SQL Injection attack (something hackers do), google it.
-Larry
whatispunk
Contributor
4074 Points
876 Posts
Re: ANSWER WAS: Incorrect order of statements
Apr 18, 2008 02:22 PM|LINK
Of course, I didn't even think of that.
Fortunately (or not), I don't really have to worry about the communication with SQL because where I work we have a standard WebService template that we use to talk to our db and all the sql goodness is abstracted away and I never have to deal with it.
So by passing in the parameters there is some sort of validation done for you?
Why UpdatePanels Are Dangerous
Why You Should Not Place Your Whole Site In An UpdatePanel
ramblor
Contributor
6676 Points
1013 Posts
Re: AddWithValue : Must declare the scalar variable "@…
Apr 18, 2008 02:24 PM|LINK
ldechent
Contributor
6326 Points
1577 Posts
Blogportalist brings up a VERY GOOD POINT!!!
Apr 18, 2008 02:26 PM|LINK
I didn't post my entire code (didn't want to scare anyone coming to look at it), and I thought an expert glancing at what I showed would see the error less than a second if I showed the short segment.
The variable transid was generated internally by a previous operation, so a hacker would not have access to it.
However, the point is: "make sure you've parameterized (or done something) for every location in your program where something is "coming in from the outside".
It wouldn't surprise me if someone else reading this will reply to it with something like "You also think about X, or Y" with respect to safeguarding your code.
Happy programming! : - )
-Larry
ldechent
Contributor
6326 Points
1577 Posts
Vertical Code (why I split a command into two lines)
Apr 18, 2008 02:35 PM|LINK
I'm like you -- I actually would prefer to write both in one line.
However, a second person reading through the code will not have to scroll over if everything fits on one page.
Also, I don't have a technical term for it but I'll call it "code vertically". If writing a long stringbuilder, I might:
sb.Append("INSERT INTO sometablename (www, xxx, yyy, zzz) VALUES ('");
sb.Append(reader["www"].ToString().Trim() + "','");
sb.Append(reader["xxx"].ToString().Trim() + "','");
etc., and if I have 20 variables, it is easier to come in and do the "surgery" when I need to make a modification. There has to be a technical term for this (I call it vertical code).