query in searching

S

srivalli

Well-known member
Joined
May 4, 2005
Messages
189
Programming Experience
Beginner
in my form i enter some queries for a particular bank.

for each bank ,i enter nearly 10 queries in ten different textboxes.

so, what i did is , each query i am entering in a textbox and i am storing it in one column at the back end. (say the column name is qry1 in msaccess)

so there are 10 queries in vb.net ,and there are 10 columns in the back end

now my query is :

now in vb.net, when i enter some word in a textbox and when i click button, i want to display all the queries related to that word, irrespective of the bank.

suppose if the word "tax" is there in 2 different banks,then i want to display all the queries related to "tax"

hope my question is very clear.

let me know as early as possible.


thanks
 
Sort by date Sort by votes
Are you asking about how to make a query which can retrieve a word (i.e 'tax') from Access Database? If this is the case try something like:

Dim sql As String
sql = "SELECT * FROM tablename WHERE qry1 LIKE %" & textbox1.text & "% OR qry2 LIKE %" & textboxt1.text & "%"
'some database connections go here
Click to expand...

If you 10 columns numbered named qry1 ~ qry10, you can build the query string using For..Next.
 
Upvote 0 Downvote
Searching for a key word

first of all thank u so much for ur kind response.

let me explain u very clearly.

in my form i have 10 textboxes ,in which i am entering 10 different queries.
in Access i created 10 different columns to store these queries. right?

now my query is

when i type a particular word(say tax) in a new textbox , and when i click the button,then that related word(tax) queries should be displayed , either in a richtextbox or what ever control.

Suppose out of ten queries ,if there are 5 queries in which "tax" word is matching ,then that 5 queries should be displayed.

hope my query is very clear

any suggestions u r welcome

thank u


ayozzhero said:
Are you asking about how to make a query which can retrieve a word (i.e 'tax') from Access Database? If this is the case try something like:



If you 10 columns numbered named qry1 ~ qry10, you can build the query string using For..Next.
Click to expand...
 
Upvote 0 Downvote
I'm afraid your query isn't clear because of your overuse of the word "query". My query is whether or not your query is whether your query should return all your queries.

But seriously, are you asking how to return all rows where any of the "query" columns contain your key word, e.g. "tax", but only return the columns that do contain that key word? That's the best guess I can make. If that is so, then it is not possible as far as I know. You would need to return all columns of the matching rows and then just display the ones that contain the key word.
 
Upvote 0 Downvote
Searching for a key word.

yes ur guess is right
what i want is to dispaly all the columns matched to that keyword .
u suggestion is to return all the columns of the matching rows and then display the ones that contain the key word.

can u explain ur logic by giving an example.

what i did till now is

i am entering one keyword in the textbox.
and when i click the button, the related columns are being displayed,but along with that the column which does not contain the key word is also displaying.

i just want to compare the entered keyword with the retrieved data.
is it possible?

let me know.pllllssss
its veryyyyy urgent
thk u for ur response.
jmcilhinney said:
I'm afraid your query isn't clear because of your overuse of the word "query". My query is whether or not your query is whether your query should return all your queries.

But seriously, are you asking how to return all rows where any of the "query" columns contain your key word, e.g. "tax", but only return the columns that do contain that key word? That's the best guess I can make. If that is so, then it is not possible as far as I know. You would need to return all columns of the matching rows and then just display the ones that contain the key word.
Click to expand...
 
Upvote 0 Downvote
We need to know how you are actually displaying the data. Will it be in TextBoxes, a DataGrid, a ListView or some other way? Your query will return a result set that will contain every column of every row that has the search term in at least one column. You need to iterate over the rows and extract only the column values that contain the search term and display them somehow. Here is a generic example but I can't give you more without knowing exactly how you want to display them.
VB.NET: 
[color=Blue]Dim[/color] myTable[color=Blue] As[/color] DataTable [color=Green]'Will contain the result set.[/color]
[color=Blue] Dim[/color] searchTerm [color=Blue]As String[/color] [color=Green]'Will contain the string to search for.[/color]

[color=Blue] For Each[/color] row [color=Blue]As[/color] DataRow [color=Blue]In[/color] myTable.Rows
	[color=Blue]For[/color] i [color=Blue]As Integer[/color] = 0 [color=Blue]To[/color] myTable.Columns.Count - 1
		[color=Blue]If CStr[/color](row(i)).IndexOf(searchTerm) <> -1 [color=Blue]Then[/color]
			[color=Green]'The field contains the search term so display it somehow.[/color]
		[color=Blue]End If[/color]
	[color=Blue]Next[/color]
[color=Blue]Next[/color]
Note that it is up to you to populate the myTable and searchTerm variables.
 
Upvote 0 Downvote
Query in searching

hello,
thank u soo much for ur response.
in the above code u mentioned ,where are u taking the input ?

my requirement is i want to enter a keyword in a textbox and i want to display that keyword related columns in different textboxes or in a list view.

plss let me know the code for entering the keyword in a textbox and display that particular columns in either textboxes or in a list view.

its veryyyyyyyyy urgent
thank u


jmcilhinney said:
We need to know how you are actually displaying the data. Will it be in TextBoxes, a DataGrid, a ListView or some other way? Your query will return a result set that will contain every column of every row that has the search term in at least one column. You need to iterate over the rows and extract only the column values that contain the search term and display them somehow. Here is a generic example but I can't give you more without knowing exactly how you want to display them.
VB.NET: 
[color=blue]Dim[/color] myTable[color=blue] As[/color] DataTable [color=green]'Will contain the result set.[/color]
[color=blue]Dim[/color] searchTerm [color=blue]As String[/color] [color=green]'Will contain the string to search for.[/color]
 
[color=blue]For Each[/color] row [color=blue]As[/color] DataRow [color=blue]In[/color] myTable.Rows
	[color=blue]For[/color] i [color=blue]As Integer[/color] = 0 [color=blue]To[/color] myTable.Columns.Count - 1
		[color=blue]If CStr[/color](row(i)).IndexOf(searchTerm) <> -1 [color=blue]Then[/color]
			[color=green]'The field contains the search term so display it somehow.[/color]
		[color=blue]End If[/color]
	[color=blue]Next[/color]
[color=blue]Next[/color]
Note that it is up to you to populate the myTable and searchTerm variables.
Click to expand...
 
Upvote 0 Downvote
Note that you have ten columns in you table and the rows returned may have a match in just column 1, or in columns 5 & 7, or in column 10, or in all columns, or some other combination. You haven't specified whether your SQL query is designed to return only one row or multiple rows. If it is one row, then you need to have ten TextBoxes and only populate the ones that correspond to fields that contain the search term. If it is multiple rows then a ListView may be more appropriate, with only the columns populated that correspond to fields that contain the search term. I'll give an example of each that uses three columns for brevity. You will have to think a bit and adapt it to your exact situation yourself. Also, I'm going to use a data adapter to fill a DataTable. If you have no intention of updating the data, or it is only a single row, it may be more appropriate to use a data reader.
VB.NET: 
	[color=Blue]Private Sub[/color] RetrieveData()
		[color=Blue]Dim[/color] connection [color=Blue]As New[/color] OleDbConnection
		[color=Blue]Dim[/color] selectSQL [color=Blue]As String[/color] = [color=Blue]String[/color].Format("SELECT * FROM Table1 WHERE field1 LIKE '%{0}%' OR field2 LIKE '%{0}%' OR field3 LIKE '%{0}%'", _
											 [color=Blue]Me[/color].searchTermTextBox.Text.Trim())
		[color=Blue]Dim[/color] adapter [color=Blue]As New[/color] OleDbDataAdapter(selectSQL, connection)
		[color=Blue]Dim[/color] table [color=Blue]As New[/color] DataTable("Results")

		adapter.Fill(table)

		[color=Green]'*** The following section would be used if you have a single row to display in text boxes ***[/color]

		[color=Green]'Assuming a single row is returned, fill text boxes from the first (and only) row.[/color]
		[color=Blue]Dim[/color] theRow [color=Blue]As[/color] DataRow = table.Rows(0)

		[color=Blue]If CStr[/color](theRow("field1")).IndexOf([color=Blue]Me[/color].searchTermTextBox.Text.Trim()) = -1 [color=Blue]Then[/color]
			[color=Green]'The field does not contain the search term.[/color]
			[color=Blue]Me[/color].field1TextBox.Clear()
		[color=Blue]Else[/color]
			[color=Blue]Me[/color].field1TextBox.Text = [color=Blue]CStr[/color](theRow("field1"))
		[color=Blue]End If[/color]

		[color=Blue]If CStr[/color](theRow("field2")).IndexOf([color=Blue]Me[/color].searchTermTextBox.Text.Trim()) = -1 [color=Blue]Then[/color]
			[color=Green]'The field does not contain the search term.[/color]
			[color=Blue]Me[/color].field2TextBox.Clear()
		[color=Blue]Else[/color]
			[color=Blue]Me[/color].field2TextBox.Text = [color=Blue]CStr[/color](theRow("field2"))
		[color=Blue]End If[/color]

		[color=Blue]If CStr[/color](theRow("field3")).IndexOf([color=Blue]Me[/color].searchTermTextBox.Text.Trim()) = -1 [color=Blue]Then[/color]
			[color=Green]'The field does not contain the search term.[/color]
			[color=Blue]Me[/color].field3TextBox.Clear()
		[color=Blue]Else[/color]
			[color=Blue]Me[/color].field3TextBox.Text = [color=Blue]CStr[/color](theRow("field1"))
		[color=Blue]End If[/color]

		[color=Green]'*********************************************************************************************[/color]

		[color=Green]'*** The following section would be used if you have multiple rows to display in a list view ***[/color]

		[color=Blue]Dim[/color] item [color=Blue]As[/color] ListViewItem

		[color=Blue]For Each[/color] row [color=Blue]As[/color] DataRow [color=Blue]In[/color] table.Rows
			item = [color=Blue]New[/color] ListViewItem

			[color=Blue]If CStr[/color](row("field1")).IndexOf([color=Blue]Me[/color].searchTermTextBox.Text.Trim()) = -1 [color=Blue]Then[/color]
			    [color=Green]'The field does not contain the search term.[/color]
				item.Text = [color=Blue]String[/color].Empty
			[color=Blue]Else[/color]
				item.Text = [color=Blue]CStr[/color](row("field1"))
			[color=Blue]End If[/color]

			[color=Blue]If CStr[/color](row("field2")).IndexOf([color=Blue]Me[/color].searchTermTextBox.Text.Trim()) = -1 [color=Blue]Then[/color]
			    [color=Green]'The field does not contain the search term.[/color]
				item.SubItems.Add([color=Blue]String[/color].Empty)
			[color=Blue]Else[/color]
				item.SubItems.Add([color=Blue]CStr[/color](row("field2")))
			[color=Blue]End If[/color]

			[color=Blue]If CStr[/color](row("field3")).IndexOf([color=Blue]Me[/color].searchTermTextBox.Text.Trim()) = -1 [color=Blue]Then[/color]
			    [color=Green]'The field does not contain the search term.[/color]
				item.SubItems.Add([color=Blue]String[/color].Empty)
			[color=Blue]Else[/color]
				item.SubItems.Add([color=Blue]CStr[/color](row("field3")))
			[color=Blue]End If[/color]
		[color=Blue]Next[/color]

		[color=Blue]Me[/color].resultsListView.Items.Add(item)

		[color=Green]'*********************************************************************************************[/color]
	[color=Blue]End Sub[/color]
Note the use of wildcards in the SQL statement to return rows that contain the search term anywhere in any field.
 
Upvote 0 Downvote
thank u soooo much for ur response.

i ll try this code and let u know the status.
bye
jmcilhinney said:
Note that you have ten columns in you table and the rows returned may have a match in just column 1, or in columns 5 & 7, or in column 10, or in all columns, or some other combination. You haven't specified whether your SQL query is designed to return only one row or multiple rows. If it is one row, then you need to have ten TextBoxes and only populate the ones that correspond to fields that contain the search term. If it is multiple rows then a ListView may be more appropriate, with only the columns populated that correspond to fields that contain the search term. I'll give an example of each that uses three columns for brevity. You will have to think a bit and adapt it to your exact situation yourself. Also, I'm going to use a data adapter to fill a DataTable. If you have no intention of updating the data, or it is only a single row, it may be more appropriate to use a data reader.
VB.NET: 
	[color=blue]Private Sub[/color] RetrieveData()
		[color=blue]Dim[/color] connection [color=blue]As New[/color] OleDbConnection
		[color=blue]Dim[/color] selectSQL [color=blue]As String[/color] = [color=blue]String[/color].Format("SELECT * FROM Table1 WHERE field1 LIKE '%{0}%' OR field2 LIKE '%{0}%' OR field3 LIKE '%{0}%'", _
											 [color=blue]Me[/color].searchTermTextBox.Text.Trim())
		[color=blue]Dim[/color] adapter [color=blue]As New[/color] OleDbDataAdapter(selectSQL, connection)
		[color=blue]Dim[/color] table [color=blue]As New[/color] DataTable("Results")
 
		adapter.Fill(table)
 
		[color=green]'*** The following section would be used if you have a single row to display in text boxes ***[/color]
 
		[color=green]'Assuming a single row is returned, fill text boxes from the first (and only) row.[/color]
		[color=blue]Dim[/color] theRow [color=blue]As[/color] DataRow = table.Rows(0)
 
		[color=blue]If CStr[/color](theRow("field1")).IndexOf([color=blue]Me[/color].searchTermTextBox.Text.Trim()) = -1 [color=blue]Then[/color]
			[color=green]'The field does not contain the search term.[/color]
			[color=blue]Me[/color].field1TextBox.Clear()
		[color=blue]Else[/color]
			[color=blue]Me[/color].field1TextBox.Text = [color=blue]CStr[/color](theRow("field1"))
		[color=blue]End If[/color]
 
		[color=blue]If CStr[/color](theRow("field2")).IndexOf([color=blue]Me[/color].searchTermTextBox.Text.Trim()) = -1 [color=blue]Then[/color]
			[color=green]'The field does not contain the search term.[/color]
			[color=blue]Me[/color].field2TextBox.Clear()
		[color=blue]Else[/color]
			[color=blue]Me[/color].field2TextBox.Text = [color=blue]CStr[/color](theRow("field2"))
		[color=blue]End If[/color]
 
		[color=blue]If CStr[/color](theRow("field3")).IndexOf([color=blue]Me[/color].searchTermTextBox.Text.Trim()) = -1 [color=blue]Then[/color]
			[color=green]'The field does not contain the search term.[/color]
			[color=blue]Me[/color].field3TextBox.Clear()
		[color=blue]Else[/color]
			[color=blue]Me[/color].field3TextBox.Text = [color=blue]CStr[/color](theRow("field1"))
		[color=blue]End If[/color]
 
		[color=green]'*********************************************************************************************[/color]
 
		[color=green]'*** The following section would be used if you have multiple rows to display in a list view ***[/color]
 
		[color=blue]Dim[/color] item [color=blue]As[/color] ListViewItem
 
		[color=blue]For Each[/color] row [color=blue]As[/color] DataRow [color=blue]In[/color] table.Rows
			item = [color=blue]New[/color] ListViewItem
 
			[color=blue]If CStr[/color](row("field1")).IndexOf([color=blue]Me[/color].searchTermTextBox.Text.Trim()) = -1 [color=blue]Then[/color]
			 [color=green]'The field does not contain the search term.[/color]
				item.Text = [color=blue]String[/color].Empty
			[color=blue]Else[/color]
				item.Text = [color=blue]CStr[/color](row("field1"))
			[color=blue]End If[/color]
 
			[color=blue]If CStr[/color](row("field2")).IndexOf([color=blue]Me[/color].searchTermTextBox.Text.Trim()) = -1 [color=blue]Then[/color]
			 [color=green]'The field does not contain the search term.[/color]
				item.SubItems.Add([color=blue]String[/color].Empty)
			[color=blue]Else[/color]
				item.SubItems.Add([color=blue]CStr[/color](row("field2")))
			[color=blue]End If[/color]
 
			[color=blue]If CStr[/color](row("field3")).IndexOf([color=blue]Me[/color].searchTermTextBox.Text.Trim()) = -1 [color=blue]Then[/color]
			 [color=green]'The field does not contain the search term.[/color]
				item.SubItems.Add([color=blue]String[/color].Empty)
			[color=blue]Else[/color]
				item.SubItems.Add([color=blue]CStr[/color](row("field3")))
			[color=blue]End If[/color]
		[color=blue]Next[/color]
 
		[color=blue]Me[/color].resultsListView.Items.Add(item)
 
		[color=green]'*********************************************************************************************[/color]
	[color=blue]End Sub[/color]
Note the use of wildcards in the SQL statement to return rows that contain the search term anywhere in any field.
Click to expand...
 
Upvote 0 Downvote
Just a small point but please don't quote other people's posts unnecessarily, or at least remove the bits that aren't needed. Only quote a post if you want to show that you are referring to that post specifically, and if you do quote please remove things like long code listings and other things that add no value. Otherwise, threads like this can get long and more difficult to read very quickly. Normally, using the "Post Reply" button or the "Quick Reply" box should be sufficient.
 
Upvote 0 Downvote
neede urgentlyyyy

The code is running perfectly ,but it is searching for a single row.

i want to check particular keyword for all the rows.
Is there any possibility?

also instead of displaying the matched queries in different textboxes, i want to display in other control like dataview or datafrid or richtextbox.Can it be possible.
if so how?
thank u
 
Upvote 0 Downvote
searching through all the rows

The code is running perfectly ,but it is searching for a single row.

i want to check particular keyword for all the rows.
Is there any possibility?

also instead of displaying the matched queries in different textboxes, i want to display in other control like dataview or datafrid or richtextbox.Can it be possible.
if so how?
thank u
 
Upvote 0 Downvote
The SQL statement included in my code will return all rows.

A DataView is not a control. It is not a UI element.

I've already shown you how to use a ListView. If you want to use a DataGrid then just remove all the field values that don't contain the search term and bind to the DataTable.

It's time to think laterally! I've aided as much as I'm going to on this topic.
 
Upvote 0 Downvote
Thanks for ur response

once again thank u soo much for ur response.i was really breaking my head for 3 days.actually the code u send i didnt go through completely.in that u mentioned how to bind to listview.now i saw .

thank a lot
jmcilhinney said:
The SQL statement included in my code will return all rows.

A DataView is not a control. It is not a UI element.

I've already shown you how to use a ListView. If you want to use a DataGrid then just remove all the field values that don't contain the search term and bind to the DataTable.

It's time to think laterally! I've aided as much as I'm going to on this topic.
Click to expand...
 
Upvote 0 Downvote
small problem in the code

there are some small contradicts in ur code.

that is for the first bank ,in textbox1 if i enter the keyword "tax" ,then that column is displayed.
but for the next bank,in the same textbox ie in textbox1,if i enter the keyword "tax" ,then both the columns are not displayed.
thank u.
 
Upvote 0 Downvote

Similar threads

Z
  • Question
RichTextBox In Nice Even Columns
Replies
2
Views
408
jmcilhinney
jmcilhinney
luca89
  • Question
Question Unable to populate RDLC from DataSet
Replies
3
Views
508
luca89
luca89
K
  • Question
Custom ribbon deployment - single .docm file
Replies
1
Views
383
jdelano
J
K
  • Question
Table relations issue
Replies
1
Views
568
jdelano
J
jcardana
  • Solved
Resolved Creating Textbox Array: System.NullReferenceException
Replies
6
Views
2K
jcardana
jcardana

Share this page

Latest posts

Back
Top