CODEWITHSUNDEEP
c#t-sqlado.net
Hung Tran
Hung Tran

Reputation: 31

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

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 with manual execution, the SP works, just not when called via my code.

Upvotes: 0

Views: 860

Answers (4)

Shaun McDonnell
Shaun McDonnell

Reputation: 441

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

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

I would run Profiler and see if the values you are expecting to see are really being sent to the proc.

Upvotes: 1

JBrooks
JBrooks

Reputation: 10013

You are forgetting the @ in the parameter name:

cmd.Parameters.Add("@username",

Upvotes: 0

Trio
Trio

Reputation: 176

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

Upvotes: 1

Related Questions