variable sql statement
Posted on 2007-08-02
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 = "*"
class1 = opponentsearchComboBox.Text
If opponentsearchComboBox1.Text = "" Then
class2 = "*"
class2 = opponentsearchComboBox1.Text
If difficultysearchComboBox.Text = "" Then
diff = "*"
diff = difficultysearchComboBox.Text
If winsearchComboBox.Text = "Yes" Then
win = 1
ElseIf winsearchComboBox.Text = "No" Then
win = 0
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.pvpDataGridView.DataSource = Me.dataset.Tables(0).DefaultView