Re: Parameterized query problem using Access Database in VS.NET2005

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Yes, of course. In your UI you let the user provide (or your code provides)
the rest of the wildcard expression. For example, if you were looking for
authors whose name begins with "B" you would pass an expression like

WHERE AuthorName LIKE 'B%'

You can also change the LIKE expression to a parameter so the SQL would look
like this:

WHERE AuthorName LIKE @NameWanted

or (in OLEDB-driven SQL parameters)
WHERE AuthorName LIKE ?

Your code would populate the Parameter with the Parameter value ("B" in this
case) concatenated with the wildcard character (%). The Parameter.Value
would then be "B%".

There are several other rules and tips that can help the SQL query optimizer
work more quickly (like ensuring that there is an index on the column and
that the wildcard is at the end of the expression) that I discuss in my
book.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"JohnAdams" <u32320@uwe> wrote in message news:6ed77d8cf7392@xxxxxx
Paul Clement wrote:

For querying an Access database with the Jet OLEDB Provider you nee to use
the 'Like' keyword with
the '%' symbol.

Paul
~~~~
Microsoft MVP (Visual Basic)


With this code
SELECT CustomerNumber, Name, Address, City, State, Zip, Phone,
LastOrdDate, Inactive
FROM Customers
WHERE (Name LIKE '%')
it gives me all rows of the table with no ability to input what row(s) I
am
looking for.

That would work if I wanted all entries but I only want selected data, for
instance if I wanna look for those customer names that start with J in an
access query it would be J* and the criteria would be LIKE [" "] but this
does not work under the VS.net2005 SQL Select statements.

The way I have it set up is a search text box on the form and the user
inputs
the name or customer number and then hits search to start the query if
they
dont have the full name or customer number then they could only input part
of
it and search to get all those that fit the criteria.

any thoughts?



.

Relevant Pages

  • Re: OOP - a question about database access
    ... >>and project so much better in SQL DBMSes than in ODBMSes, ... >>100x more bytes from the database, just because you want your objects to ... > a related invoice.. ... > assoication from the customer to the invoice collection and have done ...
    (comp.object)
  • Re: object databases
    ... Now, tell me, you can do all that with 2 lines of code with SQL ... didn't provide the Customer, Order and OrderDetail classes. ... If you try to map classes to tables, ...
    (comp.object)
  • Re: How can one make a wildcard key for a chain statement in rpgle?
    ... If you have the SQL precompiler, ... Then do a SETLL on the lib result. ... If library is a wildcard, SETLL by library using the wildcard ... turn librarywc on if wildcard for library ...
    (comp.sys.ibm.as400.misc)
  • RE: search phrase
    ... What you might want to try is using the SQL Server 2000 XML thersoursa files ... After the Full Population is complete, run the following SQL FREETEXT queries ... "Yair Nissan" wrote: ... Remove all non alphanumeric characters from the customer name. ...
    (microsoft.public.sqlserver.fulltext)
  • RE: What to do with 1.5 million records...
    ... You have a lot of duplicated information which can be eliminated. ... search on one customer name locks up the server, Access not responding, white ... Do I set this up on a SQL backend? ... Qty, Amt, Pmt Date, Service Tech, Ins Name, Ins Paid, Balance ...
    (microsoft.public.access.tablesdbdesign)