Create Database and table

Question

I have to create a database and afterwards create a table within the database created.

I have connected to the master on my connection string, but and I want to add the new database name I have just created within the Initial Catalog when connecting and creating the table.

This is what I have

string conn = "SERVER=BRIAN-PC\\SQLEXPRESS; Initial Catalog=master; user id =sa; Password=kagiso";

            String str = "CREATE DATABASE IF NOT EXIST PSAHoldings ON PRIMARY"
                + "(NAME = PSAHoldings_Data,"
                + "FILENAME = 'C:\\PSAHoldings.mdf'',"
                + "SIZE = 2MB, FILEGROWTH =10%)"
                + "LOG ON (NAME = PSAHoldings_Log,"
                + "FILENAME = 'C:\\PSAHoldingsLog,idf',"
                + "SIZE = 1MB,"
                + "FILEGROTH = 10%)";

SqlConnection connection = new SqlConnection(conn);
            connection.Open();

try
            {
                //SqlCommand to create database
                SqlCommand cmd = new SqlCommand(str, connection);
                cmd.ExecuteNonQuery();

                MessageBox.Show("DataBase was successfully created", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);

string connect = "Data Source=Esther-PC\\SQLEXPRESS; Initial Catalog=PSAHoldings; user id =sa; Password=kagiso";

            string table = "CREATE TABLE IF NOT EXIST t_original (" +
                "empId varChar(10) NOT NULL PRIMARY KEY," +
                "paycode varChar(10) NOT NULL," +
                "amount int NOT NULL," +
                ")";


            SqlConnection con = new SqlConnection(connect);
            con.Open();

            SqlCommand createTable = new SqlCommand(table, con);
            createTable.ExecuteNonQuery();
            }
}
            catch (SqlException sqlEx)
            {
                MessageBox.Show(sqlEx.ToString(), "Exception Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                connection.Close();
                con.Close();
            }

When I run the application I keep on getting exception errors.

The EXCEPTION ERROR reads it says System.Data.SqlClient.SqlException(0x80131904): Incorrect synax near the keyword 'IF'. expression of non-boolean type specified in a context where a condition is expected, near 'PSAHoldings', Unclosed quotation mark after the character string 'Size=1mb, filegrowth=10%)'.

With the edit: that looks like simply invalid TSQL. Since you're not adding any external inputs, it should simply be a question of: does this work in SSMS or similar tools? — Marc Gravell
– Marc Gravell, Commented Mar 31, 2015 at 10:05

komodosp · Accepted Answer · 2015-03-31 10:21:21Z

1

I don't think there's such code as CREATE DATABASE IF NOT EXIST - Can't find any mention of it on MSDN: https://msdn.microsoft.com/en-us/library/ms176061.aspx

Try this.

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'PSAHoldings')
  CREATE DATABASE PSAHoldings ON PRIMARY 
    (NAME = PSAHoldings_Data,
    FILENAME = 'C:\\PSAHoldings.mdf',
    SIZE = 3MB, FILEGROWTH =10%)
    LOG ON (NAME = PSAHoldings_Log,
    FILENAME = 'C:\\PSAHoldingsLog.idf',
    SIZE = 1MB,
    FILEGROWTH = 10%)

(Note some other mis-types fixed also changed file size to 3MB as I had an error saying it needed at least 3MB)

edited Mar 31, 2015 at 10:21

answered Mar 31, 2015 at 10:17

komodosp

3,6864 gold badges39 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MajkeloDev Over a year ago

"CREATE DATABASE IF NOT EXIST" - it's a MySql syntax :) He used MySql tag in his Question, but he is using SqlExpress - strange ...

Esther Over a year ago

I havent used MySql alot, Since I am still a learner I thought MySql and Sql have the the same syntax

Esther Over a year ago

@colmde thanks alot, the code worked like a charm, I guess I have to use the also for creating the table

komodosp Over a year ago

Yes I think it's IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = 't_original')

Nandita · Accepted Answer · 2015-03-31 10:20:59Z

0

 CREATE DATABASE  PSAHoldings ON PRIMARY
                (NAME = PSAHoldings_Data,
               FILENAME = 'C:\\PSAHoldings.mdf',
               SIZE = 2MB, FILEGROWTH =10%)
                LOG ON (NAME = PSAHoldings_Log,
               FILENAME = 'C:\\PSAHoldingsLog,idf',
                SIZE = 1MB,
                FILEGROWTH = 10%)

answered Mar 31, 2015 at 10:20

Nandita

34 bronze badges

Comments

Plengo · Accepted Answer · 2015-03-31 10:47:13Z

0

enter image description here

The first blue highlight try to search the db through system objects like this

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'PSAHoldings') /then your create db script goes here/
the red highlight I see two single quotes there, should be 1 single quote
checking if table already exists

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[bbr_categories]') AND type in (N'U'))

/your create table script goes here/

answered Mar 31, 2015 at 10:47

Plengo

972 silver badges11 bronze badges

Collectives™ on Stack Overflow

Create Database and table

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related