Update a column in SQL using C#

[r2k32]

Hi,
I'm workin on a website where i need to update a password using a stored procedure. I tested the stored procedure on SQL server and its working fine. but somehow i cannot seem to call it from C#. Can any one please help me?

Stored Procedure
CREATE PROCEDURE dbo.sp_ChangePassword


(
@Password VARCHAR(50),
@newPassword VARCHAR(50),
@Username VARCHAR(50)
)


AS
UPDATE Login SET Password = @newPassword
WHERE UserName = @UserName AND Password = @Password


RETURN


Code in c#



SqlConnection conn = new SqlConnection(@"Data Source=R2\SQLEXPRESS;Initial Catalog=Student;Integrated Security=True");


SqlCommand chpass = new SqlCommand("sp_ChangePassword",conn);


chpass.CommandType = CommandType.StoredProcedure;
chpass.Connection = conn;


SqlParameter usr_param = chpass.Parameters.Add("@Username", SqlDbType.VarChar);
SqlParameter currp_param = chpass.Parameters.Add("@Password", SqlDbType.VarChar);
SqlParameter newpass_param = chpass.Parameters.Add("@newPassword", SqlDbType.VarChar);


usr_param.Direction = ParameterDirection.Input;
currp_param.Direction = ParameterDirection.Input;
newpass_param.Direction = ParameterDirection.Input;


usr_param.Value = Piname.Text;
currp_param.Value = currpass.Text;
newpass_param.Value = newpass.Text;



try
{
chpass.ExecuteNonQuery();
errLabel.Text = "Password Sucessfully Changed";
}catch(Exception exp){errLabel.Text=exp.message;}

[JerryShaw]

The code looks basicaly correct, however a couple things to point out.
1) You should avoid creating procs that start with "sp_" because this tells SQL to first look in the system procedures.

2) In your proc, you may consider also testing to see if that username already exists, and raise an error if not. Also check that the current password is correct.

3) You must open the connection before executing the sproc.

4) Use the schema as part of the sproc name to avoid confusion when working with multiple schemas.

5) Consider encrypting the passwords so when (not if) someone gets access to that table they don't get all of your users and passwords.

if exists(select * from login where username=@username)
begin
... do you update stuff
end
else
raiserror('Invalid User',16,-1)

you can shorten your code a wee bit by using this:

SqlConnection conn = new SqlConnection(@"Data Source=R2\SQLEXPRESS;Initial Catalog=Student;Integrated Security=True");

SqlCommand chpass = new SqlCommand("dbo.sp_ChangePassword",conn);

chpass.CommandType = CommandType.StoredProcedure;

chpass.Parameters.AddWithValue("@UserName",Piname.Text);
chpass.Parameters.AddWithValue("@Password",currpass.Text);
chpass.Parameters.AddWithValue("@NewPassword",newpass.Text);


try
{
conn.Open();
chpass.ExecuteNonQuery();
errLabel.Text = "Password Sucessfully Changed";
}catch(Exception exp){errLabel.Text=exp.message;}
finally{ conn.Close(); }

--Jerry