Database

The new v4.0 use a database with the extension .vdb4 and the previous version used .mdb. Is there a way to convert the old database or transfer the user/group/rootfolder-data into the new one? If not I have to input manually all users and give them new passwords...

The update wizard already does this, i.e. it converts the old database without losing any settings.
I guess you have not read the update instructions. I am sending you the instructions via email.

Could you send me those instructions as well?

Right now I'm trying to upgrade the databaseand it tells me "you do not have an existing/valid license (trial mode).

I then enter my v4 license key again but it's either invalid or too long (with dashes)...

Don't know what I did wrong.

Can i also get these instructions, they did not come with my purchase of the 4.0 software.

Bump, still haven't received instructions for the upgrade.

The detailed technical procedure for upgrading your current version to v4.0 is as follows:

1.     Download FileVista version 4.0.

2.      It is recommended that you make a backup copy of your current (installed) FileVista folder so that you can roll back if anything goes wrong. If you are using a SQL Server database instead of the default database FileVista\App_Data\database\FileVista.mdb, then you should make a backup copy of the SQL Server database too. If you open FileVista\App_Data\FileVista.config and see the ConnectionString setting then you can know which database you are actually using.

3.      Copy FileVista\App_Data\FileVista.config to a separate location. If you are using the default database and not a SQL Server database then copy FileVista\App_Data\database\FileVista.mdb to a separate location too. These files are important as they contain all the data specific to your installation.
Other considerations: If you had prepared a custom language file (other than included languages) and had put it in FileVista\languages subfolder than keep a copy of it too. You may need to edit your language file and check if you need to add new strings for the new version. If you modified FileVista\web.config , you may need to move your modifications to the new file that is created in step 5.

4.    Depending on your previous installation method:
•         If you previously installed FileVista via the MSI setup file than uninstall it from Add/Remove programs. If any files or subfolders are still left under the uninstalled FileVista folder, delete all.
•         If you previously installed it manually than delete all files and subfolders under your FileVista folder.

5.    Depending on the new installation method you choose:
•         Run FileVista.msi which is found in the FileVista-v4.0.zip and install the application to the same folder as your previous installation.
•         Manual: Copy all the files and subfolders from FileVista folder which is in the FileVista-v4.0.zip to your existing FileVista folder. 

6.    Put the files that you made copies of in step 3 to the same places under your newly installed FileVista folder, 
i.e. overwrite the files with the same names: FileVista\App_Data\FileVista.config and FileVista\App_Data\database\FileVista.mdb (if required).
Important: As you have deleted the existing folder structure of FileVista, the permissions may be reset. Thus you should make
sure that the executing identity (ASPNET or NETWORK SERVICE or whichever custom user you used earlier) is granted Modify permission on FileVista\App_Data folder, i.e. the same permissions that was set on the folder before.
If you are using a SQL Server database, the update wizard will require the connection user has db_owner permission on the SQL Server database to be able to change the database structure.
If the above permission requirements are not assured, the update will fail and may leave the database in a corrupted state. In this case you will need to start over the update procedure with the backup copies you made in step 2..

7.    Run the update wizard page FileVista/update.aspx directly in your browser. The wizard will automatically detect your previous version. When updating major versions (e..g. from v3.x to v4.x), you will be asked for a new license key to be able to continue the update wizard and you will be able to click a special link on the update page to see your upgrade options (you may be elligible for a free license key). After the update wizard is completed, restart your browser and open FileVista in your browser. Now you are ready for logging into the new version. In some cases, you may experience some strange display or javascript errors after the update. This is due to the your browser which still keeps old versions of the files in its cache. Please hit CTRL + F5 once on the login page and once on the main page after you login.

Thanks Cem , having this information out there is helpful ^^

I'm now having a different issue, when putting in my license in the upgrade wizard:

"error You must have a valid license to use VistaDB"

Looking at the licenses.licx file that came with the source it's empty, adding

VistaDB.VistaDBLicense,VistaDB.4

Gives me a different error at compile

Exception occured creating type "vistadb.vistadblicense, vistadb.4, version 4.0.0.0, culture=netural, publickeytoken=dfc935afe2125461 system.argumentnullexception: value cannot be null

VistaDB is a 3rd party library and it requires a development license when you compile against it.
We leave licenses.licx file empty in the source code package otherwise you will not be able compile it due to the lack of VistaDB license on the development machine. As of v4.0, source code license owners should use a SQL Server database instead of the VistaDB database if they want to build GleamTech.FileVista.dll themselves.

Note that you can do the upgrade with GleamTech.FileVista.dll built by us (found in binary distribution) however you should also continue to run the application with this DLL to able to use the upgraded VistaDB database.

If you must use your own compiled version, then you should migrate your data to SQL Server:

1. For your existing FileVista installation, import existing Access Database (.mdb file) to SQL Server with the help of Management Studio. Note that you may refer to the sql script App_Data\database\CreateSqlServerDB.sql for verifying the database structure created by the import.

2. For your existing FileVista installation, change the connection string in App_Data\FileVista.config to connect to the new SQL Server database instead of the .mdb file:

From 

  <add key="ConnectionString" value="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=~/App_Data/database/FileVista.mdb" />

To (If executing identity (network service) is allowed to access the DB)

  <add key="ConnectionString" value="Provider=SQLOLEDB; Data Source=[SQLSERVERNAME]; Initial Catalog=[DBNAME]; Integrated Security=SSPI;" />

Or to (If a SQL Server user is allowed to access the  DB)

  <add key="ConnectionString" value="Provider=SQLOLEDB; Data Source=[SQLSERVERNAME]; Initial Catalog=[DBNAME]; User Id=[USER]; Password=[PASSWORD];" />

3. Now make sure that your existing FileVista installation works properly against the imported SQL Server database.

4. Follow the upgrade steps so that the existing SQL Server database is automatically updated for v4.0.


Note that in future versions (maybe in the next version), we plan to support Microsoft's latest deployable compact database SQL CE 4.0 (maybe as the default database) so that there is no more compilation dependancy on 3rd party products.

excluded licenses.licx from project
access import to sql goes well

setting my connection string like you suggested causes update.aspx.cs to throw:
ArgumentException was unhandled
An OLE DB Provider was not specified in the ConnectionString.  An example would be, 'Provider=SQLOLEDB;'.

adding ProviderName="System.Data.SqlClient" attribute is not allowed

adding Provider=SQLOLEDB in the connection string works and launches update wizard

after i put my license in the update wizard and click next it launches a dialog box:

"Message from webpage"
Error
Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.


clicking OK brings me back to  update wizard with my license key ... clicking next again causes:
Error:
No item by the name of 'PermissionEntry' could be found in the current database, given that @itemtype was input as '(null)'

So two questions, one, what shoudl my connection string be ... two, why is the update wizard failing

Hi William,
Sorry my bad, I forgot to include the provider name in the connection string but you have already found the correct one,  it's indeed Provider=SQLOLEDB for versions before v4.0. So, there is no problem with your connection string now. 

> "Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong."

This error means you didn't verify the database structure after importing Access database to SQL Server. By verifying I mean you make sure the imported SQL Server database structure matches the one in the sql script App_Data\database\CreateSqlServerDB.sql which is found in your existing version of FileVista installation (not from v4.0). 
The possible problem is the database does not contain the foreign key constraints. 

Note that when you click "Next" in the update wizard, it's one shot. This means your database was modified partially and an error occured in the middle of the process. So, you will get different errors when you click "Next" second time. You can discard the errors after the first one because you will need to start over each time the update fails.

So you should re-import from the original Access database to a new SQL Server database and after the import execute only the "ALTER TABLE" statements found in CreateSqlServerDB.sql so that all necessary constraints are defined.

The update wizard will be successful if you prepare the correct database.

If you still have problems, I can do the import and update for you if you send me your original Access database.

Let me know.

You were exactly right, it was the foreign key constraints on the old DB (duh me ^^)

aftering running the old alter table statements and checking identity ( in some cases setting identity and making sure it was auto-incrementing) it ran perfectly.

Thanks.

Hi,

I'm doing the same thing here but I can't figure out what I'm doing wrong.

I started off with a 3.2.4.0 installation and it's database in an *.mdb file.

I created a database on an sql and imported the mdb with no problems. Then I used the "CreateSQLDatabase.sql" from the 3.2.4.0 source code and only executed the "alter" commands.

I then edited the filevista.config to use SQL with changing the Connectionstring to 
<add key="ConnectionString" value="Provider=SQLOLEDB; Data Source=192.168.200.2\sqlexpress; Initial Catalog=[NameOftheDB]; User ID=[UsernameWhoIsOwnerOfTheDB]; Password=[pwasword]" />

and edited the "UpdateCompleteString" from 1 to 0.

Then I went to the website which opened up the Update Dialogue. It detected old version "3.0 to 3.2". I enter my license key and then it throws me (roughly translated):
"Error. The Execution has been halted. Violation of UNIQUE-KEY-Restriction "IX_Configuration". A Key cannot be added to "dbo.Configuration"-Object twice."

Hi Chris,
Your database is detected as "3.0 to 3.2" where as it should be detected "3.2 to 3.5" as you said you started off with a 3.2.4.0 installation. So it seems your database structure is strangely in between versions 3.0 and 3.2 (it seems PasswordModifiedTime field is missing from User table). 

I recommend you to do the following:

1. Delete rows with keys MaximumPasswordAge and MaximumInvalidLoginAttempts from Configuration table in the existing imported SQL database so that it's fully degraded to v3.0. Note that you don't need to re-import from Access.

2. Now go to the update page and try updating again. Hopefully this time, the update should be completed without errors. If not let me know the error message.

Hi Cem,

the column "PasswordModifiedTime" DOES exist in my user table. Anyhow I went ahead and deleted it for testing.
Also deleted "MaximumPasswordAge" and "MaximumInvalidLoginAttempts" from Configuration table.

It still detects as 3.0 to 3.2, when I do the update now I get the same Error as william:
"Error
Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong."

Now you said to check the foreign key constraints and identity. What are those and how do I do that? Sorry I'm not very familiar with administrating databases. Can you give step by step instructions on that? Thank you!

Chris

Chris,
Ok this error means, your database was now updated till 3.5. Rename the table "AccessControl" back to "PermissionEntry" so that the update can continue from v3.5 next time.

You said:
> I created a database on an sql and imported the mdb with no problems. Then I used the "CreateSQLDatabase.sql" from the 3.2.4.0 source code and only executed the "alter" commands.

So I thought you created  foreign key constraints  by executing the "alter" commands. It seems they were not created.

Please make sure you executed the following queries against the FileVista database:

ALTER TABLE [dbo].[Event] WITH NOCHECK ADD 
    CONSTRAINT [PK_Event] PRIMARY KEY  CLUSTERED 
    (
        [EventID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Group] WITH NOCHECK ADD 
    CONSTRAINT [PK_Group] PRIMARY KEY  CLUSTERED 
    (
        [GroupID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[RootFolder] WITH NOCHECK ADD 
    CONSTRAINT [PK_RootFolder] PRIMARY KEY  CLUSTERED 
    (
        [RootFolderID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User] WITH NOCHECK ADD 
    CONSTRAINT [PK_User] PRIMARY KEY  CLUSTERED 
    (
        [UserID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Configuration] ADD 
    CONSTRAINT [IX_Configuration] UNIQUE  NONCLUSTERED 
    (
        [Key]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Group] ADD 
    CONSTRAINT [IX_Group] UNIQUE  NONCLUSTERED 
    (
        [Name]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[RootFolder] ADD 
    CONSTRAINT [IX_RootFolder] UNIQUE  NONCLUSTERED 
    (
        [Name]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User] ADD 
    CONSTRAINT [IX_User] UNIQUE  NONCLUSTERED 
    (
        [Name]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Membership] ADD 
    CONSTRAINT [FK_Membership_Group] FOREIGN KEY 
    (
        [GroupID]
    ) REFERENCES [dbo].[Group] (
        [GroupID]
    ),
    CONSTRAINT [FK_Membership_User] FOREIGN KEY 
    (
        [UserID]
    ) REFERENCES [dbo].[User] (
        [UserID]
    )
GO

ALTER TABLE [dbo].[PermissionEntry] ADD 
    CONSTRAINT [FK_PermissionEntry_Group] FOREIGN KEY 
    (
        [GroupID]
    ) REFERENCES [dbo].[Group] (
        [GroupID]
    ),
    CONSTRAINT [FK_PermissionEntry_RootFolder] FOREIGN KEY 
    (
        [RootFolderID]
    ) REFERENCES [dbo].[RootFolder] (
        [RootFolderID]
    ),
    CONSTRAINT [FK_PermissionEntry_User] FOREIGN KEY 
    (
        [UserID]
    ) REFERENCES [dbo].[User] (
        [UserID]
    )
GO

Then edit the following tables and set the corresponding column as Identity:

    Group -> [GroupID] 
    RootFolder -> [RootFolderID] 
    User -> [UserID] 
    Event -> [EventID] 

Finally run the update wizard again.

okay, did that. now this happens when running the wizard.

ERROR

The Object 'IX_RootFolder' is dependend from the Column Object 'Name'.
Error on ALTER TABLE ALTER COLUMN Name, because at lease one object is accessing this column.


Maybe i could send you the mdb and you see what's wrong with it? I have to use the SQL Version now because I have to make some little changes in the source and have to compile myself and cannot use the vdb.

oh, by the way, when I wanted to set the idetities they were already set so the "alter" commands must have done it.

Yes, it seems messed up so sending us the mdb file will be the best in this case. We will migrate the data, do the update and send you back the generated sql database (as script).

Just for everyone else out there: I never got it to run until Cem converted it to a SQL Database and I could import that. After that: Import to SQL Server (Express), edited the filevista.config accordingly, worked like a charm.
Thank you very much.

Chris

Actually it turned out that there was a bug in the version detection code in the update page, Chris's database was detected mistakenly as v3.0 thus the update was failing. The problem occurs only when upgrading from a v3.2.x database and there should not be any problems when updating from a version bigger than v3.2.x (like v3.5, v3.8).

If any other users having problems updating from v3.2.x to v4.0 too, please let us know.

In the next version (v4.1), the fix will be included so this update problem only applies to FileVista v4.0.