C# : Table not created in SQL Server

Question

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

Which string actually contains string parameter {0}? What is the purpose of second argument "sample2Prot" here? — Tetsuya Yamamoto
– Tetsuya Yamamoto, Commented Aug 10, 2017 at 9:05
You are creating a table with the syntax CREATE TABLE[sample2Prot].[TCPDUMP] the syntax should be [Database].[schema].[tablename]. I would check your syntax directly in SQL Server first, then plug it into C# — Keith
– Keith, Commented Aug 10, 2017 at 9:06
@DoeJowns dbo is the default schema, you can write it like [Database]..[Tablename], this will use the default schema. — Keith
– Keith, Commented Aug 10, 2017 at 9:11
@DoeJowns any errors, or the same that it did not exist when the select is run? have noticed: select * from sys.tables where name = sample2Prot should be select * from sys.tables where name = 'sample2Prot' with the table name contained in inverted commas. — Keith
– Keith, Commented Aug 10, 2017 at 9:34

Tetsuya Yamamoto · Accepted Answer · 2017-08-10 09:20:47Z

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]

Dennis Larisch · Accepted Answer · 2017-08-10 09:27:04Z

1

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]

edited Aug 10, 2017 at 9:27

answered Aug 10, 2017 at 9:20

Dennis Larisch

5734 silver badges19 bronze badges

5 Comments

Keith Over a year ago

You are missing the schema, either add it or use [TCPDUMP]..[sample2Prot] to use default.

Dennis Larisch Over a year ago

The shema is an optional value, as you can see on MSDN

Keith Over a year ago

Have you tried running your code? It will think TCPDUMP is the schema.

Dennis Larisch Over a year ago

damn u are right, will change it. I thought if you dont enter a shema name, he uses the default shema (dbo)

Keith Over a year ago

That is true if you do not specify the database name AND the schema, it will use the default, so SELECT * FROM [Table] is the same as SELECT * FROM [DefaultSchema].[Table]

Collectives™ on Stack Overflow

C# : Table not created in SQL Server

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related