CODEWITHSUNDEEP
c#sql
Deparli
Deparli

Reputation: 33

Creating Database Table not work -> exist, cannot opened

I have a simple Program with two Buttons, that can create a New Database and a new Table.

I can create the Database, but I can't then create a new Table, and I dont know why.

Here I can create with this code a new Database

string DB_PATH = "C:\\Databases\\";
string database = form1.textBox5.Text;
string DB_NAME = database;
string sqlCreateDBQuery;
SqlConnection myConn = new SqlConnection("Server=DESKTOP-17BECDU;Integrated security=SSPI;database=master");

sqlCreateDBQuery = 
  " CREATE DATABASE "
  + DB_NAME
  + " ON PRIMARY "
  + " (NAME = " + DB_NAME + "_Data, "
  + " FILENAME = '" + DB_PATH + DB_NAME + ".mdf', "
  + " SIZE = 20MB,"
  + " FILEGROWTH = 100%) "
  + " LOG ON (NAME =" + DB_NAME + "_Log, "
  + " FILENAME = '" + DB_PATH + DB_NAME + "Log.ldf', "
  + " SIZE = 10MB, "
  + " FILEGROWTH = 100%) ";

SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, myConn);
try
{
    myConn.Open();
    myCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
    AppendTextBox(Convert.ToString(ex));
}
finally
{
    if (myConn.State == ConnectionState.Open)
    {
        myConn.Close();
    }
    myConn.Dispose();
}

And with this Code, I want to crate a new Table, but I get an Error...

string databasename = form1.textBox5.Text;
string str = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\\Databases\\" + databasename + ".mdf;Integrated Security=True;MultipleActiveResultSets=True;Connect Timeout=30;";

using (SqlConnection myConn = new SqlConnection(str))
{
    myConn.Open();

    SqlCommand myCommand = new SqlCommand("CREATE TABLE [dbo].[" + databasename + "]("
    + "[IDTest] INT IDENTITY (1, 1) NOT NULL,"
    + "[Test] NVARCHAR (10) NULL,"
    + "CONSTRAINT [PK_" + databasename + "] PRIMARY KEY CLUSTERED ([IDTest] ASC))", myConn);
    {
        myCommand.ExecuteNonQuery();
    }
    myConn.Close();
}

Error Output

System.Data.SqlClient.SqlException (0x80131904): An attempt to attach an auto-named database for file C:\Databases\test12.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

fixxed with ->

string str = @"Data Source=DESKTOP-17BECDU;Initial Catalog=" + databasename + ";Trusted_Connection=True;Integrated Security=True;MultipleActiveResultSets=True;Connect Timeout=30;";

Upvotes: 1

Views: 107

Answers (1)

Mohammad
Mohammad

Reputation: 1577

in your first query your creating and attaching that db , there is no need to attach it again in the create table query :

  string str = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\\Databases\\" + databasename + ".mdf;Integrated Security=True;

you can just use :

    string database = form1.textBox5.Text;
    string str= @"Data Source=DESKTOP-17BECDU;Initial Catalog="+database+"Integrated Security=SSPI;MultipleActiveResultSets=True;Connect Timeout=30;";

Upvotes: 2

Related Questions