CODEWITHSUNDEEP
c#mysqlsql
Esther
Esther

Reputation: 15

Create Database and table

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%)'.

Upvotes: 0

Views: 1527

Answers (3)

Plengo
Plengo

Reputation: 97

enter image description here

  1. 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/

  2. the red highlight I see two single quotes there, should be 1 single quote

  3. 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/

Upvotes: 0

komodosp
komodosp

Reputation: 3658

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)

Upvotes: 1

Nandita
Nandita

Reputation: 3

 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%)

Upvotes: 0

Related Questions