我在VB做一个登录表单。. NET,我在mysql中有一个名为user的表。我想做的是,在用户可以登录之前,用户表的Administrator列必须为TRUE, DELETED列必须为FALSE。我已经尝试了我所知道的一切,但所有非管理员用户仍然能够登录…
用户表是这样的:
+---------------+------------+---------+--------+---------+----------+---------------+---------+
| User_BannerID | FirstName | LastName | Email | Username | Password | Administrator | Deleted |
+---------------+------------+---------+--------+---------+----------+---------------+---------+
| | | | | | | | |
| | | | | | | | |
+---------------+------------+---------+--------+---------+----------+---------------+---------+
代码如下:
Imports MySql.Data.MySqlClient
Public Class frmAdlogin
Private Sub cmdCancel_Click(sender As System.Object, e As System.EventArgs) Handles cmdCancel.Click
Application.Exit()
End Sub
Private Sub cmdLogin_Click(sender As System.Object, e As System.EventArgs) Handles cmdLogin.Click
Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myConnString As String
Dim UserID As String
myConnString = "server=" & txtServer.Text & ";" _
& "user id=" & txtUsername.Text & ";" _
& "password=" & txtPassword.Text & ";" _
& "database=attendance"
conn.ConnectionString = myConnString
Try
conn.Open()
myCommand.Connection = conn
myCommand.CommandText = "SELECT user_bannerid FROM user WHERE BINARY username = ?Username and administrator = 'TRUE' and deleted = 'FALSE' "
myCommand.Parameters.Add("?Username", txtUsername.Text)
UserID = myCommand.ExecuteScalar
conn.Close()
Dim AdminForm As New frmAdmin
AdminForm.UserID = UserID
AdminForm.connectionString = myConnString
AdminForm.Show()
Me.Hide()
Me.Close()
Catch myerror As MySqlException
MessageBox.Show("Invalid login. Please Enter The Correct Server Address And Your Username Plus The Correct Password ")
conn.Dispose()
End Try
End Sub
Private Sub frmAdlogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.AcceptButton = cmdLogin
Me.CancelButton = cmdCancel
txtPassword.PasswordChar = "*"
End Sub
End Class
编辑BizApps现在的样子:
Imports MySql.Data.MySqlClient
Public Class frmAdlogin
Private Sub cmdCancel_Click(sender As System.Object, e As System.EventArgs) Handles cmdCancel.Click
Application.Exit()
End Sub
Private Sub cmdLogin_Click(sender As System.Object, e As System.EventArgs) Handles cmdLogin.Click
Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myConnString As String
Dim UserID As String
myConnString = "server=" & txtServer.Text & ";" _
& "user id=" & txtUsername.Text & ";" _
& "password=" & txtPassword.Text & ";" _
& "database=attendance"
conn.ConnectionString = myConnString
conn.Open()
myCommand.Connection = conn
myCommand.CommandText = "SELECT user_bannerid FROM user WHERE BINARY username = ?Username and administrator = 'TRUE' and deleted = 'FALSE' "
myCommand.Parameters.Add("?Username", txtUsername.Text)
Dim dt = New DataTable()
Dim ds = New MySqlDataAdapter(myCommand)
ds.Fill(dt)
If (dt.Rows.Count > 0) Then
conn.Close()
Dim AdminForm As New frmAdmin
AdminForm.UserID = UserID
AdminForm.connectionString = myConnString
AdminForm.Show()
Me.Hide()
Me.Close()
Else
MessageBox.Show("Invalid login. Please Enter The Correct Server Address And Your Username Plus The Correct Password ")
End If
End Sub
Private Sub frmAdlogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.AcceptButton = cmdLogin
Me.CancelButton = cmdCancel
txtPassword.PasswordChar = "*"
End Sub
End Class
先试试你的查询是否有效。
SELECT user_bannerid FROM user WHERE BINARY username ='myusername' and administrator = 'TRUE' and deleted = 'FALSE'
如果返回记录
试试这个:
myCommand.Connection = conn
myCommand.CommandText = "SELECT user_bannerid FROM user WHERE BINARY username = ?Username and administrator = 'TRUE' and deleted = 'FALSE' "
myCommand.Parameters.Add("?Username", txtUsername.Text)
Dim dt = new DataTable()
Dim ds = New MySqlDataAdapter(myCommand)
ds.Fill(dt)
if(dt.Rows.Count < 1 ) then // no record found
MessageBox.Show("Invalid login. Please Enter The Correct Server Address And Your Username Plus The Correct Password ")
Else //record found
conn.Close()
Dim AdminForm As New frmAdmin
AdminForm.UserID = UserID
AdminForm.connectionString = myConnString
AdminForm.Show()
Me.Hide()
Me.Close()
End If
对
由于我见过的大多数SQL数据库表示布尔值为0 (false)和1 (true),尝试将SQL语句更改为:
myCommand.CommandText = "SELECT user_bannerid FROM user WHERE BINARY username = ?Username and administrator = 1 and deleted = 0"