列表框选择一个列表框而不是所有列表框 [英] ListBoxes Select one list box as opposed to all listboxes

问题描述

大家好，



我有一个包含四个列表框的表格。作者，出版商，YEARPUBLISHED和GENRE。



目前代码工作正常，只需在四个方框中勾选一个项目。我想要做的就是点击少于四个并得到一个结果。例如，如果我只是想要一位作者的详细信息，我只想勾选作者框中的名称，获取这些细节，并能够忽略其余的框。

我试图摆弄代码但是似乎无法破解它。任何帮助都非常感谢

 公众 类 Form1 
 私有  Sub  Button1_Click（ ByVal  sender  As  System。 Object ， ByVal  e  As  System.EventArgs）句柄 Button1.Click 
 
  Dim  con  As  新 OleDb.OleDbConnection 
 
  Dim  dbProvider  As   String  
  Dim  dbSource  As   String  
 
  Dim  ds  As   New  DataSet 
 
  Dim  tables  As  DataTableCollection = ds.Tables 
 
  Dim  source1 作为 新 BindingSource（）
 
  Dim  da  As  新 OleDb.OleDbDataAdapter 
 
  Dim  sql  As   String  
 
 
 
  Dim  aa 作为  String  = authorList.Text 
 
  Dim  bb 作为 字符串 = publisherList.Text 
 
  Dim  cc  As   String  = yearpublishedList.Text 
 
  Dim  dd  As   String  = genreList.Text 
 
 
 
 dbProvider =    PROVIDER = Microsoft.ACE.OLEDB.12.0; 
 
 dbSource =   数据源= C：\Documents and Settings \Administrator\Desktop\Authors.accdb 
 
 con .ConnectionString = dbProvider& dbSource 
 
 con.Open（）
 
 
 
 sql =   SELECT * FROM books WHERE author ='& aa&  'AND publisher ='& bb&  'AND yearpublished ='& cc&  'AND genre ='& dd&  ' 
 
 
 
 
 
 da = 新 OleDb.OleDbDataAdapter（sql，con）
 
 da.Fill（ds， 作者）
 
 
  Dim  view1 作为 新 DataView（表格（ 0 ））
 
 source1.DataSource = view1 
 
 DataGridView1.DataSource = view1 
 
 DataGridView1.Refresh（）
 
 
 
 
 
 
 
 
 
 
 
 con.Close（）
 
 
 
 结束  Sub  

解决方案

根据Wes Aday的建议，您需要逐步构建SQL字符串。最有效的方法是使用 StringBuilder ，您将需要

 Imports System.Text 

同时构建如果字符串中需要AND，你需要知道你的字符串，所以你需要一个布尔或整数来跟踪你已经应用了多少个过滤器。

你还应该使用参数化查询 [ ^ ]以帮助防止sql注入 - 永远不会构建一个通过连接用户可以输入的字符串来进行SQL查询！



这样的东西应该可以工作

 ' 使用查询的第一部分初始化字符串构建器 
  Dim  sql  As  StringBuilder = 新 StringBuilder（  SELECT * FROM books WHERE）
 ' 如果我们已添加过滤器 $，则此布尔值将设置为True b $ b  Dim  AndRequired  As   Boolean  =  False  
 ' 我们将添加参数此命令 
  Dim  cmd 作为 OleDbCommand = 新 OleDbCommand（）
 
 cmd.Connection = con 
 
 ' 我们在aa中有任何内容（authorList.Text） 
 如果 aa.Length>  0  然后 
 ' < span class =code-comment>将过滤器添加到SQL  
 sql.Append（  author =？）
 
 ' 将值添加到参数中list  
 cmd.Parameters.Add（  @ author，OleDbType。 VarChar， 30 ）。值= aa 
 ' 将参数中的数字更改为列的大小 
 
 ' 我们已添加过滤条件的标记 
 AndRequired =  True  
 结束 如果 
 
 ' 下一步检查...  
 如果 bb.Length>  0  然后 
 ' < span class =code-comment>如果我们已经添加了过滤器，那么我们需要AND 
 如果 AndRequired 然后 sql.Append（  AND）
 ' 其余与aa  
 sql.Append（  publisher =？）
 cmd.Parameters.Add（  @ publisher，OleDbType.VarChar， 30 ）。Value = bb 
 AndRequired =  True  
 结束 如果 
 如果 cc.Length>  0  然后 
 如果 AndRequired 然后 sql.Append（  AND ）
 sql.Append（  yearpublished =？）
 cmd.Parameters .Add（  @ yearpublished，OleDbType.VarChar， 4 ）。值= cc 
 AndRequired =  True  
 结束 如果 
 如果 dd.Length>  0  然后 
 如果 AndRequired 然后 sql.Append（  AND ）
 sql.Append（  genre =？）
 cmd.Parameters .Add（  @ genre，OleDbType.VarChar， 30 ）。值= dd 
 AndRequired = 真 ' 并非绝对必要，但您可能希望稍后添加更多内容，以便将其放入其中 
 结束 如果 
 
 ' 让命令知道sql我们已经构建了 - 注意.ToString是必需的 
 cmd.CommandT ext = sql.ToString（）
 da = 新 OleDbDataAdapter（）
 da.SelectCommand = cmd 
 da.Fill（ds，  作者）

寒冷，



非常感谢您的详细回复。我可以看到您的代码中的逻辑，但在几个小时后尝试重新安排我的代码周围的代码我不断提出以下错误。



错误1未定义类型'OleDbType'。

错误2本地变量'sql'已在当前块中声明。

错误3类型'OleDbCommand'未定义。

错误8未定义类型'OleDbDataAdapter'。



这是代码的最新尝试，我不太确定我是否应该包含我的旧sql字符串，但是我有时间我会得到更多错误提示非常感谢。

  Imports  System.Text 
 
 
 公开 类 Form1 
 
 
 私人  Sub  Button1_Click（ ByVal  sender  As  System。 对象， ByVal  e  As  System.EventArgs ）句柄 Button1.Click 
 
  Dim  con 作为 新 OleDb.OleDbConnection 
 
  Dim  dbProvider 作为 字符串 
  Dim  dbSource  As   String  
 
  Dim  ds  As  新 DataSet 
 
  Dim 表 As  DataTableCollection = ds.Tables 
 
  Dim  source1 作为 新 BindingSource（）
 
  Dim  da  As  新 OleDb.OleDbDataAdapter 
  Dim  tt  As  新 OleDbType 
  Dim  sql  As   String  
 
 
 
  Dim  aa  As   String  = authorList.Text 
 
  Dim  bb 作为 字符串 = publisherList .Text 
 
  Dim  cc  As  字符串 = yearpublishedList.Text 
 
  Dim  dd  As  < span class =code-keyword> String  = genreList.Text 
 ' 初始化字符串构建器与查询的第一部分 
  Dim  sql  As  StringBuilder = < span class =code-keyword>新 StringBuilder（  SELECT * FROM books WHERE）
 ' 如果我们已添加过滤器，则此布尔值将设置为True  
  Dim  AndRequired  As   Boolean  =  False  
 ' 我们将向此命令添加参数 
  Dim  cmd 作为 OleDbCommand = 新 OleDbCommand（）
 
 cmd.Connection = con 
 
 ' 我们在aa中有什么（authorList.Text） 
 如果 aa.Length>  0  然后 
 ' < span class =code-comment>将过滤器添加到SQL  
 sql.Append（  author =？）
 
 ' 将值添加到参数中list  
 cmd.Parameters.Add（  @ author，OleDbType。 VarChar， 30 ）。值= aa 
 ' 将参数中的数字更改为列的大小 
 
 ' 我们已添加过滤条件的标记 
 AndRequired =  True  
 结束 如果 
 
 ' 下一步检查......  
 如果 bb.Length>  0  然后 
 ' < span class =code-comment>如果我们已经添加了过滤器，那么我们需要AND 
 如果 AndRequired 然后 sql.Append（  AND）
 ' 其余与aa  
 sql.Append（  publisher =？）
 cmd.Parameters.Add（  @ publisher，OleDbType.VarChar， 30 ）。Value = bb 
 AndRequired =  True  
 结束 如果 
 如果 cc.Length>  0  然后 
 如果 AndRequired 然后 sql.Append（  AND ）
 sql.Append（  yearpublished =？）
 cmd.Parameters .Add（  @ yearpublished，OleDbType.VarChar， 4 ）。值= cc 
 AndRequired =  True  
 结束 如果 
 如果 dd.Length>  0  然后 
 如果 AndRequired 然后 sql.Append（  AND ）
 sql.Append（  genre =？）
 cmd.Parameters .Add（  @ genre，OleDbType.VarChar， 30 ）。值= dd 
 AndRequired = 真 ' 并非绝对必要，但您可能希望稍后添加更多内容，以便将其放入其中 
 结束 如果 
 
 ' 让命令知道sql我们已经构建了 - 请注意e .ToString是必需的 
 cmd.CommandText = sql.ToString（）
 da = 新 OleDbDataAdapter（）
 da.SelectCommand = cmd 
 da.Fill（ds， 作者）
 
 
 dbProvider =   PROVIDER = Microsoft.ACE.OLEDB.12.0; 
 
 dbSource =  数据源= C：\Documents and Settings \Administrator \\ \\Desktop\Authors.accdb 
 
 con.ConnectionString = dbProvider& dbSource 
 
 con.Open（）
 
 
 
 '  sql =SELECT * FROM books WHERE author ='& aa& 'AND publisher ='& bb& 'AND yearpublished ='& cc& 'AND genre ='& dd& ' 
 
 
 
 
 
 da = 新 OleDb。 OleDbDataAdapter（sql，con）
 
 da.Fill（ds， 作者）
 
 
  Dim  view1  As  新 DataView（表格（ 0 ））
 
 source1.DataSource = view1 
 
 DataGridView1.DataSource = view1 
 
 DataGridView1.Refresh（）
 
 
 
 
 
 
 
 
 
 
 
 con.Close（）
 
 
 
 结束  Sub  
 
 结束 类 

Hi all,

I have a form with four list boxes .AUTHORS ,PUBLISHERS,YEARPUBLISHED and GENRE.

At present the code works fine providing I tick an item in all of the four boxes.What I wanted to be able to do is click on less than the four and get a result.For example if I just wanted the details of one author ,I would just like to tick on the name in the authors box get those details up and be able to ignore the remainder of the boxes.
I have tried fiddling about with the code but just can't seem to crack it .Any help greatly appreciated

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim con As New OleDb.OleDbConnection

        Dim dbProvider As String
        Dim dbSource As String

        Dim ds As New DataSet

        Dim tables As DataTableCollection = ds.Tables

        Dim source1 As New BindingSource()

        Dim da As New OleDb.OleDbDataAdapter

        Dim sql As String



        Dim aa As String = authorList.Text

        Dim bb As String = publisherList.Text

        Dim cc As String = yearpublishedList.Text

        Dim dd As String = genreList.Text



        dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"

        dbSource = "Data Source = C:\Documents and Settings\Administrator\Desktop\Authors.accdb"

        con.ConnectionString = dbProvider & dbSource

        con.Open()

       

        sql = "SELECT * FROM books WHERE author = '" & aa & "' AND publisher = '" & bb & "' AND yearpublished = '" & cc & "' AND genre = '" & dd & "' "


       
        

        da = New OleDb.OleDbDataAdapter(sql, con)

        da.Fill(ds, "Authors")
        

        Dim view1 As New DataView(tables(0))

        source1.DataSource = view1

        DataGridView1.DataSource = view1

        DataGridView1.Refresh()











        con.Close()



    End Sub

解决方案

As suggest by Wes Aday, you need to build up your SQL string bit by bit. The most efficient way of doing this is to use a StringBuilder for which you will need

Imports System.Text

Whilst building up your string you will need to know if the "AND"s are required in the string so you will need a Boolean or an integer to keep track of how many filters you have already applied.
You should also use Parameterized queries[^] to help prevent sql injection - never build a sql query by concatenating strings that can be entered by a user!

Something like this should work

'Initialise a string builder with the first part of the query
Dim sql As StringBuilder = New StringBuilder("SELECT * FROM books WHERE ")
'This Boolean will be set to True if we have added a filter already
Dim AndRequired As Boolean = False
'We will be adding parameters to this Command
Dim cmd As OleDbCommand = New OleDbCommand()

cmd.Connection = con

'Do we have anything in aa (authorList.Text)
If aa.Length > 0 Then
    'Add the filter to the SQL
    sql.Append("author = ?")
    
    'Add the value into the parameter list
    cmd.Parameters.Add("@author", OleDbType.VarChar, 30).Value = aa     
    'Change the numbers in the parameter to the sizes of your columns
    
    'Flag that we have already added a filter
    AndRequired = True
End If

'Next check ...
If bb.Length > 0 Then
    'If we have already added a filter then we need the " AND "
    If AndRequired Then sql.Append(" AND ")
    'The rest is the same as for aa
    sql.Append("publisher = ?")
    cmd.Parameters.Add("@publisher", OleDbType.VarChar, 30).Value = bb
    AndRequired = True
End If
If cc.Length > 0 Then
    If AndRequired Then sql.Append(" AND ")
    sql.Append("yearpublished = ?")
    cmd.Parameters.Add("@yearpublished", OleDbType.VarChar, 4).Value = cc
    AndRequired = True
End If
If dd.Length > 0 Then
    If AndRequired Then sql.Append(" AND ")
    sql.Append("genre = ?")
    cmd.Parameters.Add("@genre", OleDbType.VarChar, 30).Value = dd
    AndRequired = True  'Not strictly necessary but you might want to add more stuff later so put it in anyway
End If

'Let the Command know the sql we've constructed - note the .ToString IS required
cmd.CommandText = sql.ToString()
da = New OleDbDataAdapter()
da.SelectCommand = cmd
da.Fill(ds, "Authors")

Hi Chill,

Many many thanks for your detailed reply.I can see the logic in your code but after several hours of attempting to re-arrange my code around your code I keep coming up with the following errors.

Error 1 Type 'OleDbType' is not defined.
Error 2 Local variable 'sql' is already declared in the current block.
Error 3 Type 'OleDbCommand' is not defined.
Error 8 Type 'OleDbDataAdapter' is not defined.

Here is the latest attempt at the code,I am not too sure if I should include my old sql string,but the times I have I get even more errors any hints much appreciated.

Imports System.Text


Public Class Form1


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim con As New OleDb.OleDbConnection

        Dim dbProvider As String
        Dim dbSource As String

        Dim ds As New DataSet

        Dim tables As DataTableCollection = ds.Tables

        Dim source1 As New BindingSource()

        Dim da As New OleDb.OleDbDataAdapter
        Dim tt As New OleDbType
        Dim sql As String



        Dim aa As String = authorList.Text

        Dim bb As String = publisherList.Text

        Dim cc As String = yearpublishedList.Text

        Dim dd As String = genreList.Text
        'Initialise a string builder with the first part of the query
        Dim sql As StringBuilder = New StringBuilder("SELECT * FROM books WHERE ")
        'This Boolean will be set to True if we have added a filter already
        Dim AndRequired As Boolean = False
        'We will be adding parameters to this Command
        Dim cmd As OleDbCommand = New OleDbCommand()

        cmd.Connection = con

        'Do we have anything in aa (authorList.Text)
        If aa.Length > 0 Then
            'Add the filter to the SQL
            sql.Append("author = ?")

            'Add the value into the parameter list
            cmd.Parameters.Add("@author", OleDbType.VarChar, 30).Value = aa
            'Change the numbers in the parameter to the sizes of your columns

            'Flag that we have already added a filter
            AndRequired = True
        End If

        'Next check ...
        If bb.Length > 0 Then
            'If we have already added a filter then we need the " AND "
            If AndRequired Then sql.Append(" AND ")
            'The rest is the same as for aa
            sql.Append("publisher = ?")
            cmd.Parameters.Add("@publisher", OleDbType.VarChar, 30).Value = bb
            AndRequired = True
        End If
        If cc.Length > 0 Then
            If AndRequired Then sql.Append(" AND ")
            sql.Append("yearpublished = ?")
            cmd.Parameters.Add("@yearpublished", OleDbType.VarChar, 4).Value = cc
            AndRequired = True
        End If
        If dd.Length > 0 Then
            If AndRequired Then sql.Append(" AND ")
            sql.Append("genre = ?")
            cmd.Parameters.Add("@genre", OleDbType.VarChar, 30).Value = dd
            AndRequired = True  'Not strictly necessary but you might want to add more stuff later so put it in anyway
        End If

        'Let the Command know the sql we've constructed - note the .ToString IS required
        cmd.CommandText = sql.ToString()
        da = New OleDbDataAdapter()
        da.SelectCommand = cmd
        da.Fill(ds, "Authors")


        dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"

        dbSource = "Data Source = C:\Documents and Settings\Administrator\Desktop\Authors.accdb"

        con.ConnectionString = dbProvider & dbSource

        con.Open()



        'sql = "SELECT * FROM books WHERE author = '" & aa & "' AND publisher = '" & bb & "' AND yearpublished = '" & cc & "' AND genre = '" & dd & "' "





        da = New OleDb.OleDbDataAdapter(sql, con)

        da.Fill(ds, "Authors")


        Dim view1 As New DataView(tables(0))

        source1.DataSource = view1

        DataGridView1.DataSource = view1

        DataGridView1.Refresh()











        con.Close()



    End Sub

End Class

这篇关于列表框选择一个列表框而不是所有列表框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！