CODEWITHSUNDEEP
c#sql-servervisual-studio-2012
Vince
Vince

Reputation: 41

Can't create a database programmatically

I am trying to create a database through visual studios, however I can't. I get the error message: "Cannot open database "database1" requested by the login. the login failed. Login failed for user '123'" However if I manually create "database1" in SQL server studios and then try to connect to it via c# code I can connect to the database fine. I have created a user with the id 123 and password abc in server studios with every server role checkbox ticked. I am using SQL Express 2012.

private void sQLToolStripMenuItem_Click(object sender, EventArgs e)
    {
        bool create_db = false;
        bool create_table = false;


        SqlConnection myConnection = new SqlConnection("Server = localhost; Database = database1; User Id = 123; Password = abc;");
        try
        {

            myConnection.Open();
            MessageBox.Show("We were able to connect to the database!", "Success!");
            create_table = true;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            create_db = true;
            MessageBox.Show("There is no database. We will create one.", "No Database Found.");
        }

        // For creating the database if not found
        if (create_db == true)
        {
            String str;
            SqlConnection myConn = new SqlConnection("Server = localhost; Database = database1; User Id = 123; Password = abc;");

            str = "CREATE DATABASE database1";

            SqlCommand myCommand = new SqlCommand(str, myConn);
            try
            {
                myConn.Open();
                myCommand.ExecuteNonQuery();
                MessageBox.Show("DataBase is Created Successfully", "Creation", MessageBoxButtons.OK, MessageBoxIcon.Information);
                create_db = false;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
        }

Upvotes: 0

Views: 1318

Answers (2)

Siva G
Siva G

Reputation: 98

        String str;
        SqlConnection myConn = new SqlConnection("Server=localhost;User Id = 123; Password = abc;database=master");

        str = "CREATE DATABASE database1";

        SqlCommand myCommand = new SqlCommand(str, myConn);
        try
        {
            myConn.Open();
            myCommand.ExecuteNonQuery();
            MessageBox.Show("DataBase is Created Successfully", "Creation", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }
        catch (System.Exception ex)
        {
            MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        finally
        {
            if (myConn.State == ConnectionState.Open)
            {
                myConn.Close();
            }
        }

Please try this above code. Its working for me.Don't give database name before you create the database. Use database master for initialize the database connection.

Upvotes: 0

mostafa8026
mostafa8026

Reputation: 313

based on comments: if you are attempting to create a database programmatically, you can't use a connection to an unborn database, so in this situation you must open a connection to master database, something like that:

"Server = localhost; Database = master; User Id = 123; Password = abc;"

Upvotes: 2

Related Questions