Anyone ever try this in vb.net?

  • Thread starter Thread starter Lucky
  • Start date Start date
L

Lucky

OK, spent 5 days searching for info on this one. Seems like it would
be pretty common. Here's the deal:

I have a vb.net windows app that connects to an access database. I'm
using an oleDbDataAdapter, Query Builder, oleDbDataSet,
oleDbDataConnection, and a datagrid. 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
oleDbDataAdapter 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.Checked = True Then
OleAddressAdapter.SelectCommand.Parameters("Address").Value
= txtValue.Text
DsPrimarySite1.Clear() 'this is my dataset
OleAddressAdapter.Fill(DsPrimarySite1)
End If

How can I get it to return rows containing any portion of the address
entered by the user? The Like command doesn't seem to work in query
builder. How the heck would you search an Access database?

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.
 
I don't use Access as a DB, but a couple things come to mind.

If Access supports it, try ...LIKE '%Anystreet%' - this would find '123
Anystreet', '123Anystreet Rd.' etc. SQL Server has a function called
DIFFERENCE, which gives you the difference between two soundex values (you
can write one yourself, it's not _that_ hard). Maybe searching the address
pieces for similar words would work. I find that users type in the same
address differently - typos etc.

In my experience, addresses are always difficult, because of data entry.
i.e. one user might type in 123 Anystreet Rd and the next Anystreet Rd 123.
We usually break our addresses into separate fields - block, direction,
street name, etc.

HTH
Mike
 
http://www.devguru.com/Technologies/jetsql/quickref/jet_sql_intro.html


JET SQL,, when they killing JET its a peice of outdated crock.


JetSQL does support LIKE operations..


http://www.devguru.com/Technologies/jetsql/quickref/like.html



Mike Hildner said:
I don't use Access as a DB, but a couple things come to mind.

If Access supports it, try ...LIKE '%Anystreet%' - this would find '123
Anystreet', '123Anystreet Rd.' etc. SQL Server has a function called
DIFFERENCE, which gives you the difference between two soundex values (you
can write one yourself, it's not _that_ hard). Maybe searching the address
pieces for similar words would work. I find that users type in the same
address differently - typos etc.

In my experience, addresses are always difficult, because of data entry.
i.e. one user might type in 123 Anystreet Rd and the next Anystreet Rd 123.
We usually break our addresses into separate fields - block, direction,
street name, etc.

HTH
Mike

Lucky said:
OK, spent 5 days searching for info on this one. Seems like it would
be pretty common. Here's the deal:

I have a vb.net windows app that connects to an access database. I'm
using an oleDbDataAdapter, Query Builder, oleDbDataSet,
oleDbDataConnection, and a datagrid. 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
oleDbDataAdapter 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.Checked = True Then
OleAddressAdapter.SelectCommand.Parameters("Address").Value
= txtValue.Text
DsPrimarySite1.Clear() 'this is my dataset
OleAddressAdapter.Fill(DsPrimarySite1)
End If

How can I get it to return rows containing any portion of the address
entered by the user? The Like command doesn't seem to work in query
builder. How the heck would you search an Access database?

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.
Click to expand...
Click to expand...
 
Hi Lucky;

Another approach might be to put the wildcard character into your
variable rather than in the actual SQL statement because you are saying the
query builder does not allow the wildcard as an option. What I mean is, pass
in the variable with the % signs concatenated on the beginning and end of
your passed in variable, which will serve the same purpose as actually
hard-coding them in the sql string. The problem is not the LIKE operator
itself, it's the lack of the wilcard character that is causing only exact
matches to appear.

OleAddressAdapter.SelectCommand.Parameters("Address").Value = "%" &
txtValue.Text & "%"

IMHO, you're much better off hand-writing your SQL query's than using the
query builder, because of the lack of flexibility in how you develop
querys... for instance, Unions, Multiple Joins, Correlated Subqueries, and
several other features are very clunky to write in the querybuilder and will
often not validate correctly.

Anyways, try my suggestion above because the wildcard is definitely the
missing link here...

Ryan
 
Thanks Mike! I broke up the address box and now can search under
different criteria. That never occurred to me.
Now to start tweaking some things.
Many thanks to all who posted.
 
I think if you concatenate % on to the beginning and end
of what you're searching for, it will work.
For Example:

LIKE '%' + ? + '%'

This might not be exact but maybe it will set you on a
path to what you need.

% begins with whatever
contains the text that you're searching for
% ends with whatever
 
Back
Top