0

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.

7
  • 1
    Is Users a table or a view? Are there any triggers involved? Commented Sep 1, 2011 at 20:57
  • I know this might sound silly, but have you validated the text in all the textboxes to make sure there is actually text in them? Commented Sep 1, 2011 at 21:51
  • Have you tested the stored procedure using only SQL? If not, I recommend that you do. Commented Sep 1, 2011 at 22:02
  • Run a profile on the SQL Server database and see what is being submitted to the database. Then you can play with that in SQL Analyzer and see if it is going wrong at that level or before. That will split your problem search in half. Commented Sep 1, 2011 at 22:05
  • It seems like it really is a logic error, in my aspx page. The values from the textboxes are not being read correctly. Is this because I have their values loaded from the database on page_load? Commented Sep 2, 2011 at 1:51

4 Answers 4

1

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

Sign up to request clarification or add additional context in comments.

1 Comment

It might look cleaner at first - but it's not. Since you're not explicitly telling ADO.NET what type your parameter it is, ADO.NET has to make a guess - which is often quite good, but still: I would always prefer to explicitly set the datatype of my parameter as the original post has it. It's just cleaner, and quite honestly: I don't see how this approach would make any difference for the better...
1

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

Comments

0

You are forgetting the @ in the parameter name:

cmd.Parameters.Add("@username",

4 Comments

He is forgetting the @ for "password" as well, yet it works. Generally, SQL bound parameters should work without the prefix, which is true for Oracle's : as well for MS SQL Server's @.
Are you sure it is getting changed? I would run a profile on the SQL Server database and see what is being submitted to the database. Then you can play with that in SQL Analyzer and see if it is going wrong at that level or before.
@Branko Are you sure about it adding the @ for stored procs? I couldn't find this anywhere. I keep reading that they must match.
My personal experience working with ADO.NET under Oracle, DB2, PostgreSQL and MSSQL was that parameter names work without prefixes in methods such as Add and AddWithValue (parameters in SQL of course still have to be prefixed). I'm not sure whether this is a "guaranteed" behavior in all cases, though...
0

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.