how to update profile of login member

[raghu.8]

hi jerry thanks for dropdownlist, but i got another problem regarding updatating a profile of a login member, i created a storedprocedure for login information just look at my codding

CREATE PROCEDURE sp_UserValidation
(
@Username varchar(50),
@UserPassword varchar(20)
) 
AS
IF EXISTS (SELECT UserName FROM LogIn1 WHERE UserName=@Username and UserPassword=@UserPassword)
BEGIN
return 1
END
ELSE
BEGIN
return 0
END
create table login1(username varchar(50)constraint pkusername primary key,userpassword varchar(20) ,
youranswer varchar(40),username varchar(20), useraddress varchar(100),usercity varchar(20),userpostal int,
userphone int,usermobile int,Email varchar(30),website varchar(30))CREATE PROCEDURE sp_UserValidation
(
@Username varchar(50),
@UserPassword varchar(20)
) 
AS
IF EXISTS (SELECT UserName FROM LogIn1 WHERE UserName=@Username and UserPassword=@UserPassword)
BEGIN
return 1
END
ELSE
BEGIN
return 0
END
create table login1(username varchar(50)constraint pkusername primary key,userpassword varchar(20) ,
youranswer varchar(40),username varchar(20), useraddress varchar(100),usercity varchar(20),userpostal int,
userphone int,usermobile int,Email varchar(30),website varchar(30))
my problem is i want to update some of login member column but iam not able to update, i wrote some coding for that just go through that,

(Toggle Plain Text) 
 protected void Button1_Click(object sender, EventArgs e)
    {
SqlConnection con = new SqlConnection("user id=sa;password=vubrain;database=raghu;data source=vubrain4");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
 cmd.CommandText = "Update login1001 Set name = @username1001, pwd = @userpassword1001 where username1001= @username1001";
       cmd.CommandType = CommandType.Text;
       SqlParameter para2;
       SqlParameter para3;
       SqlParameter para4;
       SqlParameter para5;
       //SqlParameter para6;
       SqlParameter para7;
       SqlParameter para8;
       SqlParameter para9;
       para2 = [e-mail]("loginaddress", tb1.Text);
       para3 = [e-mail]("logincity", t6.Text);
       para4 = [e-mail]("loginpostalcode", t7.Text);
       para5 = [e-mail]("loginphone", t8.Text);
       //para6 = cmd.Parameters.AddWithValue("loginfax", t9.Text);
       para7 = [e-mail]("loginmobile", t10.Text);
       para8 = [e-mail]("loginemail", t11.Text);
       para9 = [e-mail]("loginwebsite", t12.Text);
       int intnum;
       con.Open();
      intnum = cmd.ExecuteNonQuery();
      if (intnum > 0)
      {
        Label2.Text = "updated successfully";

      }
     else
      {
        Label2.Text = "updarted failed";
     }
       con.Close(); protected void Button1_Click(object sender, EventArgs e)
    {
SqlConnection con = new SqlConnection("user id=sa;password=vubrain;database=raghu;data source=vubrain4");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
 cmd.CommandText = "Update login1001 Set name = @username1001, pwd = @userpassword1001 where username1001= @username1001";
       cmd.CommandType = CommandType.Text;
       SqlParameter para2;
       SqlParameter para3;
       SqlParameter para4;
       SqlParameter para5;
       //SqlParameter para6;
       SqlParameter para7;
       SqlParameter para8;
       SqlParameter para9;
       para2 = [e-mail]("loginaddress", tb1.Text);
       para3 = [e-mail]("logincity", t6.Text);
       para4 = [e-mail]("loginpostalcode", t7.Text);
       para5 = [e-mail]("loginphone", t8.Text);
       //para6 = cmd.Parameters.AddWithValue("loginfax", t9.Text);
       para7 = [e-mail]("loginmobile", t10.Text);
       para8 = [e-mail]("loginemail", t11.Text);
       para9 = [e-mail]("loginwebsite", t12.Text);
       int intnum;
       con.Open();
      intnum = cmd.ExecuteNonQuery();
      if (intnum > 0)
      {
        Label2.Text = "updated successfully";

      }
     else
      {
        Label2.Text = "updarted failed";
     }
       con.Close();

please sought out my problem

thank you
raghu

[JerryShaw]

raghu,

That SQL statement should have thrown some errors.
I assume you are using Microsoft SQL.
First off, it is typically not a good idea to use commandtext. Instead use stored procedures whenever possible.
Although you are adding parameters to the CMD, because the CMD type is CommandText those parameters
are not going anywhere. Also, you need to format the CommentText properly. Example:

cmd.CommandText = "Update login1001 Set name = @username1001, pwd = @userpassword1001 where username1001= @username1001";
cmd.CommandType = CommandType.Text;

should be rewritten as such:

string m_username = "Whatever this user name is";
string m_pswd = "whatever the password is";
SqlConnection con ... all that jaz...
cmd.CommandText = string.Format("update login1001 set [name]='{0}', pwd='{1}' where username1001='{0}' ",  m_username, m_pswd);
con.open();
try
{
   cmd.ExecuteNonQuery();
   Label2.Text = "updated successfully";
}
catch (Exception eSql)
{
  Label2.Text = "Failed to Update: Error:"+eSql.Message;
}

I prefer to use stored procedures, and the rest of this response is geared towards
that end.

If you want to check to see if these credentials are okay then rewrite your sp_UserValidation like this: (BTW, Never use sp_ as the start of a stored procedure name, because this tells the SQL Plan to first look at the system procs first... waste of time and resources)

create procedure proc_ValidateUser @UserName varchar(50), @UserPassword varchar(20)
AS
   if not exists (select top 1 username from Login1 where username=@UserName and 
     userpassword=@UserPassword)
     raiserror('Invalid User',16,1)
GO

Or you could write it to return a scalar value of 1 or 0. SQL defaults to returning a zero if the procedure executed without any errors.

Now if you really want to update some additional information... and this is also the login check then write a stored procedure like this:

create procedure proc_UserValidation @UserName varchar(50),@UserPassword varchar(20)
   ,@useraddress varchar(100),@usercity varchar(20),@userpostal int
   ,@userphone int,@usermobile int,@Email varchar(30),@website varchar(30)
AS
   if not exists (select top 1 username from Login1 where username=@UserName and userpassword=@UserPassword)
  raiserror('Invalid User',16,1)
else
begin
    update Login1 set useraddress=@useraddress
         ,usercity=@usercity
         ,userpostal = @userpostal
         , userphone = @userphone
         , usermobile = @usermobile
         , email = @email
         , website = @website
    where username = @UserName and userpassword=@UserPassword
end

In C# you want to just perform the Login validation then do this, and write another
stored procedure to handle the update of address, city,etc.

string m_username = "Whatever this user name is";
           string m_pswd = "whatever the password is";

            SqlConnection conn = new SqlConnection("userid=sa;password=vubrain;database=raghu;data source=vubrain4");

            SqlCommand cmd = new SqlCommand("proc_ValidateUser", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UserName", m_uername);
            cmd.Parameters.AddWithValue("@UserPassword", m_pswd);

            con.Open();
            try
            {
                cmd.ExecuteNonQuery();
                Label2.Text = "Login successfully";
            }
            catch (Exception eSql)
            {
                Label2.Text = "Login Failed with: " +eSql.Message;
            }
            con.Close();

Hope that Helps,
Jerry

[raghu.8]

hi jerry, thanks for your codding i will try this
thank you