Parameterized query problem using Access Database in VS.NET2005

  • Thread starter Thread starter JohnAdams
  • Start date Start date
J

JohnAdams

New to the VS.NET 2005 and I am writing a program using an access database as
the datasource in visual studio.NET 2005 The problem I have is I am trying
to write a parameterized query where a wildcard can be used to pull a
complete name from only one or two characters entered. If I have the exact
name I can get it to work with a ? but this defeats the purpose of the query
and the sql select for the access database doesn't reconize the * that is
used in access as a wildcard.


The satements I have right now are:


SELECT CustomerNumber, Name, Address, City, State, Zip, Phone, LastOrdDate,
Inactive
FROM Customers
WHERE Name = ?


SELECT CustomerNumber, Name, Address, City, State, Zip, Phone, LastOrdDate,
Inactive
FROM Customers
WHERE CustomerNumber = ?


and when I try to do this (or any version of it):


SELECT CustomerNumber, Name, Address, City, State, Zip, Phone, LastOrdDate,
Inactive
FROM Customers
WHERE CustomerNumber = '*'


or
WHERE CustomerNumber = ? &'*'


I get an error of this:

Error in WHERE clause near '*'.
Unable to parse query text.


I have even tried the % for sql databases and still no help.
 
¤ New to the VS.NET 2005 and I am writing a program using an access database as
¤ the datasource in visual studio.NET 2005 The problem I have is I am trying
¤ to write a parameterized query where a wildcard can be used to pull a
¤ complete name from only one or two characters entered. If I have the exact
¤ name I can get it to work with a ? but this defeats the purpose of the query
¤ and the sql select for the access database doesn't reconize the * that is
¤ used in access as a wildcard.

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)
 
Paul said:
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)
Click to expand...


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?
 
--
____________________________________
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 said:
Paul said:
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)
Click to expand...


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?
Click to expand...
 
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 said:
Paul said:
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)
Click to expand...


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?
Click to expand...
 
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
Click to expand...


Yes this helped very much. It solved my issue and program worked as I wanted
with LIKE ? in the query code and user inputs J% in the search.
Thank You very much
 
Yes this helped very much. It solved my issue and program worked as I wanted
with LIKE ? in thequerycode and user inputs J% in the search.
Click to expand...

I have similary problem...

I want to have pramized query, so my code is...
SELECT name
FROM person
WHERE (name LIKE N'%'+ @partOfName +'%')

I use VS 2005 with MS SQL server Express Edition
I used this query in Designing Queries and Views under "Query
Builder"(wizzard). I had filled in the form which vs 2005 display me
after I pressed "Execute Query"and it produce i.e. one row. But when I
start my application and I fill in my form with the same text it
doesn't produce anything...
I use SQL Server Management Studio Express and I applied the same
query there with the same text and it produces one row...
When I use another Database Tool named "Preview data" in VS2005 it
behaves like my application...
Another threat in this case is if my 'name' in person table is
"Mike "(with spaces) and I put as the parametr "Mike" it produce
me one row but when I put "ike" it failes.

Any ideas?
I took me 4 hours to trace this problem without a solution...
I think it's MS fault... What do you think?
 
I think you need to read the BOL documentation on LIKE expressions. Yours is
invalid.
You get to use one wildcard character.
I expect you need to use the CHARINDEX operator instead.

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------
 
Back
Top