Need Help Executing SQL statement in VB

[Merovingian]

I need to execute the following SQL command from within VB:

CREATE LOGIN [rx] WITH PASSWORD=N'rx', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

I've been trying to find info for days but everything always deals with updating tables etc

If someone could show me the connection string and have to execute the statement that would be great!!

[Teme64]

You didn't mention which DB you're using.

For help with connection strings, check The connection string reference. After that you may succeed in creating a new login.

[Merovingian]

Ok this is my script:

sqlservername = Tservername.Text
        Dim SQLStr As String
        Dim ConnString As String
        ConnString = "Data Source=" + System.Environment.MachineName + "\" + sqlservername + ";Initial Catalog=Master;User ID=sa;Password=rx"

        SQLStr = "CREATE LOGIN [rx] WITH PASSWORD=N'rx4', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF"
        SQLStr = "EXEC sys.sp_addsrvrolemember @loginame = N'rx', @rolename = N'sysadmin'"
        SQLStr = "ALTER LOGIN [rx] ENABLE"
        SQLStr = "CREATE DATABASE [RxMa] ON  PRIMARY ( NAME = N'RxMa', FILENAME = N'C:\SQL\RxMa.mdf' , SIZE = 3000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'RxMa_log', FILENAME = N'C:\SQL\RxMa_log.ldf' , SIZE = 1024KB , MAXSIZE = 300GB , FILEGROWTH = 10%)"
        SQLStr = "ALTER DATABASE [RxMa] SET COMPATIBILITY_LEVEL = 100"

        Dim SQLConn As New SqlConnection() 'The SQL Connection
        Dim SQLCmd As New SqlCommand() 'The SQL Command

        SQLConn.ConnectionString = ConnString 'Set the Connection String
        SQLConn.Open() 'Open the connection
        Try
            SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command
            SQLCmd.CommandText = SQLStr 'Sets the SQL String

            SQLCmd.ExecuteNonQuery() 'Executes SQL Commands Non-Querys only
            Ldbcomplete.Text = "DB and Users Successfully Setup"
            Ldbcomplete.ForeColor = Color.DarkGreen
            Ldbcomplete.Visible = True
        Catch ex As Exception

            MessageBox.Show("Something Went Wrong, Check SQL Server Name", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
        SQLConn.Close() 'Close the connection

It connects just fine but it only executes the first SQLStr then errors out. Any ideas?

[Teme64]

You replace SQLstr variable when you build the command. You should append commands to previous one (and a linefeed)

SQLStr = "CREATE LOGIN [rx] WITH PASSWORD=N'rx4', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF" & Environment.NewLine
SQLStr = SQLStr & "EXEC sys.sp_addsrvrolemember @loginame = N'rx', @rolename = N'sysadmin'" & Environment.NewLine
SQLStr = SQLStr & "ALTER LOGIN [rx] ENABLE" & Environment.NewLine
SQLStr = SQLStr & "CREATE DATABASE [RxMa] ON  PRIMARY ( NAME = N'RxMa', FILENAME = N'C:\SQL\RxMa.mdf' , SIZE = 3000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'RxMa_log', FILENAME = N'C:\SQL\RxMa_log.ldf' , SIZE = 1024KB , MAXSIZE = 300GB , FILEGROWTH = 10%)" & Environment.NewLine
SQLStr = SQLStr & "ALTER DATABASE [RxMa] SET COMPATIBILITY_LEVEL = 100"

[Merovingian]

Your a life saver!!! thank you so much!

[Merovingian]

I have one more question.

The SQL script above has variables that change depending on system name, installed dir etc. The rest of the SQL I need to pass never changes, I have a txt file dbsetup.sql that contains the other SQL statements.

Now I think what I need to do is read them in line by line and append them to SQLStr, I'm not sure on my syntax.

Should it be:

'Pulls text out of dbsetup.sql 
        Dim origsql_NAME As String = System.Environment.CurrentDirectory + "\setupscripts\templates\dbuser.sql"
        Dim TextLine As String

        If System.IO.File.Exists(origsql_NAME) = True Then
            Dim objReader As New System.IO.StreamReader(origsql_NAME)
            Do While objReader.Peek() <> -1
                SQLStr = SQLStr & objReader.ReadLine() & vbNewLine
            Loop
  
            objReader.Close()
        Else
            MsgBox("dbuser.sql Doesn't Exist at" + System.Environment.CurrentDirectory + "\SQL\setupscripts\templates")
        End If

[Teme64]

VB.NET has a very usefull namespace called My. You can access, for example, computer and filesystem easily with it

Dim origsql_NAME As String = System.Environment.CurrentDirectory & "\setupscripts\templates\dbuser.sql"

If My.Computer.FileSystem.FileExists(origsql_NAME) Then
  SQLStr = My.Computer.FileSystem.ReadAllText(origsql_NAME)
Else
  MessageBox.Show("dbuser.sql Doesn't Exist at " & System.Environment.CurrentDirectory & "\SQL\setupscripts\templates", "Script Not Found", MessageBoxButtons.OK, MessageBoxIcon.Warning)
End If