Hello,
A quick couple of questions. If I upgrade to VS 2005 Pro from Express.
1. Will my express database application still work if i connect the mdf to a server hosted SQL 2005 Server.
2. What exactly happens (if the above is possible) if more than 1 person uses the DB Front end I have built and submit changes to the same table at the same time.
I understand that the database information is cached when you are working on it until you save it and need to know what would happen if two people open the same record, edit a field and save it at the same time. I know this isn't probable but say you have a couple of thousand people accessing the database it could be pretty clse to possible that people are editing the same record.
Is it possible in express or professional so that the database query isn't cached in memory using the standard details view or grid view so that the data is live I guess a workaround would be to lock the record in some way, but this doesn't appear to be the best solution for my situation.
Thanks in advance.
SQL Express saving
ags2886
This is how you do it. Thanks NTDS.
Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded Me.Item("VBNMDATAConnectionString") = Me.Item("NewConnection") End SubWas this post helpful
I found a link on the forums:
https://blogs.msdn.com/smartclientdata/archive/2005/07/25/443034.aspx
Basically I had a situation where I wanted to change the connectionstring that M$ store when using the funky project data sources way of coding.
All I did is added this sub to the settings.vb file (instructions how to view the setting code in included in that link above)
Private
Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded Me.Item("cnDatabase") = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Me.Item("UserSettingDatabaseLocation") End SubWhere cnDatabase is the name of the Connection you have used for your datasets, and UserSettingDatabaselocation was just a User setting that i have used, but you could set that to anything like your own XML based setting or anything.
Good Luck!
RoryODonnell
In VS2005 Prof you don't have to access your mdf's directly. All you have to do is connect to your SqlServer Express instance. Just supply the instance name (which in default is localhost), and your username and password (could also be defaulted to UseWindowsAuthentication).
On your second inquiry, if two users saves a data at the same time then the rule would be "the last one wins". The ADO.Net architecture enables the users to work on disconnected mode. That is what you mean on "cached data". A workaround would be is to give the user a refresh button, so that he could requery the database.
cheers,
Paul June A. Domag
Amol Puri
I am having trouble using the connection string in Project Settings as it is application scoped and read only. What I would like to do is take the input from a login screen (server, user, databse, password if applicable) and save it in the connection string - not possible at present as read only. If connection string scoped as User then it won't show up in the Connection property of the affected TableAdapters in the graphical interface.
The only way around this that I can see is to have a User connection string in Settings and use this to modify the TableAdapter every time a form loads - not very elegant and haven't tried it yet. Would be nice if application scoped settings could be read/write. Or maybe I am missing another way.
Thanks for any help.
GS
Raven.Liu
As a test I created 2 databases on my local machine and my application runs if I point to either of these by manually changing the connection string in the Settings tab.
However if I try and change the TableAdapters connection string by the following code:
Me.IndividualTableAdapter.Connection.ConnectionString = My.Settings.VBNMDATAConnectionString2Me.IndividualTableAdapter.FillBy(Me.VBNMDATADataSet.Individual, 1)
I get the error below:
Cannot open database "NewVBNMDATA" requested by the login. The login failed.
Login failed for user 'T4\GS'
Which begs the question - just how do you change the connected database at runtime if you have TableAdapters generated by drag and drop techniques from the Data Source window
I found this link https://blogs.msdn.com/smartclientdata/archive/2005/07/25/443034.aspx but I am not sure how to apply it to connection strings.
GS
JasonJ17
Yes, you can attach your mdf on your SQL Server 2005 instance, point to the new connection string in your Project Settings, and then remove the local database file from your project.
As for what happens if two users save data at the same time, it all depends on how the TableAdapters are configured. By default, we generate the Update and Delete commands to check if the values on the local client have changed on the server since they were pulled down to the dataset. If they have, an exception will be thrown when you call TableAdapter.Update. (If you look at the CommandText of the UpdateCommand or DeleteCommand on a TableAdapter you will see some crazy looking SQL that we use to do this check.)
You can turn this behavior off by clicking the Advanced Options button on the SQL pane of the TableAdapter wizard. That it will cause the TableAdapter to always save the last person's changes. If you want to only check to see if certain columns have changed its a more advanced scenario with our designer but you can go in and manually modify the CommandText and Parameters properties on the UpdateCommand and DeleteCommand on the TableAdapter.
Hope this helps,
Eric Gruber
Visual Basic Team
Ushanta