CREATE DATABASE parameter for FROM master..sysdatabases

luccingolo

Hi all I'm experiencing troubles with the code from this site. Which parameter is exspected instead of:"master..sysdatabases" in the following sql-command: Dim mySQL As String = _ "IF EXISTS (" & _ "SELECT * " & _ "FROM master..sysdatabases " & _ "WHERE Name = 'MyDatabase')" & vbCrLf & _ "DROP DATABASE MyDatabase " & vbCrLf & _ "CREATE DATABASE MyDatabase " I'm using sql server 2008 express Thanks, Lucchingolo

Luc Pattyn

please don't post the same question in more than one forum; if some people were to answer here, and some elsewhere, it would become very messy. See the forum guidelines. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read formatted code with indentation, so please use PRE tags for code snippets.

I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).

Dave Kreskowiak

So what's the error message? Your statement of "Which parameter is exspected instead of:"master..sysdatabases" doesn't make sense. What do you want this to do?

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

luccingolo

Hi Luc, i must admit that i made a few mistakes. I'm sorry. When (in the General Database forum) i have been suggested to chage to the VS-forum i did not think of the consequences. I was too much focused on my little problem. And regarding the "formatted code", you are right of course. I promise i will do it better the next time. As english is not my native language, i must admit that i struggle with two of your expressions. 1. What is Q&A? 2. What do you mean by "real forum"? thanks a lot, Lucchingolo

Luc Pattyn

There is a Questions&Answers section on this site; it is rather new, and does not support discussions, it only serves simple questions and answers. Real forums, like this one, as well as the personal profile page, allow for back-and-forth conversations and discussions, so the issues can get an in-depth treatment when required. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read formatted code with indentation, so please use PRE tags for code snippets.

I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).

luccingolo

Hi Dave, please don't ask about the error message. It is sooo long.(i think that most sql-messages are quite long) ;) So far the Try/Catch bloc tells me it's a "SqlException" My idea was to check if a certain .mdf-file (in this case: MyDatabase.mdf) already exists. If so, it should be deleted (dpopped) to allow the creation of that file. (Of course that makes no sense, it's only for training purpose) I want to learn how to dynamically create a sql-DB with a few tables and its stored procedures. And of course i can not create a new DB(the same one) each time i lounch my application. But in the meantime i think it should be possible to solve this problem with "pure" VB instead of a SQL-command. I will try if i can not solve it with the File.Exists method of the System.IO-namespace. Should work. I hope you have undestood my intentions now. Of course if you have a suggestion or a better idea, i'm interested. Anyhow, in the case "File.Exists" would not work, i will post a new question in this forum. Thanks again for your answer, Lucchingolo

luccingolo

Tanks, everything is clear now.:thumbsup:

Dave Kreskowiak

Well, the code you found only works with actual SQL Servers. It does not work with Access at all. Access has a completely different method for creating a new database.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

luccingolo

Hi Dave, i'm sorry but either you badly misunderstood my question or my english es even worse than i thought. I AM USING SQL 2008 EXPRESS EDITION, nevertheless the code is not working. The best i could get until now is an execution without an error. But in the end, i can't find any "new" (created) .mdf-file on my harddisk??? :confused: Here comes the code.

Imports System.Data.SqlClient
Public Class Form1
Private MyConnection As String

Private Sub Form1\_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    MyConnection = "Integrated Security=SSPI;" + "Initial Catalog=;" + "Data Source=.\\SQLEXPRESS;" 'mydb

    Dim mySQL As String = "IF EXISTS (SELECT \* FROM sys.databases WHERE name = 'MyDatabase')" & \_
        "DROP DATABASE \[MyDatabase\];CREATE DATABASE \[MyDatabase\]"

    Try
        Dim conn As New SqlConnection(MyConnection)
        Dim myCmd As New SqlCommand(mySQL, conn)

        ' Open the connection, execute the command, and close the connection. It is more efficient to 
        '  ExecuteNonQuery when data is not being returned.

        If conn.State <> ConnectionState.Open Then conn.Open()

        myCmd.ExecuteNonQuery()
        conn.Close()

        ' Data has been successfully submitted

        MessageBox.Show("New Database has been ' successfully created !", \_
                        " Database Creation Completed", MessageBoxButtons.OK, \_
                        MessageBoxIcon.Information)
    Catch sqlExc As SqlException
        MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", \_
        MessageBoxButtons.OK, MessageBoxIcon.Error)

    Catch exc As Exception
        'If mstrConn = MyConnection Then
        '    ' Couldn't connect to SQL Server.  Now try MSDE.
        '    mstrConn = MY\_MSDE\_CONNECTION\_STRING
        '    'Connecting to MSDE
        'Else
        '    ' Unable to connect to SQL Server or MSDE
        '    MessageBox.Show("SQL Or MSDE Connection Failed", \_
        '         "Connection Failed!", MessageBoxButtons.OK, MessageBoxIcon.Error)
        '    End
        'End If
    End Try
End Sub

End Class

Any idea of what's wrong here. Thank you in anticipation, Lucchingolo