I have this stored procedure
CREATE PROCEDURE spEditInfo
@username nvarchar(64),
@password nvarchar(64),
@firstname nvarchar(64),
@middlename nvarchar(64),
@lastname nvarchar(64),
@email nvarchar(64) AS
UPDATE Users
SET password=@password,
firstname=@firstname,
middlename=@middlename,
lastname=@lastname,
email=@email
WHERE username=@username;
Here is the problem, though. No matter what values I throw in as the remaining parameters, only password gets changed. Any ideas why?
Here is my calling code:
SqlCommand cmd = new SqlCommand("spEditInfo", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("username", SqlDbType.NVarChar, 64);
cmd.Parameters.Add("password", SqlDbType.NVarChar, 64);
cmd.Parameters.Add("firstname", SqlDbType.NVarChar, 64);
cmd.Parameters.Add("middlename", SqlDbType.NVarChar, 64);
cmd.Parameters.Add("lastname", SqlDbType.NVarChar, 64);
cmd.Parameters.Add("email", SqlDbType.NVarChar, 64);
cmd.Parameters["username"].Value = Username.Text;
cmd.Parameters["password"].Value = Password.Text;
cmd.Parameters["firstname"].Value = FirstName.Text;
cmd.Parameters["middlename"].Value = MiddleName.Text;
cmd.Parameters["lastname"].Value = LastName.Text;
cmd.Parameters["email"].Value = Email.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
EDIT: Ok, I admit I have been wasting time looking at the wrong end. I have the values for the controls initialized during page_load. It is overwriting all of the changes, except for password, which I don't show.
Any suggestions on how to improve this?? I want the committed data to show up on the form.
EDIT: Back to square 1. It seems the data is passed perfectly to the SP, because I have the SP now do a little logging of the params to a txt file outside. However, it seems that the values are not being updated when called by my ASP.NET page. This is peculiar, as w开发者_如何学编程ith manual execution, the SP works, just not when called via my code.
Try
cmd.Parameters.AddWithValue("username", Username.Text);
Instead of using cmd.Parameters.Add
this should work for you and make it a little cleaner. Hopefully this help's you out
I would run Profiler and see if the values you are expecting to see are really being sent to the proc.
You are forgetting the @ in the parameter name:
cmd.Parameters.Add("@username",
Honestly, I would suggest using something like the Enterprise Library and the Data Application Block if you can. It really makes life easier. My 2 cents.
-Shaun
精彩评论