Changing between mssql and access through connection string
-
Hi, I have a c# (.Net 2) program that connects to access mdb file. I use strong typed dataset to do this. Now I have searched the web and found ms sql connection string I replaced what was generated by the wizard with the new one and now I can successfully connect to MS SQL server (with the same table). The problem starts when I try to delete rows from the table. In access it works great obviously, But with MS SQL I get exception when calling udpate (Wrong syntax near '.'). Is it possible at all to use strong typed datasets to connect to both MS SQL and ACESS. If yes how? Thanks, Yaniv
-
Hi, I have a c# (.Net 2) program that connects to access mdb file. I use strong typed dataset to do this. Now I have searched the web and found ms sql connection string I replaced what was generated by the wizard with the new one and now I can successfully connect to MS SQL server (with the same table). The problem starts when I try to delete rows from the table. In access it works great obviously, But with MS SQL I get exception when calling udpate (Wrong syntax near '.'). Is it possible at all to use strong typed datasets to connect to both MS SQL and ACESS. If yes how? Thanks, Yaniv
The SQL syntax is different between Access X| and SQL server. You are not going to be able to just change connection strings - you will need to duplicate some/all your SQL statements to conform with the TSQL syntax.
Never underestimate the power of human stupidity RAH
-
The SQL syntax is different between Access X| and SQL server. You are not going to be able to just change connection strings - you will need to duplicate some/all your SQL statements to conform with the TSQL syntax.
Never underestimate the power of human stupidity RAH
Hi, Thanks for your reply. I ran the sql query: DELETE FROM table1 WHERE id = 1 on both access and ms sql and it worked fine. But I don't create the queries manually, It is created by the adapter (I think) of my strong typed database ( so I don't know whats really going there). Is there a way I can view the query created by it?
Sincerely yours Y.R.
-
Hi, Thanks for your reply. I ran the sql query: DELETE FROM table1 WHERE id = 1 on both access and ms sql and it worked fine. But I don't create the queries manually, It is created by the adapter (I think) of my strong typed database ( so I don't know whats really going there). Is there a way I can view the query created by it?
Sincerely yours Y.R.
Y_R wrote:
But I don't create the queries manually
This where you have a problem. You are using the MS built in tools and therefore have only limited knowledge and understanding of the mechanics of the database and the DAL. I suggest you completely reengineer your application to use a proper data access layer where you have control and understanding of the DAL and database operations. As for
Y_R wrote:
Is there a way I can view the query created by it?
I have absolutely no idea (but would expect it to be there somewhere) as I have only used the built in stuff enough to identify what a disaster it can be. BTW this is not a rant against you, everyone has to start somewhere and you have asked intelligent questions and shown a willingness to learn. Do some reasearch into DALs, there are plenty of examples around.
Never underestimate the power of human stupidity RAH
-
Y_R wrote:
But I don't create the queries manually
This where you have a problem. You are using the MS built in tools and therefore have only limited knowledge and understanding of the mechanics of the database and the DAL. I suggest you completely reengineer your application to use a proper data access layer where you have control and understanding of the DAL and database operations. As for
Y_R wrote:
Is there a way I can view the query created by it?
I have absolutely no idea (but would expect it to be there somewhere) as I have only used the built in stuff enough to identify what a disaster it can be. BTW this is not a rant against you, everyone has to start somewhere and you have asked intelligent questions and shown a willingness to learn. Do some reasearch into DALs, there are plenty of examples around.
Never underestimate the power of human stupidity RAH
Before we started the project we checked and found strong typed datasets the most fitting solution, Though some people strongly advised against it. I works fine in a different area of my project (With mysql), So I don't know why people dislike it so much, But I have little experience with databases so I guess you have your reasons. Anyway, For my specific problem I will do as you suggested and further my research in to the DALs. Thank you very much you really helped me.
Sincerely yours Y.R.