none
Why has simple become difficult?

    Question

  • I have two identical databases one in Microsoft Access the other in SQL Server Compact Edition 3.5. Both are OLEDB compliant.

    The application I'm writing is designed to be a standalone tool, and changes made to the .NET framework made using Access as a backend data storage no longer practical.

    I remade the Access database as a SQL Server Compact Edition 3.5 database using the VB.NET IDE. To save the previous work I changed the Access data provider to the SSCE 3.5 data provider and matched the correct database to the appropriate data provider.

    Access Connection String:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\Users\Mark\Documents\Visual Studio 2008\Projects\BindingCtrls\BindingCtrls\bin\Debug\objBkUp.ACCDB";Persist Security Info=False

    SSCE Connection String:

    Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source="C:\Users\Mark\Documents\Visual Studio 2008\Projects\BindingCtrls\BindingCtrls\bin\Debug\Archives.SDF";Persist Security Info=False

    The previously working code failed. I suspected a subtle difference between the databases. A developer acquaintance suggested I write both database schemas into XML files and compare them with a freeware text comparator named  ExamDiff. In the interest of saving space I'm only going to show one column's differences between the two databases.

    Access DB column:

    <xs:element name="PCID" type="xs:int" minOccurs="0" />

    SQL Server CE column:

    <xs:element name="PCID" msdata:ReadOnly="true" type="xs:int" minOccurs="0" />

    As you can see the only difference between the two columns is: msdata:ReadOnly="true".

    The simple question is: Why can't I change the msdata:ReadOnly="true" property to false using the VB.NET Server Explorer tool inside the VB.NET IDE?

    They are suppose to be compatible. There is a flaw.


    MRM256

    Thursday, March 20, 2014 2:00 PM

All replies

  • I thought it would be helpful  for those who are interested to place the test application out on OneDrive http://1drv.ms/1hIMEPw , so you can see exactly what I'm doing.

    We will test the Access database, followed by the SQL Server CE database. All we are doing is activating one database, add two items; then do the same test with the other database.

    In the tests I ran the Access database worked without any problem. However, the SQL Server CE one blew up.

    In the frmBinding_Load event I have a section I partitioned off with dashes.

    '----------------------------------------------------------------------

    'Function to build Connection string to Access DB

    'cnnAccessCtrlDB = objDBCnn.cnnOLDEB(strAppPath & "\objBkUp.ACCDB")

    'strDBInUse = strAppPath & "\objBkUp.ACCDB"

    'Fill DataSet; including all DataTables and data

    'dsAccess = objDBStuff.getDataSetAndFill(cnnAccessCtrlDB)

    'Function to build connection to SSCE DB

    'cnnSSCE = objDBCnn.cnnOLDEB(strAppPath & "\Archives.SDF")

    'strDBInUse = strAppPath & "\Archive.SDF"

    'Fill DataSet; including all DataTables and data

    'dsAccess = objDBStuff.getDataSetAndFill(cnnSSCE)

    '----------------------------------------------------------------------

    For the first test we will active the Access database.

    '----------------------------------------------------------------------

    'Function to build Connection string to Access DB

    cnnAccessCtrlDB = objDBCnn.cnnOLDEB(strAppPath & "\objBkUp.ACCDB")

    strDBInUse = strAppPath & "\objBkUp.ACCDB"

    'Fill DataSet; including all DataTables and data

    dsAccess = objDBStuff.getDataSetAndFill(cnnAccessCtrlDB)

    'Function to build connection to SSCE DB

    'cnnSSCE = objDBCnn.cnnOLDEB(strAppPath & "\Archives.SDF")

    'strDBInUse = strAppPath & "\Archive.SDF"

    'Fill DataSet; including all DataTables and data

    'dsAccess = objDBStuff.getDataSetAndFill(cnnSSCE)

    '----------------------------------------------------------------------

    Press F5 to run the application. Press the Yes button to authorize item archiving on the PC where you are testing this application. Your PC will be different from mine.

    Once the form is up on the tree view control open up some folder and add at least two items to the database. Similar to the image below:

    As mentioned previously this one works. Now we are going to activate the SQL Server CE database like so:

    '----------------------------------------------------------------------

    'Function to build Connection string to Access DB

    'cnnAccessCtrlDB = objDBCnn.cnnOLDEB(strAppPath & "\objBkUp.ACCDB")

    'strDBInUse = strAppPath & "\objBkUp.ACCDB"

    'Fill DataSet; including all DataTables and data

    'dsAccess = objDBStuff.getDataSetAndFill(cnnAccessCtrlDB)

    'Function to build connection to SSCE DB

    cnnSSCE = objDBCnn.cnnOLDEB(strAppPath & "\Archives.SDF")

    strDBInUse = strAppPath & "\Archive.SDF"

    'Fill DataSet; including all DataTables and data

    dsAccess = objDBStuff.getDataSetAndFill(cnnSSCE)

    '----------------------------------------------------------------------

    Follow the same steps you did to run the application. When you add one item this occurs:

    First off you notice five of the six bound controls are empty, but it says one records has been added to the data set table. Select another item and press the plus button on the navigator control.

    An invalid casting exception gets thrown: "Cannot cast DBNull.Value to type System.Int32. Please use a nullable type."

    This bothers me, because according to Microsoft both of these databases are OLEDB compliant and should function exactly alike.

    I would be interested in what will happen on newer versions of Visual Studio.

    Thank you,


    MRM256

    Thursday, March 20, 2014 7:48 PM
  • Using the OLEDB provider with .NET against SQL Server Compact is not supported or encouraged, you must use the .NET provider (System.Data.SqlServerCe.dll) and either SqlCeConnection or DbConnection objects.

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Friday, March 21, 2014 11:29 AM
    Moderator
  • Then it is not an OLEDB compliant database and should not be advertised as such.

    A business that has an old OLEDB database and for business reasons needs to update to a more powerful one. They should not have to rewrite their software when a simple tweak would suffice.

    Example: Changing the provider and database names.


    MRM256

    Friday, March 21, 2014 4:48 PM
  • Where did you see it was an oledb compliant desktop database?

    And the scenario you describe was never fulfilled with oledb..


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Friday, March 21, 2014 5:48 PM
    Moderator
  • I think I'm going to consign this question to the "Black Hole of .NET", because no one seems to know how to fix the ReadOnly issue with the SQLCE database.


    MRM256

    Friday, March 21, 2014 10:15 PM
  • I know beyond a shadow of doubt that Visual Studio 2008 does NOT create SQL Server CE databases correctly.

    Here is what I did. I went to my XP computer where I installed SQL Server 2008. Using SQL Server 2008 I created a SQL Server CE database using the EXACT same schema as the Access database that I initially wanted as the applications's data store.

    I copied the SSCE database onto a USB drive and transferred it to the project folder where my test resides. I ran the application and to my great delight it worked without getting the DBNull.Value exception error.

    I think Microsoft should take a closer look at the internal workings of the SQL Server CE creation tool(Server Explorer in VS 2008) provided with the Visual Studio IDE.

    A SW Developer must have absolute faith in their development suite. If the suite fails to provide the developer with the tools needed to get the job done and this causes the developer to spend an extraordinary amount of time trying to find solutions which should have been incorporated into the development product they purchased in the first place. This really ticks them off.

    Thanks


    MRM256


    • Edited by MRM256 Friday, March 28, 2014 6:20 PM missing word
    Friday, March 28, 2014 6:03 PM
  • Although I have never explored the path you took to get to this problem, I think I have seen this dragon before and the answer was that the primary key column and or the identity setting were not correct in the SQL db.

    The database evidently created the one record but then couldn't create a second?

    I have seen SQL server create Tables as scripted copies of existing tables in SQL that would be missing the proper credentials to perform operations similar to what you are describing. 

    So That's what I would check.  Just my two cents.


    Dave just dave, I am also justdave but it did'nt beleive me so I had to become my own descendant

    Thursday, September 25, 2014 2:30 PM