[RESOLVED] sql script for attaching db in deployment project vb.net2005

[yousufkhan]

I am doing a deployment project in that i am calling a sql script to attach sql db by making customaction
i m gettting a erro Incorredt systex near 'Filneme' where i m writing wrong syntax in the following functions

 Private Sub ExecuteSql(ByVal databasename As String, ByVal Sql As String)
        Dim command As New SqlClient.SqlCommand(Sql, masterconnection)
        'Initialize the conncetion, open it and set it to the master db
        masterconnection.ConnectionString = My.Settings.masterConnectionString
        command.Connection.Open()
        command.Connection.ChangeDatabase(databasename)
        Try
            command.ExecuteNonQuery()
        Finally
            'closing the connection shuld be done in a finally block
            command.Connection.Close()
        End Try

    End Sub
    Protected Sub AddDBTable(ByVal strDBName As String)
        Try

            'Attaching database 

            'Creates the batabase

            ExecuteSql("master", "CREATE DATABASE " & strDBName & "ON ( FILENAME=" & My.Application.Info.DirectoryPath & "\Data\HospData.mdf ) FOR ATTACH")

            'Creates the tables
            ExecuteSql(strDBName, GetSql("sql.txt"))

        Catch ex As Exception
            'reports any erros and abort
            MsgBox("In execption handler create database: " & ex.Message)
            Throw ex

        End Try
    End Sub
  Public Overrides Sub Install(ByVal stateSaver As System.Collections.IDictionary)
        MyBase.Install(stateSaver)
        'AddDBTable(Me.Context.Parameters.Item("dbname"))
        AddDBTable("HospData")

    End Sub

[jmcilhinney]

Have you actually looked at the String you're creating? I'll wager not. It never ceases to amaze me that people get errors due to their data but they don't actually look at the data. This is also an example of why it's a good idea to use String.Format rather than multiple & operators: it improves readability. Try displaying that second argument to ExecuteSql and see if it is what you think it is.

[yousufkhan]

yes i know my syntax is wrong thats why I am seeking help could you please help me out in writing the correct syntax to attach the database

[jmcilhinney]

Have you actually done what I said? If so then you should be able to show us what value you are actually passing to ExecuteSql in that second argument.

[yousufkhan]

i dont no how to use string.format i tried but i fail to do it properly i want to attach a database from the specific path for which i am trying this functions

[jmcilhinney]

For the last time, please follow my SIMPLE instruction and display the actual String you're creating. That takes about 5 seconds.

MessageBox.Show("CREATE DATABASE " & strDBName & "ON ( FILENAME=" & My.Application.Info.DirectoryPath & "\Data\HospData.mdf ) FOR ATTACH")

If you actually look at the data you're using, as I suggested in the first place, it should be quite obvious where the problem is.

[yousufkhan]

I have now changed the syntax now i am copying the data files in the folder and trying to attach it,
now it giving the message database already exist

ExecuteSql("master", String.Format("CREATE DATABASE HospData ON (FILENAME='D:\MI Clinic\Data\Hospdata.mdf' )FOR ATTACH"))

[yousufkhan]

I have now changed the syntax now i am copying the data files in the folder and trying to attach it,
now it giving the message database already exist

ExecuteSql("master", String.Format("CREATE DATABASE HospData ON (FILENAME='D:\MI Clinic\Data\Hospdata.mdf' )FOR ATTACH"))

[jmcilhinney]

You haven't actually changed the syntax of the SQL code. You've simply inserted the space between the database name and and the keyword ON that was missing in the first place. The fact that you are now using a single String literal rather than using string concatenation as you were before makes me worry that you never did actually do what I suggested and see the obvious error and realise that all you had to do was change this:

ExecuteSql("master", "CREATE DATABASE " & strDBName & "ON ( FILENAME=" & My.Application.Info.DirectoryPath & "\Data\HospData.mdf ) FOR ATTACH")

to this:

ExecuteSql("master", "CREATE DATABASE " & strDBName & " ON ( FILENAME=" & My.Application.Info.DirectoryPath & "\Data\HospData.mdf ) FOR ATTACH")

I could have told you that from the start but then you could have seen that from the start yourself simply by looking at the String.

Regardless, if the error message says that the database already exists then I think the issue should be fairly obvious, i.e. the database already exists. I'm not sure of all the exact circumstances under which that error would result but logic suggests that either that data file is already attached to that instance or that instance already has a database with that name. The most likely explanation is that you've simply executed that line of code twice, meaning that, on the second attempt, that data file is already attached as a database with that name, i.e. both at the same time. That means that, if you want to retest that code after executing it successfully once before then you must detach the database first, either using code or manually.

[yousufkhan]

Yes the database was already exist on the on which i was trying the setup hence there was no sql management tool so i was not able to understand or check
I tried on another machine and it was successful thanks for your help