creating a db query based on textbox value - vb.net

J

jalo

Active member
Joined
Aug 9, 2005
Messages
28
extremely slow lookup for a query (see last post)

i am new to vb.net and am working on my first form that interacts with an access database. I dont seem to be able to figure out the syntax for creating a query against the dataset in which the WHERE clause is based on the user texbox input in the form. here is what I have - any help will be greatly appreciated!

Code: 
Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click
        Dim commandString As String = New String("Select First_Name, Last_Name from Demographics where First_Name = Me.TextBox1.Text")
        Me.OleDbDataAdapter1 = New System.Data.OleDb.OleDbDataAdapter(commandString, OleDbConnection2)
        Me.OleDbDataAdapter1.Fill(DataSet1, "Demographics")
        Me.DataGrid1.DataSource = DataSet1.Tables("Demographics")
        Me.DataGrid1.Update()
        Application.DoEvents()
    End Sub
 
Last edited by a moderator:
Thank you! :-)

One more question, how do i add more conditions to the WHERE clause - that is if i wanted to say

Code: 
WHERE First_Name = " & Me.TextBox1.Text & ""
[B]and[/B]
Last Name = " & Me.TextBox2.Text & ""

what would i put in place of "and"
 
Even better you may want to look at parameterising the query rather than relying on string concatenation, search these forums for reasons and examples.
 
slow lookup

ok, i did look up into using parameterised queries. i re-wrote what i had and it works. the issue i am having is that in either case (parameterised or not), the lookup time for the query takes forever... about one minute... i wonder if i have things placed in the wrong places... any suggestions?

in my constructor, after calling InitializeComponent(), I have Me.OleDbConnection2.Open()

and then, here is the code i have for the click of the button:

Code: 
    Private Sub FindPatient_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FindPatient.Click
        Dim command As OleDb.OleDbCommand

        If (Me.ComboBox1.SelectedIndex = 1 And Not Me.TextBox3.Text = "") Then
            command = New OleDb.OleDbCommand("Select First_Name, Last_Name, Date_of_Birth, Gender from Demographics where First_Name = ? and Last_Name = ? and Patient_ID = ?", Me.OleDbConnection2)
        ElseIf (Me.ComboBox1.SelectedIndex = 0 And Not Me.TextBox3.Text = "") Then
            command = New OleDb.OleDbCommand("Select First_Name, Last_Name, Date_of_Birth, Gender from Demographics where First_Name = ? and Last_Name = ? and Health_Card_Number = ?", Me.OleDbConnection2)
        ElseIf (Me.TextBox3.Text = "") Then
            command = New OleDb.OleDbCommand("Select First_Name, Last_Name, Date_of_Birth, Gender from Demographics where First_Name = ? and Last_Name = ?", Me.OleDbConnection2)
        End If

        command.Parameters.Add(New OleDb.OleDbParameter("Fname", OleDb.OleDbType.VarChar, 20))
        command.Parameters.Add(New OleDb.OleDbParameter("Lname", OleDb.OleDbType.VarChar, 20))
        command.Parameters.Add(New OleDb.OleDbParameter("ThirdParam", OleDb.OleDbType.VarChar, 20))
        command.Prepare()
        command.Parameters.Item("Fname").Value() = Me.TextBox1.Text
        command.Parameters.Item("Lname").Value() = Me.TextBox2.Text
        command.Parameters.Item("ThirdParam").Value() = Me.TextBox3.Text

        Me.OleDbDataAdapter1 = New System.Data.OleDb.OleDbDataAdapter(command)
        Me.OleDbDataAdapter1.Fill(DataSet1, "Demographics")
        Me.DataGrid1.DataSource = DataSet1.Tables("Demographics")
        Me.DataGrid1.Select(0)
        Me.DataGrid1.Update()
        Application.DoEvents()
        Me.OleDbConnection2.Close()

    End Sub
 
Back
Top