none
Extend typed dataset to be used by another database RRS feed

  • Question

  • We have a DataSet with Table Adapters built for MSAccess using OLEDB. We are using Visual Studio 2005 and .Net 2.0.
    Our goal is to use the same dataset with SQL Server, while maintaining compatibililty with Access.
    After testing all the queries, we found that Visual Studio was adding a ` char (ASCII 96) to the insert, update, delete queries in the designer.cs file.

    For example:

    INSERT INTO `table` (`col1`, `col2`, `col3`) values (....)

    The ` char causes errors in SQL Server, but works fine in Access.
    Is there a reason for this character? Assuming it's for escape purposes, is there a setting to make Visual Studio use another character, eg "[" or "]"?

    Thanks

    • Moved by Nancy Shao Friday, August 28, 2009 6:39 AM To get better support (From:Visual Studio Extensibility)
    Wednesday, August 26, 2009 6:28 PM

Answers

  • Hi prg1912,

     

    Do you use the wizard to generate the code for inserting, updating and querying from the Access Database?

    If you use wizard to generate commands form SQL Server, it will add “[]”.

    For the project that supports multiple databases, it is favorable to write the commands manually. It will eliminate many problems.

     

    If you have any questions or concerns, please update the thread and we will have a further discussion.

     

    Best Regards

    Yichun Feng

    • Marked as answer by Yichun_Feng Thursday, September 3, 2009 2:32 AM
    Monday, August 31, 2009 3:45 AM

All replies

  • We have a DataSet with Table Adapters built for MSAccess using OLEDB. We are using Visual Studio 2005 and .Net 2.0.

    When the designer creates insert, update and delete queries it encloses the table name and field names with a ` (ASCII char 96).
    For example:

    INSERT INTO `table` (`col1`, `col2`, `col3`) values (....)

    Is there a reason for this character? Assuming it's for escape purposes, is there a setting to make Visual Studio use another character, eg "[" or "]"?

    • Merged by Yichun_Feng Friday, August 28, 2009 7:19 AM Duplicate
    Wednesday, August 26, 2009 4:55 PM
  • One point I forgot to mention:
    Our main goal is to use the same SQL queries (basic inserts, updates, deletes) in both MS Access and SQL Server.  Our app was originally written for Access using the VS designer to generate the queries. After testing these queries, we found that if the ` char was removed and replaced by [ ] brackets, then all the queries would work in both db's.
    Thursday, August 27, 2009 9:21 PM
  • One point I forgot to mention:
    Our main goal is to use the same SQL queries (basic inserts, updates, deletes) in both MS Access and SQL Server.  Our app was originally written for Access using the VS designer to generate the queries. After testing these queries, we found that if the ` char was removed and replaced by [ ] brackets, then all the queries would work in both db's.
    Thursday, August 27, 2009 9:44 PM
  • I am not sure your question is about the visual studio extensibility, May be Ado.Net is more suitable for your question, VSX is about the extensibility in Visual studio, so you'd better post your question in ADO.NET or Sql server.
    Friday, August 28, 2009 1:58 AM
  • Hi Prg1912,

    I am moving this thread from Base "Visual Stuido Extensibility" forum to the "ADO.NET DateSet" forum, since the issue is related to ADO.NET. There are more ADO.NET experts in the "ADO.NET DateSet" forum.

    Thanks,
    Nancy


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, August 28, 2009 6:39 AM
  • Hi prg1912,

     

    Do you use the wizard to generate the code for inserting, updating and querying from the Access Database?

    If you use wizard to generate commands form SQL Server, it will add “[]”.

    For the project that supports multiple databases, it is favorable to write the commands manually. It will eliminate many problems.

     

    If you have any questions or concerns, please update the thread and we will have a further discussion.

     

    Best Regards

    Yichun Feng

    • Marked as answer by Yichun_Feng Thursday, September 3, 2009 2:32 AM
    Monday, August 31, 2009 3:45 AM