CODEWITHSUNDEEP
c#sql-server
Doe Jowns
Doe Jowns

Reputation: 302

C# : Table not created in SQL Server

I'm a newbie in SQL Server, currently trying to create a database with tables inside. Here is the code for the database creation.

if not exists(select * from sys.databases where name = 'TCPDUMP') 
    CREATE DATABASE TCPDUMP 
         ON PRIMARY (NAME = TCPDUMP, 
                     FILENAME = 'P:\Visual Studio 2015\Projects\Serene5\Serene5\Serene5.Web\App_Data\TCPDUMP.mdf', 
                     SIZE = 2MB, MAXSIZE = 10GB, FILEGROWTH = 10%) 
         LOG ON (NAME = TCPDUMP_Log, 
                 FILENAME = 'P:\Visual Studio 2015\Projects\Serene5\Serene5\Serene5.Web\App_Data\TCPDUMP.ldf', 
                 SIZE = 1MB, MAXSIZE = 5GB, FILEGROWTH = 10%)

And the code of the table.

if not exists(select * from sys.tables where name = sample2Prot) 
    CREATE TABLE [TCPDUMP].[dbo].[sample2Prot]
    (
        [IdTransmission] INT IDENTITY(1, 1) NOT NULL,
        [timestp]  NVARCHAR(32) NULL,
        [idq] NVARCHAR(32) NULL,
        [idz] NVARCHAR(32) NULL,
        [prot] NVARCHAR(32) NULL,
        [Lhowmany] NVARCHAR(32) NULL,
        [Rhowmany] NVARCHAR(32) NULL,

        CONSTRAINT[PK_TCPDump] 
            PRIMARY KEY CLUSTERED([IdTransmission] ASC)
    )

However, when I do 

SELECT * FROM [TCPDUMP].[dbo].[sample2Prot]

an error occurs :

System.Data.SqlClient.SqlException : Invalid object name 'sample2Prot'

I suppose I made a mistake in my SQL code, but I can't figure out where and why. Has anyone an idea ?

EDIT: I adapted my code to your comments (inverted the [sample2Prot].[dbo].[TCPDUMP], corrected the parenthesis..) but it still doesn't work

Upvotes: 0

Views: 675

Answers (2)

Dennis Larisch
Dennis Larisch

Reputation: 573

You should check your syntax in your CREATE TABLE command. It should look like this: 

if not exists(select * from sys.tables where name = sample2Prot) 
    CREATE TABLE [TCPDUMP].[dbo].[sample2Prot]

Check out more syntax here if you use a database shema MSDN

Then you may need to change your SELECT statement to:

SELECT * FROM [TCPDUMP].[dbo].[sample2Prot]

Upvotes: 1

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

From quick view, seems that the order of database & table name is reversed there:

CREATE TABLE [sample2Prot].[TCPDUMP]

The correct naming order to create a table is [database name].[schema name].[table name] as shown below (dbo is default database object schema used by SQL Server):

IF NOT EXISTS (SELECT * from sys.tables where name = sample2Prot) 
    CREATE TABLE [TCPDUMP].[dbo].[sample2Prot] 
    (
        [IdTransmission] INT IDENTITY(1, 1) NOT NULL,
        [timestp]  NVARCHAR(32) NULL,
        [idq]  NVARCHAR(32) NULL,
        [idz]  NVARCHAR(32) NULL,
        [prot]  NVARCHAR(32) NULL,
        [Lhowmany]  NVARCHAR(32) NULL,
        [Rhowmany]  NVARCHAR(32) NULL, 
        CONSTRAINT [PK_TCPDump] 
        PRIMARY KEY CLUSTERED ([IdTransmission] ASC)
    )

Or using USE [database_name] is much simpler:

USE [TCPDUMP]

-- CREATE TABLE sample2Prot may used directly if dbo schema used
CREATE TABLE [dbo].[sample2Prot]
(
    -- column & constraint declarations
)

The SELECT statement can be configured as below:

USE [TCPDUMP]

SELECT * FROM sample2Prot

-- or

SELECT * FROM [TCPDUMP].[dbo].[sample2Prot]

Upvotes: 2

Related Questions