I recently wrote an application and used MSSQL as the backend. After successful completion, I added an Access .mdb file to the app so that I could make it portable. I changed the code appropriately from SQL Command to OLEDB Command, but it does not work. This is the rub: I do not get an error and it will not return results when it is run from inside the program, however, when I copy the command line that fails and paste it directly into the Access Database query tool and run it, it returns a perfect result. I am baffled! I have the feeling that there is a syntax difference between the MSSQL and Access, but I cannot discover it, nor would that explain why it works correctly when it is pasted into the program directly. Following is the SQL that comes directly from the program:
"SELECT DISTINCT T.Trans_Name AS Transmission, M.Manufacturer, M.Concern, M.Years, M.Description, M.ATB " +
" FROM Master AS M INNER JOIN Trans AS T ON M.Trans = T.Trans WHERE M.Manufacturer = @Manufacturer "
Any insight would be greatly appreciated.
Pat
Loading
Bechir BejaouiPosted May 27, 2008, 5:45 PM
Pat TumPosted May 2, 2008, 12:14 PM
I am sorry that no one was able to reply to this question. I did find the solution though and am posting it for those that might be interested or are in a similar situation.
Access apparently does not support named parameters. I found this obscure piece of code on the internet and was able to modify it to resolve the issue, which was as I suspected, a syntax problem with the single quotes.
//For Access Query
string Sql1 = "SELECT DISTINCT T.Trans_Name AS Transmission, [M.Manufacturer] AS Manufacturer, [M.Concern] AS Concern, " +
"[M.Years] AS Years, [M.Description] AS Description, [M.ATB] AS Bulletin " +
" FROM Master AS M INNER JOIN Trans AS T ON M.Trans = T.Trans WHERE ";
string Sql2 = " AND ";
string trans = textBoxTrans.Text;
string Sql3 = " T.Trans_Name = \'" + trans + " \'";
Hope this helps someone else, Pat