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.Tex t = "" Then
class1 = "*"
Else
class1 = opponentsearchComboBox.Tex t
End If
If opponentsearchComboBox1.Te xt = "" Then
class2 = "*"
Else
class2 = opponentsearchComboBox1.Te xt
End If
If difficultysearchComboBox.T ext = "" Then
diff = "*"
Else
diff = difficultysearchComboBox.T ext
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("Pro vider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\arenateam.mdb"))
Me.adapter = New OleDb.OleDbDataAdapter(com mand)
Me.dataset = New DataSet
Me.command.Connection.Open ()
Me.adapter.Fill(Me.dataset )
Me.command.Connection.Clos e()
Me.pvpDataGridView.DataSou rce = Me.dataset.Tables(0).Defau ltView
End Sub
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.Tex
class1 = "*"
Else
class1 = opponentsearchComboBox.Tex
End If
If opponentsearchComboBox1.Te
class2 = "*"
Else
class2 = opponentsearchComboBox1.Te
End If
If difficultysearchComboBox.T
diff = "*"
Else
diff = difficultysearchComboBox.T
End If
If winsearchComboBox.Text = "Yes" Then
win = 1
ElseIf winsearchComboBox.Text = "No" Then
win = 0
End If
Me.command = New OleDb.OleDbCommand("SELECT
Me.adapter = New OleDb.OleDbDataAdapter(com
Me.dataset = New DataSet
Me.command.Connection.Open
Me.adapter.Fill(Me.dataset
Me.command.Connection.Clos
Me.pvpDataGridView.DataSou
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
Me.command = New OleDb.OleDbCommand("SELECT