Join 107,398 Programmers for FREE! Ask your question and get quick answers from experts. There are 1,201 online right now! We've got more than 500 tutorials and 2,000 snippets. Join and find out why Dream.In.Code is the #1 programming help community on the internet! Registration is fast and FREE... Join Now!
Ok, I have this method for updating an admin user for my site
csharp
/// <summary> /// method for updating the users information /// </summary> /// <returns></returns> public bool UpdateAdminUser() { try { //int to hold the # of rows affected int rows = 0; //string to hold the stored procedure we're executing string query = "uspUpdateAdminUser"; //create a SqlConnection and open it using (SqlConnection conn = new SqlConnection(QmetroDBHelper.GetConnectionString("Qmetro"))) { //create a SqlCommand instance SqlCommand cmd = new SqlCommand(); //set the commands properties cmd.CommandText = query; cmd.CommandType = CommandType.StoredProcedure; //add the stored procedure properties cmd.Parameters.AddWithValue("@intAdminUserID_PK", _userID); cmd.Parameters.AddWithValue("@chrAccessLevel", _accessLevel); cmd.Parameters.AddWithValue("@chrName", _name); cmd.Parameters.AddWithValue("@chrEmail", _email); //now see if the user changed the password if (!(string.IsNullOrEmpty(_password))) { //they provided a password so we need to update that and their salt value cmd.Parameters.AddWithValue("@chrPassword", HashPassword(_password)); cmd.Parameters.AddWithValue("@chrPasswordSalt", _saltValue); } else { //no password, pass null cmd.Parameters.AddWithValue("@chrPassword", DBNull.Value); cmd.Parameters.AddWithValue("@chrPasswordSalt", DBNull.Value); } cmd.Parameters.AddWithValue("@blnIsActive", _isActive); cmd.Parameters.AddWithValue("@blnAccountLocked", _isLocked); //set it's connection property cmd.Connection = conn;
//now we update and check the status if (!(admin.UpdateAdminUser())) { EditErrorMessage.Text = admin.ReturnMessage; //Response.Redirect("Users.aspx?action=view"); } else { Response.Redirect("Users.aspx?action=view"); } }
The stored procedure works because Ive ran it in Query Analyzer with no problems. With this it is returning true but isn't updating the users information. Anyone see anything wrong? Doesn't it suck that it's generally something you've done a thousand times that ends up giving you problems
Is the value for the variable "rows" 1 (from the ExecuteNonQuery)? I'm assuming you only want to update one row. Maybe another table got affected and so the final return value is positive.
Are multiple tables updated/inserted/deleted in the stored procedure? I had this situation where I had a trigger do an insert statement. So I did something on one table, and a corresponding record was logged. Even though only 1 record was updated, there were 2 rows returned (gave me hours of fun tracking that down...).
Unfortunately thats not the issue here. It is updating a single table, and it has no triggers. I did find out that it will update the password if I change it, but nothing else gets updated. Rows returns 1 every time but nothing is updated (unless I provide a value for the password then the password and salt value are updated)
I even changed the code to check and see if 1 was returned and it still returns true, though nothing is updated in my table
csharp
/// <summary> /// method for updating the users information /// </summary> /// <returns></returns> public bool UpdateAdminUser() { try { //string to hold the stored procedure we're executing string query = "uspUpdateAdminUser"; //create a SqlConnection and open it using (SqlConnection conn = new SqlConnection(QmetroDBHelper.GetConnectionString("Qmetro"))) { //create a SqlCommand instance SqlCommand cmd = new SqlCommand(); //set the commands properties cmd.CommandText = query; cmd.CommandType = CommandType.StoredProcedure; //add the stored procedure properties cmd.Parameters.AddWithValue("@intAdminUserID_PK", _userID); cmd.Parameters.AddWithValue("@chrAccessLevel", _accessLevel); cmd.Parameters.AddWithValue("@chrName", _name); cmd.Parameters.AddWithValue("@chrEmail", _email); //now see if the user changed the password if (!(string.IsNullOrEmpty(_password))) { //they provided a password so we need to update that and their salt value cmd.Parameters.AddWithValue("@chrPassword", HashPassword(_password)); cmd.Parameters.AddWithValue("@chrPasswordSalt", _saltValue); } else { //no password, pass null cmd.Parameters.AddWithValue("@chrPassword", DBNull.Value); cmd.Parameters.AddWithValue("@chrPasswordSalt", DBNull.Value); } cmd.Parameters.AddWithValue("@blnIsActive", _isActive); cmd.Parameters.AddWithValue("@blnAccountLocked", _isLocked); //set it's connection property cmd.Connection = conn;
//open our connection conn.Open();
//now execute int rows = Convert.ToInt32(cmd.ExecuteNonQuery());
The code looks fine. Except for all the global variables; they kill kittens, you know?
And will that, one place it could be screwing up is if you're passing nulls that you weren't expecting.
Also, the int value returned from a stored procedure is not necessarily the number of rows affected, it's more a status. Unless you are explicitly returning something like rows affected, it will probably be 1 as long as no errors are raised.
Those are property variables. I always use those instead of the properties themselves, just the way I was "taught" to do it. In all ExecuteNonQuery() methods rows returns the number of rows affected, Ive always used it and it has never failed me. The 2 I am passing null's to, the stored procedure looks for them as they are not required.
Now if I pass a password (then the salt is generated of course) those 2 values, password and salt, are updated in the table but nothing else is. If I don't update the password nothing is updated
From MSDN
QUOTE
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
I'm sorry I couldn't help. Whether you choose to believe me or not, I still think I'm correct on stored procedure return value. "UPDATE, INSERT, and DELETE statements" are not stored procedures.
Microsoft SQL Server stored procedures return data in four ways... ... Return codes, which are always an integer value. ... Return codes are generally used to pass back error checking information.
A SQL Server stored procedure that you can call is one that returns a status or a result parameter. This is typically used to indicate the success or failure of the stored procedure.
I don't see anything wrong in your code either. I've been looking at MSDN and the only thing different between the sample code there and yours is passing in the connection object to the command object at instantiation
MSDN
CODE
private static void CreateCommand(string queryString, string connectionString) { using (SqlConnection connection = new SqlConnection( connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); command.Connection.Open(); command.ExecuteNonQuery(); } }
1) Is your update statement a simple one? Something complex like
sql
update sometable set col1 = B.col1 col2 = B.col2 from sometable A, anothertable B where A.col3 = B.col3
might update more rows than you thought... and the row you wanted updated didn't get updated (though your test in Query Analyser should debunk this...)
2) I assume the variables with an underscore prefix are member variables of the QmetroAdmin class? Perhaps one of the get/set retrieved/assigned some values you didn't expect? Like baavgai mentioned, an unexpected null somewhere... Can check if the values passed in are the values you expected.
3) "update" access for the table and/or "exec" access for the stored proc granted? You probably ran as sa in QA. Maybe the web app connection ID didn't have access... and failed silently... (though why only the password is updated is weird... just throwing out ideas!)
4) Default values for the table columns? Might explain the weirdness in 3)
5) The where clause in the update statement in the stored proc? An incorrect formulation so a row was updated, just not the one you wanted?
6) Data problems? A duplicate row somewhere? Maybe you can keep only one row and see if that works out...
7) Wrong table or stored proc used (or even in the wrong database)? It happens...
2 May, 2008 - 09:26 PM
|