|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Parameterized query problem using Access Database in VS.NET2005the 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. On Wed, 07 Mar 2007 19:22:32 GMT, "JohnAdams" <u32320@uwe> wrote: ¤ 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 Clement wrote:
> With this code>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) 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? --
Show quote
____________________________________ 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@uwe...
> 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? > 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 -- Show quote____________________________________ 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@uwe... > 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? > >Your code would populate the Parameter with the Parameter value ("B" in this Yes this helped very much. It solved my issue and program worked as I wanted>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 > with LIKE ? in the query code and user inputs J% in the search. Thank You very much -- Message posted via DotNetMonster.com http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-ado-net/200703/1 > Yes this helped very much. It solved my issue and program worked as I wanted I have similary problem...> with LIKE ? in thequerycode and user inputs J% in the search. 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. -- Show quote____________________________________ 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) ----------------------------------------------------------------------------------------------------------------------- <Michal.Gorski***@gmail.com> wrote in message news:1175030483.878908.8220@y80g2000hsf.googlegroups.com... >> 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. > > 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? >  |
|||||||||||||||||||||||
Other interesting topics