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

[jalo]

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 don't 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!

 

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

Edited August 10, 2005 by jalo

[kahlua001]

"Select First_Name, Last_Name from Demographics where First_Name = '" & Me.TextBox1.Text & "'"

[jalo]

Thank you! :-)

 

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

 

WHERE First_Name = '" & Me.TextBox1.Text & "'"
[b]and[/b]
Last Name = '" & Me.TextBox2.Text & "'"

 

what would i put in place of "and"

[kahlua001]

WHERE First_Name = '" & Me.TextBox1.Text & "' AND Last Name = '" & Me.TextBox2.Text & "'"

[PlausiblyDamp]

Even better you may want to look at parameterising the query rather than relying on string concatenation, search these forums for reasons and examples.

[jalo]

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:

 

    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