Create app to connect a database...

[lidds]

I am trying to create an app to connect my database to SQL server automatically and create a user for my database. However when I try and run the app. it gives the following error:

My code is as follows:

    Sub Main()
        Dim connStr As String = "Data Source=(local);Initial Catalog=master;User Id=sa;Password=password;"
        Dim myConn As New SqlClient.SqlConnection(connStr)

        Dim mySQL As String = _
        "USE master" & vbCrLf _
        & "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MRCS')" & vbCrLf _
        & "EXECUTE sp_detach_db @dbname = N'MRCS',@skipchecks = N'TRUE'" & vbCrLf _
        & "EXECUTE sp_attach_db @dbname = N'MRCS', @filename1 = N'C:\Program Files\Ticodi\MRCS\Database\MRCS_Data.mdf', @filename2 = N'C:\Program Files\Ticodi\MRCS\Database\MRCS_log.ldf'" & vbCrLf _
        & "USE MRCS" & vbCrLf _
        & "IF NOT EXISTS (SELECT name FROM master.dbo.syslogins WHERE name = N'MRCSAdmin')" & vbCrLf _
        & "EXECUTE sp_addlogin @loginame = N'MRCSAdmin',@passwd = 'dragnet',@defdb = N'MRCS',@deflanguage = N'us_english'" & vbCrLf _
        & "EXECUTE sp_grantdbaccess 'MRCSAdmin','MRCSAdmin'"

        MsgBox(mySQL)

        Try
            Dim cmd As New SqlClient.SqlCommand(mySQL, myConn)

            myConn.Open()
            MsgBox("open connection")
            cmd.ExecuteNonQuery()
            MsgBox("run script")
            myConn.Close()
            MsgBox("New Database has been created!", MsgBoxStyle.Information, "Successfull")
        Catch sqlExc As SqlClient.SqlException
            MsgBox(sqlExc.ToString, MsgBoxStyle.Exclamation, "Error")
            Exit Sub
        Catch exc As Exception
        End Try
    End Sub

Could someone please give me some advise why this is not working?

Thanks in advance

Simon

 

[ss7thirty]

It looks like in your connection string you specified an initial catalog of master (i believe it was) the query that you are running is on another database it seems. The connection that you are using has no idea about the other database. Therefore set the initial catalog to the database that you wish to connect to. This should fix the problems assuming the syntax of all of those stored procedures and everything else in there is good.

But it seems that the problem is that you are not connected to the database that you are querying. Initial Catalog, i believe, refers to the database that you are opening by default.