如何在VB.NET中使用SQL语句来过滤多个结果 [英] How do I use an SQL statement in VB.NET to filter multiple results

问题描述

我一直在研究如何在vb.net中使用sql语句来过滤基于文本框的结果。



这里是我的代码：

I have been working on a way to use an sql statement in vb.net to filter results based upon textboxes.

here is my code:

Private Sub filteredresults1()

      Dim sqlconn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=c:\users\jj\documents\visual studio 2017\Projects\WindowsApplication3\WindowsApplication3\Databases\" + ComboBox1.Text + ".mdf;Integrated Security=True")
      Dim sqladaptor1 = New SqlDataAdapter("select * from " + "[" + TreeView1.SelectedNode.Text.ToString + "]" + " where Gender = " + "'" + TextBox3.Text + "'", sqlconn)

      sqlconn.Open()
      Dim sql1 As String = "select * from " + "[" + TreeView1.SelectedNode.Text.ToString + "]" + " where Gender = " + "'" + TextBox3.Text + "'"

      Dim cmd As SqlClient.SqlCommand
      cmd = New SqlClient.SqlCommand(sql1, sqlconn)

      Dim dt As New DataTable
      Dim ds As New DataSet
      sqladaptor1.Fill(dt)
      DataGridView1.DataSource = dt

  End Sub

 Private Sub filteredresults()

        Dim sqlconn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=c:\users\jj\documents\visual studio 2017\Projects\WindowsApplication3\WindowsApplication3\Databases\" + ComboBox1.Text + ".mdf;Integrated Security=True")
        Dim sqladaptor = New SqlDataAdapter("select * from " + "[" + TreeView1.SelectedNode.Text.ToString + "]" + " where [Year of employment] between " + TextBox1.Text + " and " + TextBox2.Text, sqlconn)

        sqlconn.Open()

        Dim sql As String = "select * from " + "[" + TreeView1.SelectedNode.Text.ToString + "]" + " where [Year of employment] between " + TextBox1.Text + " and " + TextBox2.Text

        Dim cmd As SqlClient.SqlCommand
        cmd = New SqlClient.SqlCommand(sql, sqlconn)
        Dim dt As New DataTable
        Dim ds As New DataSet
        sqladaptor.Fill(dt)
        DataGridView1.DataSource = dt

end sub

我想结合这两个语句。我想以一种方式设置它，如果没有文本它不会搜索该字段。例如，

I want to combine those 2 statements. I want to set it up in a way that if there is no text it wont search that field. For example,

If TextBox1.TextLength = 0 Then

        End If

Any帮助将不胜感激



我尝试过：



尝试使用通过按钮单击事件2个单独的语句，但只有1个sql语句将处理。

Any help would be appreciated

What I have tried:

tried using 2 separate statements through a button click event, but only 1 would sql statement would process.

推荐答案

尝试这样的事情：

Try something like this:

Using sqlconn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Databases\" + ComboBox1.Text + ".mdf;Integrated Security=True")
    Using da As New SqlDataAdapter(String.Empty, sqlconn)
        Dim sql As New StringBuilder("SELECT * FROM [")
        sql.Append(TreeView1.SelectedNode.Text.Replace("]", "\]"))
        sql.Append("] WHERE 1 = 1 ")
        
        If Not String.IsNullOrEmpty(TextBox3.Text) Then
            sql.Append("And Gender = @Gender")
            da.SelectCommand.Parameters.AddWithValue("@Gender", TextBox3.Text)
        End If
        
        Dim startYear As Integer, endYear As Integer
        If Integer.TryParse(TextBox1.Text, startYear) AndAlso Integer.TryParse(TextBox2.Text, endYear) Then
            sql.Append("And [Year of employment] Between @StartYear And @EndYear")
            da.SelectCommand.Parameters.AddWithValue("@StartYear", startYear)
            da.SelectCommand.Parameters.AddWithValue("@EndYear", endYear)
        End If
        
        ' Similar code for other filters...
        
        da.SelectCommand.CommandText = sql.ToString()
        
        Dim dt As New DataTable()
        da.Fill(dt)
        
        DataGridView1.DataSource = dt
    End Using
End Using

• 使用参数修复SQL Injection漏洞;
  
  注意：根据它们的来源，您可能仍需要验证 ComboBox1.Text 和 TreeView1.SelectedNode.Text values。
• 在中使用块包裹一次性对象;
• 在连接字符串中使用 | DataDirectory | 来引用当前的应用程序路径;
  
  使用本地数据库 - 智能客户端数据 [ ^ ]
• 使用 Integer.TryParse 验证 TextBox1 和 TextBox2 是否包含有效整数;
• 删除未使用的变量;





• Use parameters to fix the SQL Injection vulnerability;
  NB: Depending on where they come from, you may still need to validate the ComboBox1.Text and TreeView1.SelectedNode.Text values.
• Wrap disposable objects in Using blocks;
• Use |DataDirectory| in the connection string to refer to the current application path;
  Working with local databases – Smart Client Data[^]
• Use Integer.TryParse to validate that TextBox1 and TextBox2 contain valid integers;
• Remove unused variables;




不是你的问题的解决方案，但你有另一个问题。

永远不要通过连接用户输入来构建SQL查询，它被命名为SQL注入，这对你的数据库是危险的容易出错。

名称中的单引号和程序崩溃。如果像Brian O'Conner这样的用户输入可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating with user inputs, it is named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability.
SQL injection - Wikipedia[^]
SQL Injection[^]




这篇关于如何在VB.NET中使用SQL语句来过滤多个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！