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("Addres s").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. 5 1365
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" <tr***********@yahoo.com> wrote in message
news:2a**************************@posting.google.c om... 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("Addres s").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. http://www.devguru.com/Technologies/...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/...kref/like.html
"Mike Hildner" <mh******@afweb.com> wrote in message
news:un**************@tk2msftngp13.phx.gbl... 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" <tr***********@yahoo.com> wrote in message news:2a**************************@posting.google.c om... 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("Addres s").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.
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("Addres s").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
"Lucky" <tr***********@yahoo.com> wrote in message
news:2a**************************@posting.google.c om... 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("Addres s").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.
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.
"Mike Hildner" <mh******@afweb.com> wrote in message news:<un**************@tk2msftngp13.phx.gbl>... 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
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 -----Original Message----- OK, spent 5 days searching for info on this one. Seems
like it wouldbe pretty common. Here's the deal:
I have a vb.net windows app that connects to an access
database. I'musing an oleDbDataAdapter, Query Builder, oleDbDataSet, oleDbDataConnection, and a datagrid. The database has 1
table. One ofthe columns is called "Address". A user enters any
address into atextbox (txtValue) then clicks the Find button (cmdFind)
and adatagrid displays all the rows with this address. The
problem is thatit will only find the the address if the user types it
exactly as it'sentered the the DB. For example: the address column has
123 AnystreetRd. If the user types 123 Anystreet then clicks Find, no
rows arereturned. But if the user types 123 Anystreet Rd, all
rows aredisplayed containing that address. I don't want that
because the userwon'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 addressentered by the user? The Like command doesn't seem to
work in querybuilder. How the heck would you search an Access database?
I am a newbie and this is my first app, so please be
detailed in yourhelp. I greatly appreciate any assistance you can provide. . This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: John Ladasky |
last post by:
Hi there,
Just wanted to share a frustrating programming bug that, when I
figured it out, made me gasp, and then laugh.
In one of my programs I wrote...
c = max(a,b)
....and I was...
|
by: Harag |
last post by:
Hi all
Classic ASP,
Textpad
Local "test" WebServer. IIS5
Well my MS script debugger isn't running and I can't findout why. I'm
sick of it failing on me so was looking for an alternative. I...
|
by: Isaac Grover |
last post by:
Hi everyone,
Just out of curiosity I recently pointed one of my hand-typed pages at the W3
Validator, and my hand-typed code was just ripped to shreds. Then I pointed
some major sites...
|
by: garyolsen |
last post by:
In C++ what kind of unexpected conditions should be handled as exceptions?
Besides dividing by 0, bad memory allocation, what're the most popular
exceptions?
When should not use exception,...
|
by: MLH |
last post by:
A97 HELP shows the proper syntax for using Nz as
Nz(variant)
I'm wondering what to expect from potential past
misuse I've made. For example, consider the following...
Private Sub...
|
by: JohnQ |
last post by:
(The "C++ Grammer" thread in comp.lang.c++.moderated prompted this post).
It would be more than a little bit nice if C++ was much "cleaner" (less
complex) so that it wasn't a major world wide...
|
by: cmdolcet69 |
last post by:
Pleae help im trying to help fix a bug in some software that uses the
Dev Express 3rd party control for Reports.Had anyone ever used this
product?
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |