CODEWITHSUNDEEP
c#sql-servervisual-studio-2010
Richard
Richard

Reputation: 1

Can't Create The Same Table Twice in SQL Server

i have a C# Windows Form Application. I push a button it should create a Table and insert a value (int). I create the initial database as a Service-Database (Add New Item > Data > Service-Database).

The code for the button is (output follows below it): 

private void button1_Click(object sender, EventArgs e)
{
    SqlConnection thisConnection = new SqlConnection(Properties.Settings.Default.Database1ConnectionString);
    SqlCommand nonqueryCommand = thisConnection.CreateCommand();

    try
    {
        thisConnection.Open();

        nonqueryCommand.CommandText = "CREATE TABLE MyTable1 (intColumn int)";
        Console.WriteLine(nonqueryCommand.CommandText);
        Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());

        nonqueryCommand.CommandText = "INSERT INTO MyTable1 VALUES (99)";
        Console.WriteLine(nonqueryCommand.CommandText);
        Console.WriteLine("Number of Rows Affected is: {0}",
           nonqueryCommand.ExecuteNonQuery());
    }
    catch (SqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    finally
    {
        thisConnection.Close();  // close connection
        Console.WriteLine("Connection Closed.");
    }
}

OUTPUT: CREATE TABLE MyTable1 (intColumn int) Number of Rows Affected is: -1 INSERT INTO MyTable1 VALUES (99) Number of Rows Affected is: 1 Connection Closed.

Nothing Shows up on Server Explorer Though No additional Tables even if I close it down and reconnect.

If i push the button to make it issue the same again i get:

System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'MyTable1' in the database.

but still nothing on server explorer.

Upvotes: 0

Views: 1428

Answers (3)

KV Prajapati
KV Prajapati

Reputation: 94645

Nothing Shows up on Server Explorer Though No additional Tables even if I close it down and reconnect.

Whenever you execute your program, Visual Studio's project management automatically deploy (copy that database) .mdf database file at deployment folder Debug\Bin. I think your code uses a database which is located at Debug\Bin folder (which will be not shown in server explorer) and Server Explorer shows a database (.mdf) which is located at root of project folder and it is empty.

Upvotes: 0

PedroC88
PedroC88

Reputation: 3829

Try spceifying the commandType property of the SqlCommand = CommandType.Text

Also make sure that you are connecting to the same instance of SQL. You can get your code's by breakpointing the line after you open the connection (for it's when you know it works) and going looking for the servername.

Note that you can have multiple SQL instances in one machine... so you could be working on the right server (say localhost) and yet not be accessing the correct instance (say SQLEXPRESS instead of MSSQLSERVER).

Upvotes: -2

John Saunders
John Saunders

Reputation: 161773

The exception told you exactly what's going on. Your table already exists. You can't create it again. You need to DROP the table if it already exists.

Upvotes: 5

Related Questions