Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am making a project on which the admin adds the new users on database.
I'm attending to make the username like firstname.lastname and if the database has another user with the same first name and last name it increments the username for one like firstname1.lastname.
I can register that and is not making me any problems.
The point is how to edit that user if for example we got three users with the same data like:
firstname.lastname
firstname1.lastname
firstname2.lastname

If a want to update the data of second username (firstname1.lastname) I have to check the database and if the user already exists.

The Stored procedure is like following and I don't know how to fix it
SQL
ALTER PROCEDURE [dbo].[procUpdateUser]
 @ID_Personi int
 ,@Emri nvarchar(20)
,@Mbiemri nvarchar(20)
,@Gjinia nvarchar(10)
,@DataLindjes date
,@VendiLindjes nvarchar(30)
,@ID_Komuna int
,@EmriPerdoruesit nvarchar (20)
,@Fjalekalimi nvarchar (20)
,@DataRegjistrimit date 


AS
BEGIN
	Declare @numri int = 0
	SET @numri =(SELECT COUNT (*) from PERSONI  where Emri = @Emri AND Mbiemri = @Mbiemri)
	DECLARE @ID_Roli int
			SET @ID_Roli = (SELECT (ID_Roli) FROM dbo.ROLI where ID_Roli=4) 
	DECLARE @ID_Identifikimi int
			SET @ID_Identifikimi = (SELECT ID_Identifikimi FROM PERSONI 
			where Emri = @Emri AND Mbiemri = @Mbiemri AND DataLindjes = @DataLindjes AND 
			Gjinia = @Gjinia AND VendiLindjes = @VendiLindjes AND ID_Roli = @ID_Roli AND ID_Personi=@ID_Personi ) 
	if @numri = 0
	
		BEGIN
			
	UPDATE [TEMA_ScoMan].[dbo].[IDENTIFIKIMI] SET
						
	[EmriPerdoruesit] = LOWER(@Emri + '.' + @Mbiemri)
	,[Fjalekalimi] = LOWER(@Emri + '.' + @Mbiemri)
	,[ID_Roli] = @ID_Roli
	,[DataRegjistrimit] = @DataRegjistrimit
	where ID_Identifikimi = @ID_Identifikimi
					
			 BEGIN

								
	UPDATE [TEMA_ScoMan].[dbo].[PERSONI] SET
						
		[Emri] = @Emri
		,[Mbiemri] = @Mbiemri
		,[Gjinia] = @Gjinia
		,[DataLindjes] = @DataLindjes
		,[VendiLindjes] = @VendiLindjes
		,[ID_Roli] = @ID_Roli
		,[ID_Identifikimi] = @ID_Identifikimi
		,[ID_Komuna] = @ID_Komuna
		where PERSONI.ID_Personi = @ID_Personi
					 END
		END
	
	ELSE
		BEGIN

UPDATE [TEMA_ScoMan].[dbo].[IDENTIFIKIMI] SET
						
 [EmriPerdoruesit]  = LOWER(CONCAT( @Emri , @numri) + '.' + @Mbiemri)
,[Fjalekalimi] = LOWER(@Emri + '.' + @Mbiemri)
,[ID_Roli] = @ID_Roli
,[DataRegjistrimit] = @DataRegjistrimit
where ID_Identifikimi = @ID_Identifikimi
						
BEGIN  	
												
UPDATE [TEMA_ScoMan].[dbo].[PERSONI] SET
										   
										   [Emri] = @Emri
										   ,[Mbiemri] = @Mbiemri
										   ,[Gjinia] = @Gjinia
										   ,[DataLindjes] = @DataLindjes
										   ,[VendiLindjes] = @VendiLindjes
										   ,[ID_Roli] = @ID_Roli
										   ,[ID_Identifikimi] = @ID_Identifikimi
										   ,[ID_Komuna] = @ID_Komuna
										   where PERSONI.ID_Personi = @ID_Personi
									
							END
	END
END


Thank you in advance for your reply.
Posted
Updated 23-Feb-22 20:29pm
Comments
George Jonsson 10-Nov-15 17:57pm    
Something to read while you are waiting for a solution.
http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/[^]
George Jonsson 10-Nov-15 18:07pm    
As I don't speak your language, it is a bit difficult to follow your code.

I see that you send the parameter ID_Personi as a parameter. Is this value a unique ID for the table?
If so why not use this ID directly?
dr_iton 10-Nov-15 18:24pm    
The problem is to the ELSE condition, if the user exists the parameter @numri counts the users with the same first names and last names and it concatenates that number to the name. For example if I update the user with firstname1.lastname the parameter @numri gets the value 2 which means that that the new username gets now firstname2.lastname, this user already exists.
If I make the count like parameter @numri counts the users and gets the value 2 and I increase it to 3 which means now hte new user gets firsname3.lastaname. After this if I register a new user with same name data he also gets the same username like firstname3.lastname
The problem is here and I know that is a math problem and don't know how to solve this.
Thank you again for your fast reply.

surely, the combinations are all valid - its you the programmer who hasnt analysed the requirements correctly !

- so the only way to solve the issue when you need to edit a firstname.lastname record where there are multiples, is to use a seperate form or such to allow the user to select which one of the multiple records to edit (and you'd have to provide the user with enough information eg email address, phone number to disambiguate the other record)
 
Share this answer
 
Comments
dr_iton 10-Nov-15 18:30pm    
The problem is to the ELSE condition, if the user exists the parameter @numri counts the users with the same first names and last names and it concatenates that number to the name. For example if I update the user with firstname1.lastname the parameter @numri gets the value 2 which means that that the new username gets now firstname2.lastname, this user already exists.
If I make the count like parameter @numri counts the users and gets the value 2 and I increase it to 3 which means now the updated user gets firsname3.lastaname. After this if I register a new user with same name data he also gets the same username like firstname3.lastname
The problem is here and I know that is a math problem and don't know how to solve this.
Thank you again for your fast reply.
As Garth say, you need to sit down and analyze the problem and check the requirements.

It seems to me that you try to use the same procedure for both inserting a new person and also to update an existing person.

If my assumption is correct, I think this is a mistake. It is better to separate the two functionalities into two different procedures for a cleaner logic.

Insert

The first thing you need to do in this case is to count how many persons that already exists with the same first and last name. That will give you the an index for this person.
The problem occurs if a person in this set has been deleted, then you will have two persons with the same index.
John Doe1, John Doe2, John Doe4.
NewIndex = Count + 1 = 4 = trouble

So you need to have an algorithm that takes care of this case too.


Update

When you update there is no way you can select the right person from the set John Doe1, John Doe2 and John Doe3 automatically.
What you need to do, as Garth said, in the case of multiple names, is first to present the admin with a list of the names and as much relevant information as possible, including the primary key of the table.
Then the admin can select the person to update and the stored procedure is called with the primary key and you will always be sure that the correct person is updated.
(Provided the admin picks the right one, of course)

[UPDATE]
OK, so your problem is when you are updating a person and the first name and the last name has changed and all of a sudden you a yet another person with the same name.

What you need to do now is to take a deep breath and go through the different scenarios. One key here, pun intended, is that you do provide the primary key as a parameter into the stored procedure.
Now you have to consider the different cases:

1. Neither the first name nor the last name has been updated.
No problem, update as normal.

2. Either the first name or the last name has been updated or both.
Check if the new combination of names exist in the database.
If not: No problem
If so: Assign a new index number for this person. Use the same algorithm as for the insert SP.

I hope this will help you forward.
I just came from the pub and about to go to sleep, so maybe, just maybe, I am not at my brightest right now.
 
Share this answer
 
v2
Comments
dr_iton 11-Nov-15 2:32am    
Your assumption is not correct. I use two stored procedures for different purposes. The first one is for Inserting data in table and the second one is for Updating the data.
The insertion works perfectly because if I add a new user I always take max ID value (which is primary key) of that table. While I update the data of a user I do refer to the ID of that record, but if updated user already exists with the same user data it counts again the user data and changes the username according to the number of same user data records.
As I mentioned before this must be a kind of math problems and probably I'll find a solution for it.
I got in mind your suggestion and I will give a try in that direction.
Thank you again.
George Jonsson 11-Nov-15 3:09am    
So your problem case is if you have an existing person and that person changes either first or last name so that the new combination first name + last name already exists?
Otherwise there should be no issue.
dr_iton 11-Nov-15 6:59am    
Yes that is my problem that I can't solve it.
Can I make a solution if I send a username as a parameter if I don't change the users first and last name, other ways the procedure shall change username and password.
George Jonsson 11-Nov-15 10:07am    
See my updated answer.
I found a solution while I made another parameter, like username and sent that parameter to the SP with the following condition:
SQL
ALTER PROCEDURE [dbo].[procUpdateUser]
 @ID_Personi int
 ,@Emri nvarchar(20)
,@Mbiemri nvarchar(20)
,@Gjinia nvarchar(10)
,@DataLindjes date
,@VendiLindjes nvarchar(30)
,@ID_Komuna int
,@EmriPerdoruesit nvarchar (20)
,@Fjalekalimi nvarchar (20)
,@DataRegjistrimit date 


AS
BEGIN
DECLARE @ID_Roli int
SET @ID_Roli = (SELECT (ID_Roli) FROM dbo.ROLI where ID_Roli=4) 
DECLARE @ID_Identifikimi int
SET @ID_Identifikimi = (SELECT ID_Identifikimi FROM PERSONI 
where PERSONI.ID_Personi = @ID_Personi)
Declare @numri int = 0
SET @numri =(SELECT COUNT (*) from PERSONI  where Emri = @Emri AND Mbiemri = @Mbiemri)

if (@numri = 0 AND LOWER(@Emri + '.' + @Mbiemri) != @EmriPerdoruesit )
	
BEGIN
			
UPDATE [TEMA_ScoMan].[dbo].[IDENTIFIKIMI] SET
						
 [EmriPerdoruesit] = LOWER(@Emri + '.' + @Mbiemri)
,[Fjalekalimi] = LOWER(@Emri + '.' + @Mbiemri)
,[ID_Roli] = @ID_Roli
,[DataRegjistrimit] = @DataRegjistrimit
where ID_Identifikimi = @ID_Identifikimi
					
 BEGIN
						
UPDATE [TEMA_ScoMan].[dbo].[PERSONI] SET
						
[Emri] = @Emri
,[Mbiemri] = @Mbiemri
,[Gjinia] = @Gjinia
,[DataLindjes] = @DataLindjes
,[VendiLindjes] = @VendiLindjes
,[ID_Roli] = @ID_Roli
,[ID_Identifikimi] = @ID_Identifikimi
,[ID_Komuna] = @ID_Komuna
where PERSONI.ID_Personi = @ID_Personi
 END
END

else if (@numri = 0 AND LOWER(@Emri + '.' + @Mbiemri) = @EmriPerdoruesit )
	
BEGIN
			
UPDATE [TEMA_ScoMan].[dbo].[IDENTIFIKIMI] SET
						
 [EmriPerdoruesit] = @EmriPerdoruesit
,[Fjalekalimi] = LOWER(@Emri + '.' + @Mbiemri)
,[ID_Roli] = @ID_Roli
,[DataRegjistrimit] = @DataRegjistrimit
where ID_Identifikimi = @ID_Identifikimi
					
 BEGIN
						
UPDATE [TEMA_ScoMan].[dbo].[PERSONI] SET
						
[Emri] = @Emri
,[Mbiemri] = @Mbiemri
,[Gjinia] = @Gjinia
,[DataLindjes] = @DataLindjes
,[VendiLindjes] = @VendiLindjes
,[ID_Roli] = @ID_Roli
,[ID_Identifikimi] = @ID_Identifikimi
,[ID_Komuna] = @ID_Komuna
where PERSONI.ID_Personi = @ID_Personi
 END
END

else if (@numri >0 AND LOWER(@Emri + '.' + @Mbiemri) != @EmriPerdoruesit )
	
BEGIN
			
UPDATE [TEMA_ScoMan].[dbo].[IDENTIFIKIMI] SET
						
 [EmriPerdoruesit] = LOWER(CONCAT(@Emri, @numri) + '.' + @Mbiemri)
,[Fjalekalimi] = LOWER(@Emri + '.' + @Mbiemri)
,[ID_Roli] = @ID_Roli
,[DataRegjistrimit] = @DataRegjistrimit
where ID_Identifikimi = @ID_Identifikimi
					
 BEGIN
						
UPDATE [TEMA_ScoMan].[dbo].[PERSONI] SET
						
[Emri] = @Emri
,[Mbiemri] = @Mbiemri
,[Gjinia] = @Gjinia
,[DataLindjes] = @DataLindjes
,[VendiLindjes] = @VendiLindjes
,[ID_Roli] = @ID_Roli
,[ID_Identifikimi] = @ID_Identifikimi
,[ID_Komuna] = @ID_Komuna
where PERSONI.ID_Personi = @ID_Personi
 END
END

	
ELSE if (@numri > 0 AND LOWER(@Emri + '.' + @Mbiemri) = @EmriPerdoruesit )
BEGIN

UPDATE [TEMA_ScoMan].[dbo].[IDENTIFIKIMI] SET
						
 [EmriPerdoruesit]  = @EmriPerdoruesit
,[Fjalekalimi] = LOWER(@Emri + '.' + @Mbiemri)
,[ID_Roli] = @ID_Roli
,[DataRegjistrimit] = @DataRegjistrimit
where ID_Identifikimi = @ID_Identifikimi
						
BEGIN  	
UPDATE [TEMA_ScoMan].[dbo].[PERSONI] SET
									                       [Emri] = @Emri
								                     ,[Mbiemri] = @Mbiemri
										   ,[Gjinia] = @Gjinia
										   ,[DataLindjes] = @DataLindjes
										   ,[VendiLindjes] = @VendiLindjes
										   ,[ID_Roli] = @ID_Roli
										   ,[ID_Identifikimi] = @ID_Identifikimi
										   ,[ID_Komuna] = @ID_Komuna
										   where PERSONI.ID_Personi = @ID_Personi
									
							END
	END
END


Updated.
 
Share this answer
 
v2
Lets share another Idea with you.

one option could be to have a new field named AdditionalIdentifier of type integer.
For all such cases where you are appending a number in firstname, you get that number in AdditionalIdentifier field.

This can easily be acheived using SQL Server Views

SQL
DECLARE @temp TABLE
(
	string NVARCHAR(50)
)

INSERT INTO @temp (string)
VALUES 
	('firstname.lastname'),
	('firstname1.lastname'),
	('firstname2.lastname')

SELECT string, SUBSTRING(string, PATINDEX('%[0-9]%', string), PATINDEX('%[0-9][^0-9]%', string + 't') - PATINDEX('%[0-9]%', 
					string) + 1) AS AdditionalIdentifier
FROM @temp


since now you have got this number. You have got the power to do a search over this field. This will make your life easier because your incremental logic gets simplified

ID  UserId 					FirstName 	LastName  	AdditionalIdentifier
1	asif.iqbal				asif		iqbal	
2	asif1.iqbal				asif		iqbal		1
3	ahmed.raza				ahmed		raza
4	ahmed1.raza				ahmed		raza		1


now if you want to modify ID 2 asif.iqbal to ahmed.raza what you need to do is to do a search over firstname + lastname to get max(AdditionalIdentifier).
for above case below query would return 1

SQL
select max(AdditionalIdentifier)
from	<VIEW>
WHERE 	FirstName= 'ahmed'
and		secondName='raza'


you increment that value by 1 for non null cases and append it to first name as per your business case. For above case ID 2 will be modified as

ID  UserId 					First 		Name  		AdditionalIdentifier
1	asif.iqbal				asif		iqbal	
2	ahmed2.raza				Ahmed		Raza		2
3	ahmed.raza				ahmed		raza
4	ahmed1.raza				ahmed		raza		1


I am hoping I have understood your problem correctly.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900