How do I use an SQL statement in VB.NET to filter multiple results

Question

0.00/5 (No votes)

See more:

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 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.

Posted 30-May-17 21:06pm

Member 11856456

Updated 31-May-17 11:25am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2017-05-31T11:25:00

Try something like this:

VB.NET

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

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;

Now, do yourself a favour and give your controls meaningful names, rather than accepting the default names provided by the designer. You might remember what ListBox42 represents now, but when you come back to look at your code in six months time, you won't have a clue! :)

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Patrice T · Accepted Answer · 2017-05-30T21:16:00

Solution 1

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[^]

Posted 30-May-17 21:16pm

Patrice T

Comments

Member 11856456 31-May-17 12:59pm

This brings up a good question then. How do you go about making a search or filter function that would not cause an SQL injection?

Patrice T 31-May-17 13:44pm

Second link is a solution.

Maciej Los 31-May-17 15:04pm

This is almost an answer. I promote it as 4. I'd upvote it when you add some details about parameterized queries.

Patrice T 31-May-17 17:43pm

Hi, Thank you.
Have look at second link in solution