SQL Parameters Not Affecting SqlCommand (C#)

[spamguy]

Assume an SqlCommand object with a valid connexion and SQL command of:

UPDATE Company
SET Web = @website
WHERE ID = @companyID

There exists a button on a form page that acts as a 'Submit': when clicked, the SQL update is executed and the user is sent back to a profile to view changes:

sqlCommand1.Parameters["@website"].Value = website.Text; // a TextBox value
sqlCommand1.Parameters["@companyID"].Value = companyID;
 
sqlCommand1.Connection.Open();
sqlCommand1.ExecuteNonQuery();
 
Response.Redirect("details.aspx?id=" + companyID);
 

These parameters are listed in the collection menu of sqlCommand1. When this code is tried, though, no changes occur in the database. I'm out of ideas what could be wrong; I've always had bad relations with SqlParameter.

Thanks!

[Plater]

What does your stored procedure look like?
It seems like you should be getting an exception if something was very wrong.
BUT it's also possible if something goes wrong with the WHERE clause it will fail without an error.

[nateraaaa]

Where are you setting the query to be executed by your sqlcommand?

SqlConnection conn = new SqlConnection("dbconnectionstring");
conn.Open();
 
SqlCommand cmd = new SqlCommand();
cmd.Connection = "your database connection";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "your update query or stored proc name";
 
cmd.Parameters.Add("@website", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@CompanyID", SqlDbType.Int);
 
cmd.ExecuteNonQuery();

Give this a try.

Nathan

[spamguy]

What does your stored procedure look like?
It seems like you should be getting an exception if something was very wrong.
BUT it's also possible if something goes wrong with the WHERE clause it will fail without an error.

Not sure I understand what you mean about the stored procedure. Isn't that what I supplied? (And as it applies to SQL, of course, there isn't one, being a simple query.)

It's unlikely something's wrong with the WHERE clause. Note that Response.Redirect() depends on the same variable @companyID does to be forwarded properly.

[nateraaaa]

You can create a stored procedure that executes the simple update query above.

[spamguy]

You can create a stored procedure that executes the simple update query above.

In SQL? Sure, I could, but why? Queries don't much simpler than this. I tend to reserve stored procedures for the heavy-hitting processes.

Perhaps you all are being confused by the fact I'm using SqlConnection and SqlCommand objects in VS' Design pane instead of establishing the connexion programmatically?

[Plater]

What I am thinking is your executing this literally
"UPDATE Company SET Web = @website WHERE ID = @companyID"
And your parameters aren't being added in because they are not being added correctly?

I've only used parameters on stored procedures, but I'm pretty sure they CAN be used on inline commands, just more like this maybe?
"UPDATE Company SET Web = %1 WHERE ID = %2"

[spamguy]

Where are you setting the query to be executed by your sqlcommand?

SqlConnection conn = new SqlConnection("dbconnectionstring");
conn.Open();
 
SqlCommand cmd = new SqlCommand();
cmd.Connection = "your database connection";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "your update query or stored proc name";
 
cmd.Parameters.Add("@website", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@CompanyID", SqlDbType.Int);
 
cmd.ExecuteNonQuery();

Give this a try.

Nathan

Tried this, but no difference. I had to tweak the code because the parameters remained undefined, so both parameter lines became '...).Value = *value*;'

[nateraaaa]

Tried this, but no difference. I had to tweak the code because the parameters remained undefined, so both parameter lines became '...).Value = *value*;'

Try wrapping the code that I sent you into a try catch finally block.

try
{
//sqlcode that I posted
}
catch(Exception ex)
{
string message = ex.Message.ToString();
}
finally
{
cmd.Dispose();
conn.Dispose();
}

Run the debugger. If there is a problem executing the sql code the code will fall into the catch block. You can hover over ex or hover over message to see what error occurred.

[spamguy]

Try wrapping the code that I sent you into a try catch finally block.

try
{
//sqlcode that I posted
}
catch(Exception ex)
{
string message = ex.Message.ToString();
}
finally
{
cmd.Dispose();
conn.Dispose();
}

Run the debugger. If there is a problem executing the sql code the code will fall into the catch block. You can hover over ex or hover over message to see what error occurred.

I'll certainly give that a shot, though I can't run the debugger...it's a component of the development server no one bothered to set up. X-/ Plus I'd have get my computer added to Debugger Users. I'll have to pull a Response.Write or alert().

-----

UPDATE: I tacked on a Label and sent 'message' to that in lieu of a debugger. Result: No exceptions.

[Plater]

What if you just tried:
"UPDATE Company SET Web = 'http://www.google.com' WHERE ID = 2"
(Replace 2 with some number you're going to fudge with that you know exists)
And see if it works?

[nateraaaa]

Are you attaching to a development database where you could look at the values change in your table? First run your update query and make sure that the table will update with your query. Next run your sql command in your code and then check your table.

[spamguy]

What if you just tried:
"UPDATE Company SET Web = 'http://www.google.com' WHERE ID = 2"
(Replace 2 with some number you're going to fudge with that you know exists)
And see if it works?

That works. Since data was being pulled from the database using a static query to fill in TextBox fields, it's not unreasonable that it should work in reverse.

[Plater]

it's not unreasonable that it should work in reverse.

But that doesn't mean it does, are you remembering the single quote ' around the string data for the website?

[spamguy]

Are you attaching to a development database where you could look at the values change in your table? First run your update query and make sure that the table will update with your query. Next run your sql command in your code and then check your table.

Quite sure the table is updating properly. One of my first attempts was done whilst I was half-awake, so the query turned out SET Web = '@website', which set the cell to '@website'. It's been there ever since (minus the change to google.com, re: above post).

Urg, I wish there could be some intermediate way of printing the full query a la PHP...then it would be a snap to debug.

[spamguy]

Now this is interesting. If I assemble the query in a n00bish, asking-for-injection-attack way, it also does not update the Web field.

cmd.CommandText = "UPDATE Company SET Web = '" + website.Text + "' WHERE ID = " + companyID; // same response with website.Text.ToString()
cmd.Connection.Open();
cmd.ExecuteNonQuery();
 

-----

UPDATE: I see what is happening now. If I dump website.Text to Label1, it shows website.Text contains the old contents of TextBox, not whatever was typed in over it. How odd. So, new question: how to circumvent this?

[chillicane]

i appear to be having a similar problem:

            SqlCommand sqlComm;
 
            sqlComm = new SqlCommand("update T_Script_Master set sm_sql = @foo where sm_id = @bar", ConnPool.instance().getConn());
 
            sqlComm.Parameters.Add( new SqlParameter("@foo",SqlDbType.Text ));
            sqlComm.Parameters.Add(new SqlParameter("@bar",SqlDbType.VarChar));
 
            sqlComm.Parameters["@foo"].Value = this.mSQL;            
            sqlComm.Parameters["@bar"].Value = this.Guid;            
 
            sqlComm.ExecuteNonQuery();
 

this code does Nothing. No exceptions, no data saved. In fact, with SQL Profiler running in the background, it appears that no statement is being executed at all.

note that doing it the dodgy way works fine, but im storing SQL statements in this database so i want to be able to escape the quotes etc. Irk, i cannot work this one out.

[Plater]

Why not just switch to stored procedures?

Then you could go:

SqlCommand sqlComm;
 
sqlComm = new SqlCommand("myUpdateProc", ConnPool.instance().getConn());
sqlComm.CommandType=CommandType.StoredProcedure;//need to check on spelling for this, I wing'ed it
 
sqlComm.Parameters.Add( new SqlParameter("@foo",SqlDbType.Text ));
sqlComm.Parameters.Add(new SqlParameter("@bar",SqlDbType.VarChar));
 
sqlComm.Parameters["@foo"].Value = this.mSQL;            
sqlComm.Parameters["@bar"].Value = this.Guid;            
 
sqlComm.ExecuteNonQuery();
 

[spamguy]

Why not just switch to stored procedures?

Then you could go:

SqlCommand sqlComm;
 
sqlComm = new SqlCommand("myUpdateProc", ConnPool.instance().getConn());
sqlComm.CommandType=CommandType.StoredProcedure;//need to check on spelling for this, I wing'ed it
 
sqlComm.Parameters.Add( new SqlParameter("@foo",SqlDbType.Text ));
sqlComm.Parameters.Add(new SqlParameter("@bar",SqlDbType.VarChar));
 
sqlComm.Parameters["@foo"].Value = this.mSQL;            
sqlComm.Parameters["@bar"].Value = this.Guid;            
 
sqlComm.ExecuteNonQuery();
 

That's worth trying later, but as I said, parameters are no longer the problem. See my last post.

[spamguy]

Another source helped me with the TextBox.Text issue. And I quote:

Take a look at your Page_Load event. This always fires *before* the
Click event. If you are reloading the textbox from the database inside
Page_Load, it will overwrite whatever the user typed before you can pick it
up in the update_Click event. If this is what is happening, you can use an
"if (!Page.IsPostBack)" inside Page_Load to avoid executing that part of the
code during the postback.

[Plater]

didn't even see that question.
Yeah, it's a pretty common trip-up to forget the isPostback deal.