Hi,
I am using VB.Net 2.0 and have connected to Microsoft Access but having trouble running a query as it uses * as a wildcard, but when I run it in .Net it returns nothing as needs to have %.
The solutions I can think of are:
1) If I was able to extract out the actual SQL string from the MS Access query from my code then I could do a Replace("*", "%") then would work - but don't know how to extract the SQL string.
2) If there is some way to make the query work with the * then that would also solve my problem.
The problem is that I have to run through 850 MS Databases (Student Lab Tests) so can't just change them manually.
The code below is what I have used, and works fine if you use % in the Access query, but returns nothing if you use *.
Dim con As System.Data.OleDb.OleDbConnection = Nothing Dim cmd As System.Data.OleDb.OleDbCommand = Nothing Dim objRS As System.Data.OleDb.OleDbDataReader = Nothing
con =
New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFolder & "\Access\Access.mdb;")cmd =
New System.Data.OleDb.OleDbCommand()cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * FROM qryQ01"
con.Open()
objRS = cmd.ExecuteReader
Any help would be much appreciated.
Kind Regards,
Grant.
R
Bassam Mansoob
The Access online help topic "Using Wildcard Characters in String Comparisons" contains a nasty little sentence:
It is important to note that the ANSI SQL wildcards (%) and (_) are only available with MicrosoftR Jet version 4.X and the Microsoft OLE DB Provider for Jet. They will be treated as literals if used through Microsoft Access or DAO.
Hmm, trying to interpret that: queries executed in Access do NOT behave the same way as queries executed through OLE DB.I tried to find a workaround by executing the saved Access query from code (Command.Type = StoredProcedure) but that has the same problem, it only works if you encode the wildcard as "%". Ouch.
Kressilac
Hi,
If I put the % into the Access query then I can't run the query from Access or I will get the error you mentioned, but if I change it to % then save and close it, then run the query from my code it works
I also just went to use another query that uses as a wildcard (1 character) in Access and the same thing - doesn't work from my code arrrgggg.
If anyone is still checking this post, then any help would be much appreciated.
My Queries are:
Doesn't work from code:
SELECT tblCustomer.*, tblCustomer.PostalCode
FROM tblCustomer
WHERE (((tblCustomer.PostalCode) Like "*RZ"));
Does work from code:
SELECT tblCustomer.*, tblCustomer.PostalCode
FROM tblCustomer
WHERE (((tblCustomer.PostalCode) Like "%RZ"));
and
SELECT tblCustomer.CustomerID, tblCustomer.CustomerName, tblCustomer.Phone
FROM tblCustomer
WHERE (((tblCustomer.Phone) Not Like " . . . ") AND ((tblCustomer.Country) Like "France"));
Kind Regards,
Grant.
Will Harris
Cynthia Joffrion
There seems to be some confusion about the difference between a wildcard character in a string and the shortcut for requesting all fields.
In both SQL and Access you use * to select all fields...
Select * from MyTable
However when you want a wildcard character in a string you use % in SQL. If you tried to use two * or two % in the following command it would never work because either the "all fields" character would be wrong or you would unintentionally be searching for a incorrect literal character in the search criteria...
Select * from MyTable where Title Like 'VB%'
While we're on the subject it's better to list the fields you actually want in your select clause instead of using * (even if you happen to want all the fields that are currently in the table). If you end up adding new fields to your table later you could break existing code by unintentionally selecting those new fields and even if you don't there's no sense having the overhead of selecting fields you're not using.
itai_sh100