在具有相同用户名和姓的registerin用户名上遇到问题！ [英] Having problem on registerin usernames with same user first name and last name!
问题描述
我正在创建一个项目，管理员在其上添加数据库上的新用户。
我正在参与制作用户名，如firstname.lastname，如果数据库有另一个用户具有相同的名称和姓氏，它增加了一个像firstname1.lastname的用户名。
我可以注册，并没有让我有任何问题。
关键是如何编辑用户如果我们有三个用户使用相同的数据，例如：
firstname.lastname
firstname1.lastname
firstname2.lastname
如果想要更新第二个用户名（firstname1.lastname）的数据，我必须检查数据库以及用户是否已经存在。
存储过程如下所示，我不知道如何解决它
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
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.
推荐答案
当然，这些组合都是有效的 - 你是没有正确分析要求的程序员！
- 因此，当您需要编辑存在倍数的firstname.lastname记录时，解决问题的唯一方法是使用单独的表单等，以允许用户选择要编辑的多个记录中的哪一个（并且您可以必须向用户提供足够的信息，例如电子邮件地址，电话号码以消除其他记录的歧义）
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)
正如Garth所说，您需要坐下来分析问题并检查要求。
在我看来，你尝试使用相同的程序插入一个新人，也更新现有的人。
如果我的假设是正确的，我认为这是一个错误。最好将两个功能分成两个不同的程序，以实现更清晰的逻辑。
插入
在这种情况下，您需要做的第一件事就是计算已经存在的具有相同名字和姓氏的人数。这将为您提供此人的索引。
如果此组中的某人已被删除，则会出现问题，那么您将有两个具有相同索引的人。
John Doe1，John Doe2，John Doe4。
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
所以你需要有一个算法来处理这种情况。
< u> 更新
当您更新时，您无法从John Doe1，John Doe2和John中选择合适的人选Doe3自动。
你需要做什么，正如Garth所说，在多个名字的情况下，首先向管理员提供一个名单列表和尽可能多的相关信息，包括表的主键。
然后管理员可以选择要更新的人，并使用主键调用存储过程，您将始终确保更新了正确的人。
（提供管理员选择正确的，当然）
[更新]
好的，所以你的问题是当你更新一个人和名字和姓氏已经改变了，突然间你又是另一个同名的人。
你现在要做的就是深入了解呼吸并经历不同的场景。这里的一个关键，双关语是你确实将主键作为参数提供给存储过程。
现在你必须考虑不同的情况：
1.名字和姓氏都没有更新。
没问题，正常更新。
2.更新名字或姓氏或两者都有。
检查数据库中是否存在新的名称组合。
如果不是：没问题
如果是这样：为此人分配一个新的索引号。使用与插入SP相同的算法。
我希望这可以帮助你前进。
我刚从酒吧来到这里去睡觉，也许，也许，只是也许，我现在不是最聪明的。
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.
我找到了一个解决方案，而我做了另一个参数，比如用户名并将该参数发送到SP具有以下条件：
I found a solution while I made another parameter, like username and sent that parameter to the SP with the following condition:
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.
这篇关于在具有相同用户名和姓的registerin用户名上遇到问题！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
