Update a column in SQL using C#

Please support our C# advertiser: Intel Parallel Studio Home
Reply

Join Date: Jan 2008
Posts: 1
Reputation: r2k32 is an unknown quantity at this point 
Solved Threads: 0
r2k32 r2k32 is offline Offline
Newbie Poster

Update a column in SQL using C#

 
0
  #1
Jan 27th, 2008
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;}
Reply With Quote Quick reply to this message  
Join Date: Nov 2006
Posts: 436
Reputation: JerryShaw is on a distinguished road 
Solved Threads: 72
JerryShaw JerryShaw is offline Offline
Posting Pro in Training

Re: Update a column in SQL using C#

 
0
  #2
Jan 28th, 2008
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:
  1. SqlConnection conn = new SqlConnection(@"Data Source=R2\SQLEXPRESS;Initial Catalog=Student;Integrated Security=True");
  2.  
  3. SqlCommand chpass = new SqlCommand("dbo.sp_ChangePassword",conn);
  4.  
  5. chpass.CommandType = CommandType.StoredProcedure;
  6.  
  7. chpass.Parameters.AddWithValue("@UserName",Piname.Text);
  8. chpass.Parameters.AddWithValue("@Password",currpass.Text);
  9. chpass.Parameters.AddWithValue("@NewPassword",newpass.Text);
  10.  
  11.  
  12. try
  13. {
  14. conn.Open();
  15. chpass.ExecuteNonQuery();
  16. errLabel.Text = "Password Sucessfully Changed";
  17. }catch(Exception exp){errLabel.Text=exp.message;}
  18. finally{ conn.Close(); }

--Jerry
Reply With Quote Quick reply to this message  
Reply

This thread is more than three months old.
Perhaps start a new thread instead?
Message:


Thread Tools Search this Thread



About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC