请教大神，vbnet sql多条件查询并显示的问题，谢过！！！

COSByVal 2024-06-19 10:07:21

Button1为查询按钮，我用以下代码为了实现多条件查询，并将查询结果显示在datagridview中，提示 “Parameters不是string成员” ，请教该怎么解决？谢谢！！！

Imports System.Data.SqlClient

Public Sub FilterAndDisplayData(ByVal connectionString As String, ByVal query As String)
Using connection As New SqlConnection(connectionString)
Dim adapter As New SqlDataAdapter(query, connection)
Dim table As New DataTable()
connection.Open()
adapter.Fill(table)
connection.Close()
DataGridView1.DataSource = table
End Using
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim connectionString As String = "Data Source=XTZJ-20230713LX\COSBYVAL;Initial Catalog=SJBG;Integrated Security=True"

Dim Value_AA, Value_AB, Value_B, Value_C, Value_D, Value_E, Value_F As String
If Me.ComboBox1.Text <> "" Then
Value_AA = Me.ComboBox1.Text
Else
Value_AA = "*"
End If
If Me.ComboBox3.Text <> "" Then
Value_AB = Me.ComboBox3.Text
Else
Value_AB = "*"
End If
If Me.TextBox1.Text <> "" Then
Value_B = Me.TextBox1.Text
Else
Value_B = "*"
End If
If Me.ComboBox16.Text <> "" Then
Value_C = Me.ComboBox16.Text
Else
Value_C = "*"
End If
If Me.ComboBox4.Text <> "" Then
Value_D = Me.ComboBox4.Text
Else
Value_D = "*"
End If
If Me.ComboBox8.Text <> "" Then
Value_E = Me.ComboBox8.Text
Else
Value_E = "*"
End If
If Me.TextBox20.Text <> "" Then
Value_F = Me.TextBox20.Text
Else
Value_F = "*"
End If

Dim query As String = "SELECT * FROM KJS" & Strings.Left(Me.ComboBox2.Text, 4) & " WHERE zsaxm = @value1 AND zsbxm = @value2 AND bsdwmc = @value2 AND ywlx = @value2 AND zs = @value2 AND bgwh = @value2 AND bz = @value2"

Dim parameters As SqlParameter() = {
New SqlParameter("@value1", SqlDbType.VarChar, 100) With {.Value = Value_AA},
New SqlParameter("@value2", SqlDbType.VarChar, 100) With {.Value = Value_AB},
New SqlParameter("@value3", SqlDbType.VarChar, 100) With {.Value = Value_B},
New SqlParameter("@value4", SqlDbType.VarChar, 100) With {.Value = Value_C},
New SqlParameter("@value5", SqlDbType.VarChar, 100) With {.Value = Value_D},
New SqlParameter("@value6", SqlDbType.VarChar, 100) With {.Value = Value_E},
New SqlParameter("@value7", SqlDbType.VarChar, 100) With {.Value = Value_F}
}

For Each parameter In parameters
Command.Parameters.Add(parameter)
Next

FilterAndDisplayData(connectionString, query)
End Sub

...全文

166 1 打赏收藏转发到动态举报

写回复

用AI写文章

1 条回复

切换为时间正序

请发表友善的回复…

发表回复

清风明月9987321 2024-06-28

打赏
举报

在您的代码中，您尝试将参数添加到 SQL 查询中，但没有正确地创建和使用 SqlCommand 对象。这是您遇到错误的主要原因。

此外，您的查询字符串和参数的数量和位置也需要进行调整。您需要确保每个参数都正确映射到查询中的占位符。

下面是调整后的代码：

创建 SqlCommand 对象并添加参数。
使用 SqlCommand 对象执行查询并填充 DataTable。
修改后的代码如下：


Imports System.Data.SqlClient

Public Sub FilterAndDisplayData(ByVal connectionString As String, ByVal query As String, ByVal parameters As SqlParameter())
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(query, connection)
        command.Parameters.AddRange(parameters)
        Dim adapter As New SqlDataAdapter(command)
        Dim table As New DataTable()
        connection.Open()
        adapter.Fill(table)
        connection.Close()
        DataGridView1.DataSource = table
    End Using
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim connectionString As String = "Data Source=XTZJ-20230713LX\COSBYVAL;Initial Catalog=SJBG;Integrated Security=True"

    Dim Value_AA, Value_AB, Value_B, Value_C, Value_D, Value_E, Value_F As String
    If Me.ComboBox1.Text <> "" Then
        Value_AA = Me.ComboBox1.Text
    Else
        Value_AA = "*"
    End If
    If Me.ComboBox3.Text <> "" Then
        Value_AB = Me.ComboBox3.Text
    Else
        Value_AB = "*"
    End If
    If Me.TextBox1.Text <> "" Then
        Value_B = Me.TextBox1.Text
    Else
        Value_B = "*"
    End If
    If Me.ComboBox16.Text <> "" Then
        Value_C = Me.ComboBox16.Text
    Else
        Value_C = "*"
    End If
    If Me.ComboBox4.Text <> "" Then
        Value_D = Me.ComboBox4.Text
    Else
        Value_D = "*"
    End If
    If Me.ComboBox8.Text <> "" Then
        Value_E = Me.ComboBox8.Text
    Else
        Value_E = "*"
    End If
    If Me.TextBox20.Text <> "" Then
        Value_F = Me.TextBox20.Text
    Else
        Value_F = "*"
    End If

    Dim query As String = "SELECT * FROM KJS" & Strings.Left(Me.ComboBox2.Text, 4) & " WHERE zsaxm = @value1 AND zsbxm = @value2 AND bsdwmc = @value3 AND ywlx = @value4 AND zs = @value5 AND bgwh = @value6 AND bz = @value7"

    Dim parameters As SqlParameter() = {
        New SqlParameter("@value1", SqlDbType.VarChar, 100) With {.Value = Value_AA},
        New SqlParameter("@value2", SqlDbType.VarChar, 100) With {.Value = Value_AB},
        New SqlParameter("@value3", SqlDbType.VarChar, 100) With {.Value = Value_B},
        New SqlParameter("@value4", SqlDbType.VarChar, 100) With {.Value = Value_C},
        New SqlParameter("@value5", SqlDbType.VarChar, 100) With {.Value = Value_D},
        New SqlParameter("@value6", SqlDbType.VarChar, 100) With {.Value = Value_E},
        New SqlParameter("@value7", SqlDbType.VarChar, 100) With {.Value = Value_F}
    }

    FilterAndDisplayData(connectionString, query, parameters)
End Sub