开发者

TSQL: Updating multiple columns not working?? (ASP.NET C#)

开发者 https://www.devze.com 2023-04-02 01:34 出处:网络
I have this stored procedure CREATE PROCEDURE spEditInfo @username nvarchar(64), @password nvarchar(64),

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

0

精彩评论

暂无评论...
验证码 换一张
取 消