Full Text Search in Visual Basic

Full Text Search in Visual Basic

• Lantze

  Take the quotes off of the "SearchTextBox.Text" and concantenate the contents with ampersands if you're trying to pass the value, otherwise you're searching the DB for the name of your textbox. 

  If you want to use that method I suggest you wrap the textbox contents with a function that will make sure any embedded quotes in the text are handled properly.  I would also suggest that rather than a literal, you just use a parameter for the value or perhaps a stored procedure with parameters.

  
  

• smeganb

  Thanks for the suggestions. I am going to have to do some research and trial and error to see how far I can get with this. I am a Newbie to VB and SQL Server, so this is a pretty big challenge for me at this point.
  

• ts11

  Yes, it works!! I was trusting the flyout task menu on the listbox for binding to the FullDocuments table (apparently not a good idea this case). Your programatic code approach is much better.

  Thanks for guiding me through stage one - a working full text search. I have learned alot of basics and have some ideas for how to make it more sophisticated, etc. But getting over this initial hurdle is major. Thanks again, Reed.

  
  

• WordScript

  Did you use the wildcards when passing in the TextBox1.Text value

  You might want to post some of the relevant code that you are currently using so we can take a look at it - without an exception analyzing your code is the next best thing. Please don't do a blanket copy and paste, just post the methods that are relevant to the problem, such as the Button_Click event handler.

  
  
  

• Bruce HK

  You're quite welcome. Glad you got it.

  As an aside, you can trust the design-time databindings (using that flyout menu on the ListBox) providing that you work with the databindings properly.

  WHen you used the designer to do the binding, a DataSet, TableAdapter, and BindingSource were all created for you. The BindingSource has properties and methods for keeping the data source in sync with the bound control(s). With a little more research on these three items and looking into design time data binding, you should make good headway.

  If you run into any new issues, just start a new thread! Good luck!!

  
  
  

• abelsgmx

  The DataSet Designer is your next stop.

  If you double click the DataSet in the Solution Explorer, Visual Studio will open the DataSet designer. Now you find the table in question and right click on the TableAdapter at the bottom of the table. You'll select Add Query from the context menu. This will open the query designer that will allow you to create the query that takes the search parameter.

  Once you've configured your dataset by adding the new Fill/GetData methods, you will then:

  1. Create a New instance of the DataSet and TableAdapter
  2. Use the TableAdapter to Fill the DataSet by calling the new Fill method that you added to the DataTable.

  The query string you use when adding the new Fill method will look something like:

  SELECT * FROM [Full Documents] WHERE [SectionText] = @SearchText

  The code in the button will then look something like:

  Dim ds As New DocumentsDataSet
  Dim ta As New DocumentsDataSetTableAdapters.FullDocumentsTableAdapter
  
  ta.FillBySearch(ds.FullDocuments, TextBox1.Text)

  This code assumes that the DataSet is named DocumentsDataSet and that the table is named FullDocuments.

  If the bindings are already in place for the ListBox then you should be good to go. You'll probably want to suspend and resume binding on the list while you're filling the dataset and those methods will be on the BindingSource created by the ID. Also don't forget that the Fill you are going to do replaces the default fill code that was generated by the designer when you setup your databinding. Just comment out the fill code that the IDE wrote for you.

  Keep at it! You're moving right along!

  
  
  

• ArildFines

  Reed: Thanks for your help and patience. The SectionText column contains multiple words.

  I created the query using the SELECT statement you provided. That seemed to work okay.

  I pasted in the code for the button click event that you provided and clicked the debug button. The program ran with no errors. When I typed a word in the textbox and clicked the button, the program seemed be be searching for several seconds but returned no results in the listbox. If I clicked the button too soon I would see the hourglass which would go away after awhile and I could click the button again trying different search terms (which I know are in the SectionText column of the database).

  Without any error messages it is difficult for me to provide feedback for you. Do you have any ideas as to what needs to be adjusted at this point so that I can get search results

  
  

• JulianMadle

  Regardless of your final intent, all you are really trying to do is query a database. If you search MSDN or the forums for info on query database, dataset, or ADO you should find plenty of resources.

  Your goal is to add the database to your Data Sources, generate a DataSet containing all of the necessary tables in the database, configure the TableAdapter on the Full_Documents DataTable by adding new Fill/GetData methods that take a SelectionText parameter, bind the ListBox to the DataSet/DataTable/and DataColumn(s), and finally clear and then fill the DataTable using the Fill() method you added passing in the TextBox1.Text value when the Button is clicked.

  See if you can get started with that. Good luck!

  
  
  

• Winanjaya

  Here is the code for the Button_Click event hander.

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

  Dim ds As New DocumentsDataSet

  Dim ta As New DocumentsDataSetTableAdapters.FullDocumentsTableAdapter

  ta.FillBy(ds.FullDocuments, String.Format("{0}{1}{0}", "%", TextBox1.Text))

  End Sub

  
  

• Mule

  Ok, well that's only part of what you need your routine to do :)

  You've gathered the records, but now you have to display them in the ListBox. You either need to loop through the ds.FullDocuments.Rows collection and add info from the row to the ListBox.Items, or you need to bind the ListBox to the FullDocuments table.

  The way the program is structured now, you are creating the DataSet on the fly so the databinding would have to be created (or recreated) each time the button is pressed. You could modify the code to something like:

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

  ListBox1.DataSource = Nothing

  Dim ds As New DocumentsDataSet

  Dim ta As New DocumentsDataSetTableAdapters.FullDocumentsTableAdapter

  ta.FillBy(ds.FullDocuments, String.Format("{0}{1}{0}", "%", TextBox1.Text))

  ListBox1.DisplayMember = "FieldName1"

  ListBox1.ValueMember = "FieldName2"

  ListBox1.DataSource = ds.FullDocuments

  End Sub

  In this example "FieldName1" and "FieldName2" should be the names of fields in the FullDocuments table. The DisplayMember should be set to the data that you want to see in the list and the ValueMember should be set to a field that indentifies the row; for instance DisplayMember = "Name", ValueMember = "ID".

  Also understand that this is not really good data binding. You really don't want to completely reset the bindings each time the button is pressed; but for now, this should give you a working example.

  The other option is to loop through the row. This option might look something like:

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

  Dim ds As New DocumentsDataSet

  Dim ta As New DocumentsDataSetTableAdapters.FullDocumentsTableAdapter

  ta.FillBy(ds.FullDocuments, String.Format("{0}{1}{0}", "%", TextBox1.Text))

  ListBox1.Items.Clear

  For Each dr as DataRow in ds.FullDocuments.Rows

  ListBox1.Items.Add(dr.Item("FieldName1"))

  Next

  End Sub

  Hope that helps!

  
  
  

• Figo Fei - MSFT

  Ok, this is just a simple SQL error that occured becuase I did not know the data types of your fields. You just need to use LIKE instead of EQUALS:

  SELECT * FROM [Full Documents] WHERE [SectionText] LIKE @SearchText

  I'm not sure what all the field [SectionText] contains... If it is just one word then that query should work fine; if it is multiple words you may want to include wild cards (%) in the parameter value. So when you call that FillBy() method and pass in TextBox1.Text, you might want to add percent signs to each end like:

  ta.FillBySearch(ds.FullDocuments, String.Format("{0}{1}{0}", "%", TextBox1.Text))

  The parameter @SearchText is just an aribitrary variable. The @ at the beginning is what specifies this as a parameter, but the name could have been anything (ie. @1 or @var, etc.).

  
  
  

• laksh

  Happy to help, you might find some insight from this previous post:

  http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=335078&SiteID=1

  
  

• TezW

  I have been researching and trying some things with limited success. I am able to connect to the database using a full-text query (well, sort of). I created a stored procedure (first stored procedure!) from my full-text query in SQL Management Studio and dropped it onto my form in VB (first full-text search in VB – even if the search term was hard-coded). So I have achieved the first two objectives laid out by rkimble:

  1 Add database to your Data Sources.

  2 Generate a DataSet containing all of the necessary tables in the database.

  3 Configure the TableAdapter on the Full_Documents DataTable by adding new Fill/GetData methods that take a SelectionText parameter

  4 Bind the ListBox to the DataSet/DataTable/and DataColumn(s)

  5 Clear and then fill the DataTable using the Fill() method you added passing in the TextBox1.Text value when the Button is clicked.

  I am struggling with 3, 4 and 5. I am not exactly sure what “configure” means, other than using the listbox tasks flyout to link to a data source, display member and value member. Here is the code I have written to try to fill the listbox control:

  Public Class Form1

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

  Dim strSearch As String = TextBox1.Text

  ListBox1.GetItemText("WHERE CONTAINS ([Full Documents].SectionText, " & _

  "' """ & strSearch & """ '")

  End Sub

  End Class

  The application executes but I don’t get any search results. Any thoughts on what to try next

  
  

• Gray Ghost

  Thanks so much. I am learning alot and feel I am close to getting this to work. I think I have only one problem to sort out. When I added the query in query designer, I used your suggestion:

  SELECT * FROM [Full Documents] WHERE [SectionText] = @SearchText

  As I finished the process I got this message box warning:

  "The data types ntext and ntext are incompatible in the equal to operator."

  When I debugged, the application loaded without errors but I got the same warning when I clicked the button:

  "The data types ntext and ntext are incompatible in the equal to operator."

  This was my first time using the query designer. Glad you pointed that out to me as I was getting frustrated trying to add queries from the listbox flyout task menus. If we can get the query ntext problem sorted out, I think it will work.

  I understand that SectionText and SearchText are string(ntext) data. What other options do we have here Also, is "SearchText" simply an arbitrary variable declaration that you created or is it a recognized entity within VB

  
  
• Full Text Search in Visual Basic