• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

Flip Between Local MDF & Server MDF

I am using the following code to override the current connection string in my application:

I have attached the DHFDB to the local SQL server.

Before I fill a tableadapter I call ConnectDB().  See code below.

If the DB is local it attaches fine and the data will load, but if it is network connected, the data does not load (no errors, just an empty dataset).

Any help?

Thanks!
Public Sub ConnectDB()
        If IsNetworkDrive() Then
            My.Settings.SetUserOverride("DHFDBConnectionString", "Data Source=" & My.Settings.SQLServer & ";Initial Catalog=DHFDB;Integrated Security=True;User Instance=True")
        Else
            My.Settings.SetUserOverride("DHFDBConnectionString", "Data Source=.\SQLEXPRESS;AttachDbFilename=" & My.Settings.DBLocation & ";Integrated Security=True;User Instance=True")
        End If

    End Sub

Namespace My
    Partial Friend NotInheritable Class MySettings
        Inherits Global.System.Configuration.ApplicationSettingsBase

        Private Shared userOverrides() As String = { _
            "DHFDBConnectionString"}

        Private Shared userOverrideSuffix As String = "UserOverride"

        Public Sub SetUserOverride(ByVal [property] As String, ByVal value As String)
            Me([property]) = value
        End Sub

        Private Sub userOverride_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) _
            Handles Me.SettingsLoaded
            Dim userProperty As String
            For Each appProperty As String In userOverrides
                userProperty = appProperty & userOverrideSuffix
                If CType(Me(userProperty), String).Length > 0 Then
                    Me(appProperty) = Me(userProperty)
                End If
            Next
        End Sub

        Private Sub userOverride_SettingsSaving(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) _
            Handles Me.SettingsSaving
            Dim userProperty As String
            For Each appProperty As String In userOverrides
                userProperty = appProperty & userOverrideSuffix
                Me(appProperty) = Me(userProperty)
            Next
        End Sub
    End Class
End Namespace

Open in new window

0
vwalla
Asked:
vwalla
  • 8
  • 6
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> If the DB is local it attaches fine and the data will load, but if it is network connected

You need to have enabled Remote connections enabled on the server you are trying to connect over the network to get it fixed.
Steps to enable remote connections here:
http://support.microsoft.com/kb/914277

Make sure that your Firewall and Ports are configured properly to get it work.
0
 
vwallaAuthor Commented:
To clarify, the database is not remote, it is on a network drive.  But, the debugging I am doing is local using my local instance of SQL server.

I hvae other apps that are working fine using SQL Server on this same machine.

Even with remnote connections enabled, it is still not working.

Thanks
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> To clarify, the database is not remote, it is on a network drive.  

If it is present in Network drive, you should provide the UNC path of the network drive like

\\remoteservername\sharename\foldername

instead of a mapped network drive like Z:\foldername

Also make sure that the user/login under which you are performing this action has access to the network path.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
vwallaAuthor Commented:
Either way, it is not connecting.

The DB is on the MSSQL\Data folder on the server and the local copy is in C:\...

If I change the connection string manually in the DataSet Designer to the "Server Mode" so to speak, it works fine, but this is at design time.  I need to specify the connection string at runtime based on whether users want to share the database on the network or just use a copy locally.

Thanks.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Are you sure whether the MDF and LDF file present in MSSQL\Data folder is not attached to any other instance of SQL Server.
If attached, you might receive errors while trying to attach.
0
 
vwallaAuthor Commented:
I am sure. No errors and it works if I change the connection in the faraday designer.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> No errors and it works if I change the connection in the faraday designer.

So, if you try to do it programmatic you are receiving errors right..
Then you need to create your connection string dynamically since you are passing values from user..
0
 
vwallaAuthor Commented:
No errors when it is created dynamically, just an empty dataset.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> just an empty dataset.

Is it correctly prepared..
I think this is the reason why it isn't working for Shared drives unless otherwise you change it from designer.
0
 
vwallaAuthor Commented:
>>Is it correctly prepared..

I am not sure I understand what you mean?  I think I will have to have 2 sep datasets and based on if it "isNetwork", fill the datatables accordingly on each instance of the tableadpaters fill.

For example:
If IsNetworked then
   'Fill Server Dataset Here
else
   'Fill Local Dataset Here
End if

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> I am not sure I understand what you mean?

I meant / asked you to ensure that your server properties / connection credentials are passed correctly.
Otherwise, Try debugging your application and see where exactly your server misses passing your credentials or server names correctly.
0
 
vwallaAuthor Commented:
Everything gets passed ok.  Gonna have to keep tweaking it I guess.
0
 
vwallaAuthor Commented:
I am stupid.  It works fine the first way it was done.  The problem was in that the server database schema was not eaxcatly the same in one of the tables I was pulling.  I will award full points for your help.

Thanks again!
0
 
vwallaAuthor Commented:
Thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now