时间戳

首页 > 解决方案 > vb.net 无法在 OR 语句中获取数据库值

sql - vb.net 无法在 OR 语句中获取数据库值

问题描述

我有kriteria列名的数据库，其中kode包含值kriteria 1kriteria 6

我希望用户可以从中选择 3 到 6 个值kode，它将在 datagridview 中显示为矩阵...

如果用户选择 6 个标准则没有问题，并且它显示矩阵 6x6 像这样
...但如果用户选择少于 6 个标准或参数为空，则它什么也不显示...

这是我的代码

Private Sub perhitungankriteria_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        For Each item In menuuser.ListBox2.Items
            list2.Add(item)
        Next
        connect()

    End Sub

    Sub connect()
        Dim adapter As OleDbDataReader
        Dim cmd As OleDbCommand
        cmd = New OleDbCommand("SELECT kode FROM kriteria WHERE kode=@kriteria1 OR kode=@kriteria2 OR kode=@kriteria3 OR kode=@kriteria4 OR kode=@kriteria5 OR kode=@kriteria6", konek)
        cmd.Parameters.Add("@kriteria1", OleDbType.VarChar, 50).Value = list2(0)
        cmd.Parameters.Add("@kriteria2", OleDbType.VarChar, 50).Value = list2(1)
        cmd.Parameters.Add("@kriteria3", OleDbType.VarChar, 50).Value = list2(2)
        cmd.Parameters.Add("@kriteria4", OleDbType.VarChar, 50).Value = list2(3)
        cmd.Parameters.Add("@kriteria5", OleDbType.VarChar, 50).Value = list2(4)
        cmd.Parameters.Add("@kriteria6", OleDbType.VarChar, 50).Value = list2(5)
        adapter = cmd.ExecuteReader

        DataGridView1.Columns.Clear()

        While adapter.Read
            list.Add(adapter("kode"))
            DataGridView1.Columns.Add(adapter("kode"), adapter("kode"))
        End While

我在 sql SELECT 语句中尝试了 IN 而不是 WHERE 查询，但出现了同样的问题

标签： sqlvb.netms-access

解决方案

在用户从列表框中进行选择后，通过单击按钮调用 BuildCommand。

Dim list2() As String = {"kriteria1", "kriteria2", "kriteria3", "kriteria4", "kriteria5", "kriteria6"}
    Private Sub Form4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ListBox1.DataSource = list2

Private Sub BuildCommand()
        Dim sb As New StringBuilder("SELECT kode FROM kriteria WHERE kode = ")
        Dim ct As Integer = ListBox1.SelectedItems.Count
        Dim cn As New OleDbConnection("Your connection string")
        Dim cmd As New OleDbCommand
        cmd.Parameters.Add("@kriteria1", OleDbType.VarChar, 50).Value = list2(0)
        cmd.Parameters.Add("@kriteria2", OleDbType.VarChar, 50).Value = list2(1)
        cmd.Parameters.Add("@kriteria3", OleDbType.VarChar, 50).Value = list2(2)
        cmd.Parameters.Add("@kriteria4", OleDbType.VarChar, 50).Value = list2(3)
        cmd.Parameters.Add("@kriteria5", OleDbType.VarChar, 50).Value = list2(4)
        cmd.Parameters.Add("@kriteria6", OleDbType.VarChar, 50).Value = list2(5)
        If ListBox1.SelectedItems.Contains(ListBox1.Items(0)) Then
            sb.Append("@kriteria1 OR kode = ")
        End If
        If ListBox1.SelectedItems.Contains(ListBox1.Items(1)) Then
            sb.Append("@kriteria2 OR kode = ")
        End If
        If ListBox1.SelectedItems.Contains(ListBox1.Items(2)) Then
            sb.Append("@kriteria3 OR kode = ")
        End If
        If ListBox1.SelectedItems.Contains(ListBox1.Items(3)) Then
            sb.Append("@kriteria4 OR kode = ")
        End If
        If ListBox1.SelectedItems.Contains(ListBox1.Items(4)) Then
            sb.Append("@kriteria5 OR kode = ")
        End If
        If ListBox1.SelectedItems.Contains(ListBox1.Items(5)) Then
            sb.Append("@kriteria6;")
        End If
        Dim strSQL As String = sb.ToString
        If strSQL.EndsWith("= ") Then
            Dim index As Integer = strSQL.LastIndexOf("O") 'last capital O
            strSQL = strSQL.Remove(index)
            strSQL &= ";"
        End If
        Debug.Print(strSQL) 'Check the immediate window
        cmd.CommandText = strSQL
        cmd.Connection = cn
        'use your command as you wish
    End Sub

推荐阅读