How to Search for records in a Database using VB.net

[Lucky]

I have a vb.net windows app that connects to an access database. The
database has 1 table. One of the columns is called "Address". A user
enters any address into a textbox (txtValue) then clicks the Find
button (cmdFind) and a datagrid displays all the rows with this
address. The problem is that it will only find the the address if the
user types it exactly as it's entered the the DB. For example: the
address column has 123 Anystreet Rd. If the user types 123 Anystreet
then clicks Find, no rows are returned. But if the user types 123
Anystreet Rd, all rows are displayed containing that address. I don't
want that because the user won't know exactly how it's stored in the
DB. I used an oleDbDataAdapte r and Query builder for my select
statement which is: SELECT *
FROM [Primary Site List]
WHERE (Address LIKE ?)

My cmdFind_click event code is as follows (I'm using option buttons):

If optAddress.Chec ked = True Then
OleAddressAdapt er.SelectComman d.Parameters("A ddress").Value
= txtValue.Text
DsPrimarySite1. Clear() 'this is my dataset
OleAddressAdapt er.Fill(DsPrima rySite1)
End If

How can I get it to return rows containing any portion of the address
entered by the user?

I am a newbie and this is my first app, so please be detailed in your
help. I greatly appreciate any assistance you can provide.

[Ryan Cooper]

Your question really is not a .NET question but a SQL question. Anyways, if
you are using the LIKE feature in a sql statement, you have to use a special
"wildcard" character (%) to make the system look for records that don't
match exactly. Heres an example:

"SELECT * FROM Customers WHERE Address LIKE '%" & strAddressSearc h & "%'"

This will return ALL records that contain the strAddressSearc h variable
anywhere in the Address field.

Alternatively, you can place the % on only one side of the variable to
search for records with the address
starting with:
"SELECT * FROM Customers WHERE Address LIKE '" & strAddressSearc h & "%'"
or ending with:
"SELECT * FROM Customers WHERE Address LIKE '%" & strAddressSearc h & "'"
the search string.

Sorry, I'm not going to re-write all your code for you, but this is what you
need in your SQL statement to make the LIKE feature work. Not sure that you
can do this with the query builder, you may have to code this by hand.
You'll learn more doing it that way anyways.

HTH
Ryan


"Lucky" <tr***********@ yahoo.com> wrote in message
news:2a******** *************** ***@posting.goo gle.com...

I have a vb.net windows app that connects to an access database. The
database has 1 table. One of the columns is called "Address". A user
enters any address into a textbox (txtValue) then clicks the Find
button (cmdFind) and a datagrid displays all the rows with this
address. The problem is that it will only find the the address if the
user types it exactly as it's entered the the DB. For example: the
address column has 123 Anystreet Rd. If the user types 123 Anystreet
then clicks Find, no rows are returned. But if the user types 123
Anystreet Rd, all rows are displayed containing that address. I don't
want that because the user won't know exactly how it's stored in the
DB. I used an oleDbDataAdapte r and Query builder for my select
statement which is: SELECT *
FROM [Primary Site List]
WHERE (Address LIKE ?)

My cmdFind_click event code is as follows (I'm using option buttons):

If optAddress.Chec ked = True Then
OleAddressAdapt er.SelectComman d.Parameters("A ddress").Value
= txtValue.Text
DsPrimarySite1. Clear() 'this is my dataset
OleAddressAdapt er.Fill(DsPrima rySite1)
End If

How can I get it to return rows containing any portion of the address
entered by the user?

I am a newbie and this is my first app, so please be detailed in your
help. I greatly appreciate any assistance you can provide.

[Lucky]

Thanks for the hint Ryan. Going to try it out tomorrow. I really don't
think I asked anyone to re-write all my code for me though. Heck I
only posted 5 lines of code plus the select statement so folks would
know where I'm coming from. My database is Access 2000 and I'm coding
in vb.net, but I guess the SQL query gave you the impression I was
asking a SQL question. I know it gets confusing, believe me. After
being stuck on this the past 4 days I'm ready to try anything!

"Ryan Cooper" <in**@ryancoope r.com> wrote in message news:<qu******* ***********@typ hoon.sonic.net> ...

Your question really is not a .NET question but a SQL question. Anyways, if
you are using the LIKE feature in a sql statement, you have to use a special
"wildcard" character (%) to make the system look for records that don't
match exactly. Heres an example:

"SELECT * FROM Customers WHERE Address LIKE '%" & strAddressSearc h & "%'"

This will return ALL records that contain the strAddressSearc h variable
anywhere in the Address field.

Alternatively, you can place the % on only one side of the variable to
search for records with the address
starting with:
"SELECT * FROM Customers WHERE Address LIKE '" & strAddressSearc h & "%'"
or ending with:
"SELECT * FROM Customers WHERE Address LIKE '%" & strAddressSearc h & "'"
the search string.

Sorry, I'm not going to re-write all your code for you, but this is what you
need in your SQL statement to make the LIKE feature work. Not sure that you
can do this with the query builder, you may have to code this by hand.
You'll learn more doing it that way anyways.

HTH
Ryan

[Lucky]

Unfortunatly that solution doesn't work. Query builder wouldn't accept
that.
Does anyone else have any ideas? More info is that I'm using an
oleDbDataAdapte r to connect to my Access database.

"Ryan Cooper" <in**@ryancoope r.com> wrote in message news:<qu******* ***********@typ hoon.sonic.net> ...

Your question really is not a .NET question but a SQL question. Anyways, if
you are using the LIKE feature in a sql statement, you have to use a special
"wildcard" character (%) to make the system look for records that don't
match exactly. Heres an example:

"SELECT * FROM Customers WHERE Address LIKE '%" & strAddressSearc h & "%'"

This will return ALL records that contain the strAddressSearc h variable
anywhere in the Address field.

Alternatively, you can place the % on only one side of the variable to
search for records with the address
starting with:
"SELECT * FROM Customers WHERE Address LIKE '" & strAddressSearc h & "%'"
or ending with:
"SELECT * FROM Customers WHERE Address LIKE '%" & strAddressSearc h & "'"
the search string.

Sorry, I'm not going to re-write all your code for you, but this is what you
need in your SQL statement to make the LIKE feature work. Not sure that you
can do this with the query builder, you may have to code this by hand.
You'll learn more doing it that way anyways.

HTH
Ryan

[Ron Allen]

Lucky,
QueryBuilder doesn't like this because 'straight' Access uses a
different syntax for wildcards. Use * vs % in query builder. You will need
to use the other syntax through ADO.NET though.

Ron Allen
"Lucky" <tr***********@ yahoo.com> wrote in message
news:2a******** *************** ***@posting.goo gle.com...

Unfortunatly that solution doesn't work. Query builder wouldn't accept
that.
Does anyone else have any ideas? More info is that I'm using an
oleDbDataAdapte r to connect to my Access database.

"Ryan Cooper" <in**@ryancoope r.com> wrote in message news:<qu******* ***********@typ hoon.sonic.net> ...

Your question really is not a .NET question but a SQL question. Anyways, if you are using the LIKE feature in a sql statement, you have to use a special "wildcard" character (%) to make the system look for records that don't match exactly. Heres an example:

"SELECT * FROM Customers WHERE Address LIKE '%" & strAddressSearc h & "%'"
This will return ALL records that contain the strAddressSearc h variable anywhere in the Address field.

Alternatively, you can place the % on only one side of the variable to
search for records with the address
starting with:
"SELECT * FROM Customers WHERE Address LIKE '" & strAddressSearc h & "%'" or ending with:
"SELECT * FROM Customers WHERE Address LIKE '%" & strAddressSearc h & "'" the search string.

Sorry, I'm not going to re-write all your code for you, but this is what you need in your SQL statement to make the LIKE feature work. Not sure that you can do this with the query builder, you may have to code this by hand.
You'll learn more doing it that way anyways.

HTH
Ryan