DaniWeb Forum Index > Software Development > C#

C# RSS

Update a column in SQL using C#

Please support our C# advertiser: $4.95 a Month - ASP.NET Web Hosting – Click Here!

•

Join Date: Jan 2008

Posts: 1

Reputation: r2k32 is an unknown quantity at this point

Solved Threads: 0

r2k32

Offline

Newbie Poster

Update a column in SQL using C#

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;}

•

Join Date: Nov 2006

Posts: 436

Reputation: JerryShaw is on a distinguished road

Solved Threads: 72

JerryShaw

Offline

Posting Pro in Training

Re: Update a column in SQL using C#

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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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(); }
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(); }