[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

variable sql statement

Hello Experts

I'm building a VB program that connects to a database and allows the user to view insert and search the database for results. Currently the program works and can accomplish all of these things. I however dislike clunkiness of the search function. As of now the user has to choose one option from each of the combo boxes in order for the search function to return values. I am trying to program it so that if the the sql statement will be able to scale with the user inputs. I don't know a lot about sql and thought that if I input a "*" in  a WHERE statement that it would return all of the fields for that value but that does not seem to be the case.

MY code for the search button:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim class1 As String
        Dim class2 As String
        Dim diff As String
        Dim win As Int32

        If opponentsearchComboBox.Text = "" Then
            class1 = "*"
        Else
            class1 = opponentsearchComboBox.Text
        End If

        If opponentsearchComboBox1.Text = "" Then
            class2 = "*"
        Else
            class2 = opponentsearchComboBox1.Text
        End If

        If difficultysearchComboBox.Text = "" Then
            diff = "*"
        Else
            diff = difficultysearchComboBox.Text
        End If

        If winsearchComboBox.Text = "Yes" Then
            win = 1
        ElseIf winsearchComboBox.Text = "No" Then
            win = 0
        End If

        Me.command = New OleDb.OleDbCommand("SELECT * FROM pvp WHERE P1 = '" & class1 & "' AND P2 = '" & class2 & "' AND Difficulty = '" & diff & " ' ", New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\arenateam.mdb"))

        Me.adapter = New OleDb.OleDbDataAdapter(command)
        Me.dataset = New DataSet

        Me.command.Connection.Open()
        Me.adapter.Fill(Me.dataset)
        Me.command.Connection.Close()

        Me.pvpDataGridView.DataSource = Me.dataset.Tables(0).DefaultView
    End Sub
                                                                                                   
0
JSmead
Asked:
JSmead
  • 2
  • 2
2 Solutions
 
amit_gCommented:
You need to use like but in your case you can use something like this ...

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim class1 As String
        Dim class2 As String
        Dim diff As String
        Dim win As Int32
        Dim SQL As String

      SQL = "SELECT * FROM pvp WHERE 1 = 1"

        If opponentsearchComboBox.Text = "" Then
            class1 = "*"
        Else
            class1 = opponentsearchComboBox.Text
            SQL = SQL & " and P1 = '" & class1 & "'"
        End If

        If opponentsearchComboBox1.Text = "" Then
            class2 = "*"
        Else
            class2 = opponentsearchComboBox1.Text
            SQL = SQL & " and P2 = '" & class2 & "'"
        End If

        If difficultysearchComboBox.Text = "" Then
            diff = "*"
        Else
            diff = difficultysearchComboBox.Text
            SQL = SQL & " and Difficulty = '" & diff & "'"
        End If

        If winsearchComboBox.Text = "Yes" Then
            win = 1
        ElseIf winsearchComboBox.Text = "No" Then
            win = 0
        End If

        Me.command = New OleDb.OleDbCommand(SQL, New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\arenateam.mdb"))

        Me.adapter = New OleDb.OleDbDataAdapter(command)
        Me.dataset = New DataSet

        Me.command.Connection.Open()
        Me.adapter.Fill(Me.dataset)
        Me.command.Connection.Close()

        Me.pvpDataGridView.DataSource = Me.dataset.Tables(0).DefaultView
    End Sub
0
 
PaulHewsCommented:
The select clause specifies which fields will be in the resultset.  The Where clause specifies which rows will be in the resultset.

If you wish to return records that match on one or another field, you have to specify the fields in the WHERE clause.

For example, if you wish to match the string "hello" in Field1 or Field2

Select * From Table Where Field1 = 'hello' OR Field2 = 'hello'

This means return a resultset with all the columns, for the rows where the value in Field1 matches 'hello' or the value in Field2 matches 'hello'

In your case, I think where you have * you mean you don't want to discriminate in your query... So what you have to do is build your WHERE clause appropriately.

Dim WhereString As New System.Text.StringBuilder

If class1 <> "*" Then
    WhereString.Append(" P1 = '" & class1 & "' AND")
End If

If class2 <> "*" Then
    WhereString.Append(" P2 = '" & class2 & "' AND")
End If

If diff <> "*" Then
    WhereString.Append(" Difficulty = '" & diff & "' AND")
End If
If WhereString.Length > 4 Then
    WhereString.Length -= 4
    WhereString.Insert(0, "WHERE", 1)
Else
End If

Debug.WriteLine(WhereString.ToString)



Me.command = New OleDb.OleDbCommand("SELECT * FROM pvp WHERE " & WhereString.ToString, New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\arenateam.mdb"))
0
 
PaulHewsCommented:
Except for this has to change, because the WHERE keyword is inserted above.

Me.command = New OleDb.OleDbCommand("SELECT * FROM pvp " & WhereString.ToString, New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\arenateam.mdb"))
0
 
JSmeadAuthor Commented:
Say just to let you know I might not have time to get to this question until tomorrow as the day is winding down. looks like a few good suggestions for me to work on. Thanks.
0
 
JSmeadAuthor Commented:
Say guys I tried both of your suggestions when i got in today they both worked and I learned something new from your answers. So im going to split the points.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now