mysql - 搜索栏不起作用；找不到专栏？

Question

好的。我正在制作一个大程序，应该在今天发布，但遗憾的是我在测试时因为这个问题而推迟了。我有管理区域来恢复人员信息，以防万一，我注意到搜索栏不起作用，给我一个错误，表明该列不起作用。我使用的是MySql数据库。 vb.net 编程。错误是:找不到列 [nusername]。但它确实存在。一切正常。是的，我知道 sql 注入(inject)，忽略它。这是我的代码，但请不要窃取我的代码:

Imports MySql.Data.MySqlClient
Public Class DeathLairAdminControlPanel
    Dim MySqlConn As MySqlConnection
    Dim MySqlCmd As MySqlCommand
    Dim dbDT As New DataTable

    Public Property AUsernameHomePass As String
    Private Sub DeathLairAdminControlPanel_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LabelALoggedIn.Text = AUsernameHomePass
    End Sub

    Private Sub ButtonUpdateU_Click(sender As Object, e As EventArgs) Handles ButtonUpdateU.Click
        If TextBoxNUsern.Text = "" Or TextBoxNEmail.Text = "" Or TextBoxNPass.Text = "" Or TextBoxNPhone.Text = "" Then
            MessageBox.Show("Please don't leave empty areas.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
        ElseIf TextBoxNUsern.Text.Length < 4 Then
            MessageBox.Show("Username too short.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
        ElseIf TextBoxNEmail.Text.Length < 8 Then
            MessageBox.Show("Email cant be that short. IT MUST BE VALID!.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
        ElseIf TextBoxNPass.Text.Length < 6 Then
            MessageBox.Show("Password can't be that short. Make it longer and secure.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
        ElseIf TextBoxNPhone.Text.Length < 6 Then
            MessageBox.Show("Phone number can't be that short. IT MUST BE VALID.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
        ElseIf System.Text.RegularExpressions.Regex.IsMatch(TextBoxNUsern.Text, "^[A-Za-z0-9]+$") And System.Text.RegularExpressions.Regex.IsMatch(TextBoxNPass.Text, "^[A-Za-z0-9.]+$") And System.Text.RegularExpressions.Regex.IsMatch(TextBoxNEmail.Text, "\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*") And System.Text.RegularExpressions.Regex.IsMatch(TextBoxNPhone.Text, "^\+[0-9()-+ ]+$") And System.Text.RegularExpressions.Regex.IsMatch(TextBoxNCity.Text, "^[A-Za-z ]+$") Then

            MySqlConn = New MySqlConnection
            MySqlConn.ConnectionString =
                "server=localhost;userid=root;password=HIDDEN;database=syscore"
            Dim MySqlRea As MySqlDataReader

            Try
                MySqlConn.Open()
                Dim Query As String
                Query = "update syscore.normaluser set nusername='" & TextBoxNUsern.Text & "',nemail='" & TextBoxNEmail.Text & "',npass='" & TextBoxNPass.Text & "',nphone='" & TextBoxNPhone.Text & "',ncity='" & TextBoxNCity.Text & "' where nusername='" & TextBoxNUsern.Text & "'"
                MySqlCmd = New MySqlCommand(Query, MySqlConn)
                MySqlRea = MySqlCmd.ExecuteReader

                MessageBox.Show("User has been updated.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
                MySqlConn.Close()
            Catch ex As MySqlException
                MessageBox.Show(ex.Message)
            Finally
                MySqlConn.Dispose()
            End Try
        End If
        loaddatabase_table()
    End Sub

    Private Sub ButtonDeleteU_Click(sender As Object, e As EventArgs) Handles ButtonDeleteU.Click
            MySqlConn = New MySqlConnection
            MySqlConn.ConnectionString =
                "server=localhost;userid=root;password=HIDDEN;database=syscore"
            Dim MySqlRea As MySqlDataReader

            Try
                MySqlConn.Open()
                Dim Query As String
                Query = "delete from syscore.normaluser where nusername'" & TextBoxNUsern.Text & "'"
                MySqlCmd = New MySqlCommand(Query, MySqlConn)
                MySqlRea = MySqlCmd.ExecuteReader

            MessageBox.Show("User has been deleted.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
                TextBoxNUsern.Text = ""
                TextBoxNEmail.Text = ""
                TextBoxNPass.Text = ""
                TextBoxNPhone.Text = ""
                TextBoxNCity.Text = ""
                MySqlConn.Close()
            Catch ex As MySqlException
                MessageBox.Show(ex.Message)
            Finally
                MySqlConn.Dispose()
            End Try
        loaddatabase_table()
    End Sub

    Private Sub loaddatabase_table()
        MySqlConn = New MySqlConnection
        MySqlConn.ConnectionString =
            "server=localhost;userid=root;password=HIDDEN;database=syscore"
        Dim MySqlDAd As New MySqlDataAdapter
        Dim dbDataSet As New DataTable
        Dim bSource As New BindingSource

        Try
            MySqlConn.Open()
            Dim Query As String
            Query = "select * from syscore.normaluser"
            MySqlCmd = New MySqlCommand(Query, MySqlConn)
            MySqlDAd.SelectCommand = MySqlCmd
            MySqlDAd.Fill(dbDataSet)
            bSource.DataSource = dbDataSet
            DataGridView1.DataSource = bSource
            MySqlDAd.Update(dbDataSet)
            TextBoxNUsern.Text = ""
            TextBoxNEmail.Text = ""
            TextBoxNPass.Text = ""
            TextBoxNPhone.Text = ""
            TextBoxNCity.Text = ""
            MySqlConn.Close()
        Catch ex As MySqlException
            MessageBox.Show(ex.Message)
        Finally
            MySqlConn.Dispose()
        End Try
    End Sub
    Private Sub ButtonLDB_Click(sender As Object, e As EventArgs) Handles ButtonLDB.Click
        MySqlConn = New MySqlConnection
        MySqlConn.ConnectionString =
            "server=localhost;userid=root;password=HIDDEN;database=syscore"
        Dim MySqlDAd As New MySqlDataAdapter
        Dim dbDataSet As New DataTable
        Dim bSource As New BindingSource

        Try
            MySqlConn.Open()
            Dim Query As String
            Query = "select * from syscore.normaluser"
            MySqlCmd = New MySqlCommand(Query, MySqlConn)
            MySqlDAd.SelectCommand = MySqlCmd
            MySqlDAd.Fill(dbDataSet)
            bSource.DataSource = dbDataSet
            DataGridView1.DataSource = bSource
            MySqlDAd.Update(dbDataSet)
            TextBoxNUsern.Text = ""
            TextBoxNEmail.Text = ""
            TextBoxNPass.Text = ""
            TextBoxNPhone.Text = ""
            TextBoxNCity.Text = ""
            MySqlConn.Close()
        Catch ex As MySqlException
            MessageBox.Show(ex.Message)
        Finally
            MySqlConn.Dispose()
        End Try
        loaddatabase_table()
    End Sub

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        If e.RowIndex >= 0 Then
            Dim gridrow As DataGridViewRow
            gridrow = Me.DataGridView1.Rows(e.RowIndex)

            TextBoxNUsern.Text = gridrow.Cells("nusername").Value.ToString
            TextBoxNEmail.Text = gridrow.Cells("nemail").Value.ToString
            TextBoxNPass.Text = gridrow.Cells("npass").Value.ToString
            TextBoxNPhone.Text = gridrow.Cells("nphone").Value.ToString
            TextBoxNCity.Text = gridrow.Cells("ncity").Value.ToString
        End If
    End Sub

    Private Sub TextBoxSdbgrid_TextChanged(sender As Object, e As EventArgs) Handles TextBoxSdbgrid.TextChanged
        Dim DV As New DataView(dbDT)
       ' ERROR HERE:
        DV.RowFilter = String.Format("nusername Like '%{0}%'", TextBoxSdbgrid.Text)
        DataGridView1.DataSource = DV
    End Sub

    Private Sub ReportToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ReportToolStripMenuItem.Click
        MessageBox.Show("To get support or report do it on skype gangsteris33 we are always online until night.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

    Private Sub LoginToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles LoginToolStripMenuItem.Click
        Form1.Show()
        TextBoxNCity.Text = ""
        TextBoxNEmail.Text = ""
        TextBoxNPass.Text = ""
        TextBoxNPhone.Text = ""
        TextBoxNUsern.Text = ""
        TextBoxSdbgrid.Text = ""
        Me.Hide()
    End Sub

    Private Sub ExitToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExitToolStripMenuItem.Click
        Application.Exit()
    End Sub

    Private Sub ButtonClearChat_Click(sender As Object, e As EventArgs) Handles ButtonClearChat.Click
        MySqlConn = New MySqlConnection
        MySqlConn.ConnectionString =
            "server=localhost;userid=root;password=HIDDEN;database=sysinfo"
        Dim MySqlRea As MySqlDataReader

        Try
            MySqlConn.Open()
            Dim Query As String
            Query = "delete from syscore.normaluser"
            MySqlCmd = New MySqlCommand(Query, MySqlConn)
            MySqlRea = MySqlCmd.ExecuteReader

            MessageBox.Show("Chat has been cleaned.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
            MySqlConn.Close()
        Catch ex As MySqlException
            MessageBox.Show(ex.Message)
        Finally
            MySqlConn.Dispose()
        End Try
    End Sub
End Class

希望你能解决这个问题。我一直在寻找原因，但不知道。可以编辑此内容的人请修复此错误的代码粘贴。 (网站问题不是我的技能)。如果有人想将其标记为重复，请在阻止此主题之前使用我的代码修复我的问题。

Answer 1

在方法 loaddatabase_table 中，在数据集填充后添加此行

Private Sub loaddatabase_table()
    Dim dbDataSet As New DataTable ' This is a misleading name. 

    ....
    MySqlDAd.Fill(dbDataSet)
    ' Add this line
    dbDT = dbDataSet
End Sub

现在，全局变量dbDT指向从数据库加载的有效表，因此，当您构建DataView时，列nusername出现在源数据表中

请注意，删除按钮中的代码无法工作，因为您缺少 nusername 和要删除的值之间的=

最后，虽然调用 ExecuteReader 也适用于 INSERT/UPDATE/DELETE 查询，但正确的使用方法是 ExecuteNonQuery，以避免当您没有从命令中读取任何内容时不必要地构建 DataReader