mysql - 使用两个文本框的输入在 ListView 中搜索数据

Question

我正在使用 VB.NET 和 MySQL 处理基本银行系统，但在显示交易历史记录中的特定数据时遇到问题。问题是，我想显示从特定日期到特定日期(例如边界或其他内容)发生的所有交易。这是我想使用的代码行，但我无法让它工作:

Dim view As String = "select * from tblhistory where accountnumber = '"& txtAccount.Text & "' 和日期如 '"& txtFrom.Text & "%' 或日期如 '"& txtTo.Text & "' ”

我想到的概念是，使用 LIKE 运算符，使用户输入的边界仅显示用户想要查看的特定交易，而不是显示所有用户的交易，并且很难找到该特定交易。

你们能帮我解决这个问题吗？非常感谢。再会。顺便说一句，这是完整的代码:

Public Class TransactionHistory
Public rights As String
Public log As String
Public accountnum As String
Private Sub btnProceed_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProceed.Click
    lvHistory.Items.Clear()
    Dim con As New MySqlConnection("server = localhost ; uid = root ; database = dbbank")
    Dim view As String = "select * from tblhistory where accountnumber = '" & txtAccount.Text & "' and date like '" & txtFrom.Text & "%' or date like '" & txtTo.Text & "'"
    Dim cmd As New MySqlCommand(view, con)

    con.Open()
    Dim data As MySqlDataReader = cmd.ExecuteReader()

    While data.Read()
        Dim entries As ListViewItem
        entries = lvHistory.Items.Add(data(0))
        With entries
            .SubItems.Add(data(1))
            .SubItems.Add(data(2))
            .SubItems.Add(data(3))
            .SubItems.Add(data(4))
        End With
    End While

    If rights = "manager" Then
        Manager.Show()
        Me.Close()
    ElseIf rights = "teller" Then
        Teller.Show()
        Me.Close()
    End If
End Sub

Private Sub cmbType_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbType.SelectedIndexChanged
    lvHistory.Items.Clear()
    Dim con As New MySqlConnection("server = localhost ; uid = root ; database = dbbank")
    Dim view As String = "select * from tblhistory where accountnumber = '" & txtAccount.Text & "' and transactiontype = '" & cmbType.SelectedItem & "'"
    Dim cmd As New MySqlCommand(view, con)

    con.Open()
    Dim data As MySqlDataReader = cmd.ExecuteReader()

    While data.Read()
        Dim entries As ListViewItem
        entries = lvHistory.Items.Add(data(0))
        With entries
            .SubItems.Add(data(1))
            .SubItems.Add(data(2))
            .SubItems.Add(data(3))
            .SubItems.Add(data(4))
        End With
    End While
End Sub

Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
    Me.Close()
End Sub

Private Sub TransactionHistory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    txtAccount.Text = accountnum
End Sub

下课

Answer 1

“或”导致了您的问题。这实际上就像要求“此帐户的所有内容和类似 FROM 的日期”，并且还给我“带有类似 TO 的日期的所有内容”。在日期条件周围添加括号可以解决这个问题。

我认为这可以解决一些问题:

Dim view As String = "select * from tblhistory where accountnumber = '" 
& txtAccount.Text & "' and (date like '" & txtFrom.Text & "%' or date like '" & txtTo.Text & "')"

但是，请记住，这只会查找特定日期内的交易，而不是日期之间的交易。

也试试

Dim view As String = "select * from tblhistory where accountnumber = '" 
    & 
txtAccount.Text & "' and date between '" & txtFrom.Text & "' and '" & txtTo.Text & "'"

最后，使用参数。