Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
2

I have a database table in which i am saving created users along with username now what i want to get string value from my database if username exists in database and then i will show error message "UserName Exists or Choose new one etc etc"

here is my stored procedure

ALTER PROCEDURE [dbo].[p_SaveUpdate_AdminUserAccount]

 (    
 @Id_User int,    
 @nm_UserName varchar(50),      
 @nm_UserPassword varchar(50),      
 @nm_UserRole int      
 )    
AS    
BEGIN    
IF @Id_User=0    
 BEGIN 
 Declare @count int
    Select @count=COUNT(1) from ref_Admin_UserAccount where nm_UserName=@nm_UserName
    if(@count > 0)
    begin
        select -1 as a;
    end
    else
    BEGIN   
  SET NOCOUNT ON;  
  Insert into ref_Admin_UserAccount(nm_UserName,nm_UserPassword,nm_UserRole)  

values(@nm_UserName,@nm_UserPassword,@nm_UserRole) 
SELECT SCOPE_IDENTITY(); 
end

END    
ELSE    
 BEGIN    
  SET NOCOUNT ON;    
  UPDATE ref_Admin_UserAccount    
   SET    
   nm_UserName=@nm_UserName,    
   nm_UserPassword=@nm_UserPassword,    
   nm_UserRole=@nm_UserRole   
  WHERE Id_User=@Id_User;    
  SELECT @Id_User;    
End    
End

and here is my code to save values in database from presentation layer using 3 tier archiecture

 public int SaveAdminUserAccountInformation(AdminAccountProperties oAdminUser)
        {
            try
            {
                SqlParameter[] parm = new SqlParameter[4];

                parm[0] = new SqlParameter(PARM_ADMIN_USER_ID, SqlDbType.Int);
                parm[0].Value = oAdminUser.UserID;
                parm[1] = new SqlParameter(PARM_USER_NAME, SqlDbType.VarChar);
                parm[1].Value = oAdminUser.UserName;
                parm[2] = new SqlParameter(PARM_ADMIN_USER_PASSWORD, SqlDbType.VarChar);
                parm[2].Value = oAdminUser.Password;
                parm[3] = new SqlParameter(PARM_USER_ROLE, SqlDbType.Int);
                parm[3].Value = oAdminUser.UserRole;
                return Convert.ToInt32(SqlHelper.ExecuteScalar(this._ConnString, CommandType.StoredProcedure, SQL_ADMIN_USER_INSERT_UPDATE, parm));

            }
            catch (Exception ex)
            {
                throw ex;

            }
        }

please give me some help.

share|improve this question
 
use SELECT CAST(scope_identity() AS int) –  Anna.P Jun 12 at 12:03
 
where to use it in stored procedure?\ –  aami Jun 12 at 12:14
 
after the insert ... –  Anna.P Jun 12 at 12:15
 
why not to return int value because in your stored procedure you are getting -1 if username exists...? –  Ammar Asjad Jun 13 at 8:19
 
@AmmarAsjad it works fine now thanks. –  aami Jun 17 at 4:54
add comment

3 Answers

up vote 1 down vote accepted

Your stored procedure returns -1 if username exists so i suggest you to return int rather than string in your Data Access Layer. here is some modifications in your code

 public int SaveAdminUserAccountInformation(AdminAccountProperties oAdminUser)
        {
            try
            {
                SqlParameter[] parm = new SqlParameter[4];

                parm[0] = new SqlParameter(PARM_ADMIN_USER_ID, SqlDbType.Int);
                parm[0].Value = oAdminUser.UserID;
                parm[1] = new SqlParameter(PARM_USER_NAME, SqlDbType.VarChar);
                parm[1].Value = oAdminUser.UserName;
                parm[2] = new SqlParameter(PARM_ADMIN_USER_PASSWORD, SqlDbType.VarChar);
                parm[2].Value = oAdminUser.Password;
                parm[3] = new SqlParameter(PARM_USER_ROLE, SqlDbType.Int);
                parm[3].Value = oAdminUser.UserRole;
                int a =Convert.ToInt32(SqlHelper.ExecuteScalar(this._ConnString, CommandType.StoredProcedure, SQL_ADMIN_USER_INSERT_UPDATE, parm));
return a;

            }
            catch (Exception ex)
            {
                throw ex;

            }
        }

after this on basis of return value do what ever you want to do in presentation layer.

share|improve this answer
add comment
up vote 0 down vote

Instead Selecting the New row in Stored Procedure. Use an OUT Parameter of the Stored Procedure. Like the Code Below : 

                try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "USP_IUD_FAC_SUBJECT";
                cmd.Parameters.AddWithValue("@EVENT", EVENTI);
                cmd.Parameters.AddWithValue("@FAC_SUB_CODE", FAC_SUB_CODE);
                cmd.Parameters.AddWithValue("@FAC_SUB_NAME", FAC_SUB_NAME);
                cmd.Parameters.AddWithValue("@TRAN_DATE", TRAN_DATE);
                cmd.Parameters.AddWithValue("@TRAN_USER_ID", TRAN_USER_ID);
                cmd.Parameters.AddWithValue("@APPROVED", APPROVED);
                cmd.Parameters.AddWithValue("@APPROVED_DATE", APPROVED_DATE);
                cmd.Parameters.AddWithValue("@APPROVED_USER_ID", APPROVED_USER_ID);

                SqlParameter outparameter = new SqlParameter("@MSG", SqlDbType.NVarChar, 200);
                outparameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(outparameter);

                SqlParameter OutId = new SqlParameter("@RETURNOUTID", SqlDbType.Int);
                OutId.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(OutId);

                cmd.ExecuteNonQuery();
                object msg = cmd.Parameters[outparameter.ParameterName].Value;
                ReturnId2 = Convert.ToString(cmd.Parameters[OutId.ParameterName].Value);

                return Convert.ToString(msg);

            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new ArgumentException(ex.Message);
            }
            finally
            {
                transaction.Commit();
                DL_CCommon.Connection().Close();
            }

So, Create the Stored Procedure with an Out Parameter. And, Set the Required value into that Out parameter in Procedure. For using Out Parameter in Stored Procedure Follow the link : http://www.codeproject.com/Articles/126898/Sql-Server-How-to-write-a-Stored-procedure-in-Sql

share|improve this answer
add comment
up vote 0 down vote

SQL

ALTER PROCEDURE [dbo].[p_SaveUpdate_AdminUserAccount]
  @Id_User int,    
 @nm_UserName varchar(50),      
 @nm_UserPassword varchar(50),      
 @nm_UserRole int,  
 @id int OUTPUT
AS

C#

SqlConnection db = DataConn.SqlConnection();
SqlCommand sqlComm = new SqlCommand(
           "p_SaveUpdate_AdminUserAccount @Id_User, @nm_UserName, @nm_UserPassword, @nm_UserRole, @id", db, transaction) { CommandType = CommandType.Text };
sqlComm.Parameters.Add(new SqlParameter("@Id_User", SqlDbType.Int)).Value;
sqlComm.Parameters.Add(new SqlParameter("@nm_UserName", SqlDbType.VarChar)).Value;
sqlComm.Parameters.Add(new SqlParameter("@nm_UserPassword", SqlDbType.VarChar)).Value;
sqlComm.Parameters.Add(new SqlParameter("@nm_UserRole", SqlDbType.VarChar)).Value;
sqlComm.Parameters.Add(new SqlParameter("@id", SqlDbType.Int) { Direction = ParameterDirection.Output });

sqlComm.Parameters.Add(param);
    sqlComm.ExecuteNonQuery();
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.