SQL Query Issue: MSSQL vs. Access
-
I recently wrote an application in C# 2005 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
-
I recently wrote an application in C# 2005 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
-
What do the code to your command object looks like? Maybe you're not connecting to the file correctly. Do a select * for one of the table and see if you get results.
Thank you for your reply, Youtea. The application returns results from query's that are built on a series of "if" statements. These are created from the request of the user. //For Access Query string Sql1 = "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 "; string Sql2 = " AND "; string Sql3 = " T.Trans_Name = @Trans "; string Sql4 = " M.Manufacturer = @Manufacturer "; string Sql5 = " M.Concern = @Concern "; string Sql6 = " M.Years = @Year "; if (textBoxMans.Text != "" && textBoxTrans.Text == "" && textBoxCons.Text == "" & textBoxYears.Text == "") { Sql = Sql1 + Sql4; } The resulting query string that comes from a selection using the text box "textboxmans.text" is as follows: "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 ". This should return results and returns perfectly when pasted into the Access program manually, however, using the C# application, it connects properly but says that there are no matching results for the query, which is both incorrect and also does not match the SAME query when it is pasted into both the Access program or the SQL query analyzer using MS SQL 2000 (exact same table names apply to both MSSQl and ACCESS).Also, if I leave off the added statements and run just Sql1 WITHOUT the "WHERE" clause, it works! I seem to think there is syntax error, but if so, then why does it work when the resulting query, as assembled by the application, is copied and pasted into Access? I am BAFFLED!!! Appreciate your reply. Any other ideas?....Pat
-
Thank you for your reply, Youtea. The application returns results from query's that are built on a series of "if" statements. These are created from the request of the user. //For Access Query string Sql1 = "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 "; string Sql2 = " AND "; string Sql3 = " T.Trans_Name = @Trans "; string Sql4 = " M.Manufacturer = @Manufacturer "; string Sql5 = " M.Concern = @Concern "; string Sql6 = " M.Years = @Year "; if (textBoxMans.Text != "" && textBoxTrans.Text == "" && textBoxCons.Text == "" & textBoxYears.Text == "") { Sql = Sql1 + Sql4; } The resulting query string that comes from a selection using the text box "textboxmans.text" is as follows: "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 ". This should return results and returns perfectly when pasted into the Access program manually, however, using the C# application, it connects properly but says that there are no matching results for the query, which is both incorrect and also does not match the SAME query when it is pasted into both the Access program or the SQL query analyzer using MS SQL 2000 (exact same table names apply to both MSSQl and ACCESS).Also, if I leave off the added statements and run just Sql1 WITHOUT the "WHERE" clause, it works! I seem to think there is syntax error, but if so, then why does it work when the resulting query, as assembled by the application, is copied and pasted into Access? I am BAFFLED!!! Appreciate your reply. Any other ideas?....Pat
-
Thank you for your reply, Youtea. The application returns results from query's that are built on a series of "if" statements. These are created from the request of the user. //For Access Query string Sql1 = "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 "; string Sql2 = " AND "; string Sql3 = " T.Trans_Name = @Trans "; string Sql4 = " M.Manufacturer = @Manufacturer "; string Sql5 = " M.Concern = @Concern "; string Sql6 = " M.Years = @Year "; if (textBoxMans.Text != "" && textBoxTrans.Text == "" && textBoxCons.Text == "" & textBoxYears.Text == "") { Sql = Sql1 + Sql4; } The resulting query string that comes from a selection using the text box "textboxmans.text" is as follows: "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 ". This should return results and returns perfectly when pasted into the Access program manually, however, using the C# application, it connects properly but says that there are no matching results for the query, which is both incorrect and also does not match the SAME query when it is pasted into both the Access program or the SQL query analyzer using MS SQL 2000 (exact same table names apply to both MSSQl and ACCESS).Also, if I leave off the added statements and run just Sql1 WITHOUT the "WHERE" clause, it works! I seem to think there is syntax error, but if so, then why does it work when the resulting query, as assembled by the application, is copied and pasted into Access? I am BAFFLED!!! Appreciate your reply. Any other ideas?....Pat
You are likely having problems with parameter order in the query. Unlike the SQl Data classes, Oledb essentially ignores parameter names, and relies exclusively on parameter position in the query. It seems likely that you might be depending on matching the parameter names (rather than their order in the query string) before executing the query (although I can't tell that from the code provided...).
-
You are likely having problems with parameter order in the query. Unlike the SQl Data classes, Oledb essentially ignores parameter names, and relies exclusively on parameter position in the query. It seems likely that you might be depending on matching the parameter names (rather than their order in the query string) before executing the query (although I can't tell that from the code provided...).
Rob, Thank you for your thoughtful answer. Yes, It has been so long since I used OLEDB that I forgot that, but in this case, it was not the problem. I mentioned in the beginning that I was suspicious that it had something to do with the syntax passing the parameter. I found an obscure piece of code on the net yesterday that showed me the problem. It had to do with passing the single quotes. The solution is pasted below in the event that it might also help some others: //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 + " \'"; Thanks to you and "Youtea" for your input, Pat
-
I recently wrote an application in C# 2005 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
Access does not support named parameters (e.g. @Manufacturer). It only supports positional parameters indicated with the ? character.
DoEvents: Generating unexpected recursion since 1991