Link to home
Start Free TrialLog in
Avatar of JSmead
JSmead

asked on

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
                                                                                                   
ASKER CERTIFIED SOLUTION
Avatar of amit_g
amit_g
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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"))
Avatar of JSmead
JSmead

ASKER

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.
Avatar of JSmead

ASKER

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.