Why does code not enter IF block?

Question

I am using the given code to copy my database files...it works like a charm in debug mode but as soon as I create a setup, it stops working. The error is

"Database Detach Failed"

I tried checking the code line by line and found that the code does not enter the IF block.

I have no idea why.

Public Sub bk()
  Try
    Dim strDatabasePath As String = My.Computer.FileSystem.CombinePath(My.Application.Info.DirectoryPath, "LIC.mdf")
    Dim strdbLogPath As String = My.Computer.FileSystem.CombinePath(My.Application.Info.DirectoryPath, "LIC_log.ldf")

    ''# Dim strDatabasePath As String = My.Computer.FileSystem.CombinePath(Application.UserAppDataPath, "LIC.mdf")
    ''# Dim strdbLogPath As String = My.Computer.FileSystem.CombinePath(Application.UserAppDataPath, "LIC_log.ldf")

    MsgBox(Application.UserAppDataPath)
    ''# DB.Connection can be any valid SQLConnection which you might already be using in your application
    Dim con As New SqlClient.SqlConnection(LIC.My.Settings.LICConnectionString)
    Dim srvCon As New ServerConnection(con)
    Dim srv As Server = New Server(srvCon)
    MsgBox(srv.ToString)
    If srv.Databases.Contains(strDatabasePath) Then
      MsgBox("In If")
      If con.State = ConnectionState.Open Then
        MsgBox(con.State)
        con.Close()
      End If
      MsgBox(con.State & " Is It True?")
      srv.KillAllProcesses(My.Computer.FileSystem.CombinePath(My.Application.Info.DirectoryPath, "LIC.mdf"))
      srv.DetachDatabase(strDatabasePath, True)
      My.Computer.FileSystem.CopyFile(strDatabasePath, "c:\backup\LIC.mdf", True)    
      My.Computer.FileSystem.CopyFile(strdbLogPath, "c:\backup\LIC_log.ldf", True)    
      MessageBox.Show("Backup taken successfully")
    End If    
    srvCon.Disconnect()
    con.Open()
  Catch ex As Exception
    MessageBox.Show("Error Occured : " & ex.Message)    
  End Try
End Sub

thanks...can u throw some light on "why the down vote and any solution to my prob"?? :P — user1150440, Jan 15 '12 at 16:45
it gives your average n00b a power trip and a sense of accomplishment in their otherwise uneventful life — Green Day, Jan 15 '12 at 16:46
try printing the value of "strDatabasePath" just before the if statement and make sure it contains what you expect — Green Day, Jan 15 '12 at 16:47
i tried it ...it points to the .mdf file..but does not enter the IF block — user1150440, Jan 15 '12 at 16:51
as Nelson has already mentioned, print the srv.Databases list and inspect it's content — Green Day, Jan 15 '12 at 16:55
`C:\Users\mono\AppData\Local\Apps\2.0\Data\J17BC107.O4P\Y8KA1TGQ.51R\lic...tion_4d5f0e541bcb5349_0001.0000_a2ca4a03febcb75f\Data\Lic.mdf` This is what i get from `strDatabasePath` — user1150440, Jan 15 '12 at 17:05

competent_tech · Accepted Answer · 2012-01-15T21:54:46.630

2

I think the primary problem with your code is that you are mixing concepts.

The name of the database is completely different than the path to the file(s) that SQL Server uses to store the contents of the database.

When you are performing operations on the Databases collection and the Server, the operations such as Contains and DetachDatabase expect the name of the database, not the path to the files.

You can obtain the name of the Database from the SqlClient connection (in the Database property) and you can obtain the name of the database files from the Server object using the MasterDBPath and MasterDBLogPath properties.

This makes your code much cleaner and not dependent on the files being stored in specific locations.

Public Sub bk()
    Try
        ''# DB.Connection can be any valid SQLConnection which you might already be using in your application
        Using con As New SqlClient.SqlConnection(LIC.My.Settings.LICConnectionString)
            Dim sDatabaseName As String

            con.Open()

            sDatabaseName = con.Database

            con.Close()

            Dim srvCon As New ServerConnection(con)
            Dim srv As Server = New Server(srvCon)

            If srv.Databases.Contains(sDatabaseName) Then
                Dim oDatabase As Database
                Dim cFiles As New List(Of String)

                ' Get a local reference to the database
                oDatabase = srv.Databases(sDatabaseName)

                ' Collect the list of database files associated with this database
                For Each oFileGroup As FileGroup In oDatabase.FileGroups
                    For Each oFile As DataFile In oFileGroup.Files
                        cFiles.Add(oFile.FileName)
                    Next
                Next

                ' And collect the list of log files associated with this database
                For Each oFile As LogFile In oDatabase.LogFiles
                    cFiles.Add(oFile.FileName)
                Next

                ' Ensure nothing is using the database
                srv.KillAllProcesses(sDatabaseName)

                ' Detach the database
                srv.DetachDatabase(sDatabaseName, False)

                ' And finally, copy all of the files identified above to the backup directory
                For Each sFileName As String In cFiles
                    System.IO.File.Copy(sFileName, System.IO.Path.Combine("c:\backup\", System.IO.Path.GetFileName(sFileName)), True)
                Next

                MessageBox.Show("Backup taken successfully")
            End If
            srvCon.Disconnect()
        End Using
    Catch ex As Exception
        MessageBox.Show("Error Occured : " & ex.Message)
    End Try
End Sub

edited Jan 15 '12 at 21:54

answered Jan 15 '12 at 20:20

competent_tech

44,465
11
90
113

i tried your code just now...no luck yet..using your code,it does not even get into the IF block while in debugging mode. – user1150440 Jan 15 '12 at 20:37
the code snippet that i am using ..works fine in debugging mode but fails after deployment...[http://www.sendspace.com/file/l08qjj](http://www.sendspace.com/file/l08qjj) i have uploaded it here...kindly have a look...the code is in InsertData.vb just below the InsertData_FormClosed sub. – user1150440 Jan 15 '12 at 20:40
this is what i am trying to do now..but i failed at this also...[http://stackoverflow.com/questions/8873226/terminate-application-using-full-path](http://stackoverflow.com/questions/8873226/terminate-application-using-full-path) – user1150440 Jan 15 '12 at 20:44
i tried to print the DataBase name using `MsgBox(con.Database.ToString)`....seems it cannot find the DataBase name...the `MsgBox` is empty. – user1150440 Jan 15 '12 at 20:49
Ah, that is a big clue! It sounds like your connection string may be missing or faulty. You could try this with a hard-coded connection string to verify that it works and then chase down the issue with the connection string (I suspect it is an issue with MySettings). I just tested the code with a hard-coded connection string and verified that it worked as desired. – competent_tech Jan 15 '12 at 21:04
Connection string is fine..if i add `con.open()` just before the if block..it stars getting the name of the DB...but as soon as the connection is closed..the name is gone and `detach fails` – user1150440 Jan 15 '12 at 21:11
if u don't mind...can u please download my project??just give it a look...[http://www.sendspace.com/file/l08qjj](http://www.sendspace.com/file/l08qjj) – user1150440 Jan 15 '12 at 21:12
I have just rewritten the answer so that the actual database and log file names are now extracted from the database prior to detaching it. – competent_tech Jan 15 '12 at 21:22
Ok, I have figured out what is going on: the serverconnection changes the database name to the master database on the connection so that it can perform the various operations. So the connection database name is only available right after the connection is opened (and the connection should NOT be closed). I have revised the answer to extract the database name from the connection right after it is opened and then use it throughout the rest of the code. I have now used this code to successfully backup a test database. – competent_tech Jan 15 '12 at 21:30
Put this ` MsgBox(con.Database)` just before this and see what happens `If srv.Databases.Contains(con.Database) Then` – user1150440 Jan 15 '12 at 21:30
can i use my connection string here `Using con As New SqlClient.SqlConnection("server=IBM02;Database=IAA;Network Library=DBMSSOCN;Integrated Security=SSPI") ` – user1150440 Jan 15 '12 at 21:33
print `sDatabaseName ` using a `msgbox` ...its still empty. – user1150440 Jan 15 '12 at 21:38
this is my connection string `Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\LIC.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True` is something wrong with my connection string?? – user1150440 Jan 15 '12 at 21:43
re can you use your connection string: yes, I have updated the answer appropriately. re databasename empty: this sounds like a problem with your connection string. Can you include the connection string so I can see what it looks like? – competent_tech Jan 15 '12 at 21:44
this is my connection string `Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\LIC.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True` is something wrong with my connection string?? – user1150440 Jan 15 '12 at 21:45
Ok, I see what the issue is now that I see your connection string: you need to open the connection, retrieve the database name, then close the connection. I have updated the answer with this info. – competent_tech Jan 15 '12 at 21:55
Friend i owe you a big time :) Thanks a Zillion Dude for your time and patience...Salute...if you don't mind can i add you in facebook or gamil??EDIT :I wont bother you much :) – user1150440 Jan 15 '12 at 22:02
You are very welcome. Unfortunately, I have not yet joined any of the social networking sites (except this one). – competent_tech Jan 15 '12 at 22:12
Hmmmm...okay..i understand..if possible drop me a mail at info@nyxsolutions.in – user1150440 Jan 15 '12 at 22:15

score 0 · Answer 2 · answered Jan 15 '12 at 16:44

0

Have you checked that the database is on this path?

My.Application.Info.DirectoryPath

As you would imagine, your code will only enter that IF in the case that srv.Databases has that path you're looking for.

Try to print the srv.Databases list to inspect what it's content is.

answered Jan 15 '12 at 16:44

Nelson Reis

4,780
9
43
61

kindly explain..i can't get what u r saying..sorry to bother. – user1150440 Jan 15 '12 at 16:47
You're not bothering at all! Set a breakpoint in the IF. Here's what I'm meaning http://blogs.interfacett.com/wp-content/uploads/2011/09/media_1316713199466.png – Nelson Reis Jan 15 '12 at 16:48
`C:\Users\mono\AppData\Local\Apps\2.0\Data\J17BC107.O4P\Y8KA1TGQ.51R\lic...tion_4d5f0e541bcb5349_0001.0000_a2ca4a03febcb75f\Data\Lic.mdf` This is what i get from `strDatabasePath` – user1150440 Jan 15 '12 at 17:07
I bet that this path don't exist on the srv.Databases list. That is why your code doesn't enter the If statement. Maybe you will need some kind of string treatment to do the check you want. – Nelson Reis Jan 15 '12 at 17:11
Is `Application.UserAppDataPath` the only way to get to the .mdf file??isn't there any other way??i mean after making an .exe...while debugging `My.Application.Info.DirectoryPath` works fine. – user1150440 Jan 15 '12 at 17:15
Does it work if you use the first 2 lines in debug mode and the other 2 lines below when you deploy it? If that's the case, you can add a condition like the one described here: http://stackoverflow.com/questions/3613249/how-to-use-if-debug-in-vb-net – Nelson Reis Jan 15 '12 at 17:37
it works with the first 2 lines in debug mode...but does not work with anything when i deploy it...alas :( – user1150440 Jan 15 '12 at 17:50
Try this one: Directory.GetParent(Assembly.GetExecutingAssembly().Location) , read it here: http://stackoverflow.com/a/218064/29544 – Nelson Reis Jan 15 '12 at 17:53
this also leads me to the same directory as `Application.UserAppDataPath`...if you dont mind can i send you the project..just give it a look. – user1150440 Jan 15 '12 at 17:58
Ok, put it on a public place where I can get it and I'll take a look at it. – Nelson Reis Jan 15 '12 at 18:16
[http://www.sendspace.com/file/l08qjj](http://www.sendspace.com/file/l08qjj) i have uploaded it here...kindly have a look...the code is in `InsertData.vb` just below the `InsertData_FormClosed` sub. – user1150440 Jan 15 '12 at 18:22
the file will be available for a limited period only..so please download it ASAP..Thanks. – user1150440 Jan 15 '12 at 18:23

Why does code not enter IF block?

2 Answers2