存在与数据库用户的vb登录表单 [英] vb log in form with database user exists
问题描述
嗨专家，
i有这个代码用于登录，但我需要一些帮助，以确保数据库表中已存在的用户名不能用来注册新用户。
任何帮助???谢谢！
hi experts,
i have this code working for log in, but i would need some help to make sure if username that already exists in the DB table cannot be used to register a new user.
any help??? thanks!
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim Result As String
Result = "Account Created" & vbCrLf
Result = Result & "UserName: " & txtFName.Text & vbCrLf
Result = Result & "Gender: " & ComboBox1.SelectedItem
btnSubmit.Enabled = False
Dim insertCommand As SqlCeCommand = ssceconn.CreateCommand()
insertCommand.CommandText = "Insert Into People (user, l_name,p_name) Values (?,?,?)"
insertCommand.Parameters.Add(New SqlCeParameter("user", SqlDbType.NText, 50))
insertCommand.Parameters.Add(New SqlCeParameter("l_name", SqlDbType.NText, 50))
insertCommand.Parameters.Add(New SqlCeParameter("p_name", SqlDbType.NText, 50))
insertCommand.Parameters("user").Value = txtFName.Text
insertCommand.Parameters("l_name").Value = txtLName.Text
insertCommand.Parameters("p_name").Value = txtPName.Text
ssceconn.Open()
insertCommand.ExecuteNonQuery()
ssceconn.Close()
If txtFName.Text = "" Or txtLName.Text = "" Or txtPName.Text = "" And ComboBox1.SelectedItem = "" Then
MsgBox("Please fill in the form")
txtPName.Text = ""
txtLName.Text = ""
ElseIf txtLName.Text = txtPName.Text And ComboBox1.SelectedItem = ("Male") Or ComboBox1.SelectedItem = ("Female") Then
MsgBox(Result)
Form2.Show()
txtFName.Text = ""
txtPName.Text = ""
txtLName.Text = ""
ElseIf MsgBox("Wrong Password Please retype or Please fill in your gender") Then
txtPName.Text = ""
txtLName.Text = ""
End If
btnSubmit.Enabled = True
End Sub
推荐答案
我想你首先需要查看你已有的代码 - 它有缺陷。
你为什么选择将数据插入数据库，然后检查它是否有效？所有这一切都是用垃圾填满您的数据库，这将阻止用户创建他的帐户。
假设他进入用户但没有其他任何东西。没有其他用户具有相同的价值。
您将数据插入数据库，然后告诉他这是错误的。
他纠正它，并再次尝试 - 但现在用户值正在使用中，因此您添加的任何代码都会拒绝他的数据 - 可能是在它再次添加之后。
首先进行检查 - 首先是有效性，然后是现有价值。
检查实际现有价值只是一个略有不同的查询的简单情况：
I think you first need to look at the code you already have - it has flaws.
Why are you inserting data into the database and then checking to see if it is valid? all that does is fill your database with rubbish, that will prevent the user creating his account.
Suppose he enters a user but nothing else. No other user has the same value.
You insert the data into the db, and then tell him it is wrong.
He corrects it, and tried again - but now the user value is in use, so any code you add will reject his data - presumably after it has been added again.
Do your checking first - validity first, then existing value.
Checking tha actual existing value is just a simple case of a slightly different query:
SELECT COUNT(*) from People WHERE [user]=@USER并以与上述相同的方式设置@USER参数。然后发出ExecuteScalar并检查返回值。如果它不为零，则记录已存在。
[来自OP：作为解决方案发布]
and setting the @USER parameter in much the same way you do above. Then issue a ExecuteScalar and check the return value. if it is non-zero, then records exist already.
[FROM THE OP: POSTED AS A SOLUTION]
Dim selectCommand As SqlCeCommand = ssceconn.CreateCommand()
selectCommand.CommandText = "SELECT COUNT(*) FROM People WHERE f_name = '" & txtFName.Text & "'"
Dim myReader As SqlCeDataReader = selectCommand.ExecuteReader
issit like correct ??
不要发布解决方案，这意味着你向人们发送电子邮件 - 改为使用评论或回复系统。
它会起作用，但这有两个原因是个坏主意：
首先，连接字符串会让你容易受到意外或故意的SQL注入攻击，这些攻击可能会造成损害或破坏您的数据库。像在原始代码中一样使用参数化查询。
其次，不需要使用DataReader - 您只对单个整数值感兴趣，所以请改用ExecuteScalar方法，然后返回直接唯一的值：
issit like correct??
Don''t post as a solution, it means you send an email to people - use the Comment or Reply systems instead.
It will work, but it''s a bad idea for two reasons:
Firstly, concatenating strings leaves you vulnerable to accidental or deliberate SQL injection attacks which can damage or destroy your database. Use parameterised queries as you did in your original code.
Secondly, there is no need to use a DataReader - you are only interested in a single integer value, so use the ExecuteScalar method instead, and it returns the one and only value directly:
Dim selectCommand As SqlCeCommand = ssceconn.CreateCommand()
selectCommand.CommandText = "SELECT COUNT(*) FROM People WHERE f_name = @FN"
selectCommand.Parameters.AddWithValue("@FN", txtFName.Text)
Dim existingUsers as Integer = selectCommand.ExecuteScalar
如果您希望用户名（列用户）始终是唯一的，请不要强制执行这个你自己。让数据库处理它。
为了做到这一点，将列定义为主键或唯一键。在这种情况下，我认为一个唯一的密钥更合适，因为我个人会创建一个代理密钥作为主键（目前不存在于您的表中）。
但是要将用户名定义为唯一，您可以像这样修改表：
If you want that the user names (column user) are unique all the time, do not enforce this by yourself. Let the database handle it.
In order to do this define the column as a primary or unique key. I would think a unique key is more appropriate in this case since personally I would create a surrogate key as a primary key (not currently existing in your table).
But to define the user name as unique, you can modify the table like this:
ALTER TABLE People ADD CONSTRAINT UNQ_People_User UNIQUE ([user]);
有关唯一约束的详细信息，请参阅 UNIQUE Constraints [ ^ ]
现在，当存在唯一约束时，如果您尝试第二次添加同一用户，则SQL命令将引发错误。因此，您需要将执行包装到 try..catch 块中。
在抓住你的内部可以检查错误是由于唯一约束还是其他因素。如果它是由约束引起的，只需告知用户。
For more information about unique constraint, see UNIQUE Constraints[^]
Now when the unique constraint exists, if you try to add the same user the second time, the SQL command will throw an error. Because of this you need to wrap the execution into a try..catch block.
Inside the catch you can check whether the error is because of the unique constraint or something else. If it''s caused by the constraint, simply inform the user.
这篇关于存在与数据库用户的vb登录表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
