CODEWITHSUNDEEP
sqlsql-server
Mostafa Talebi
Mostafa Talebi

Reputation: 9173

SQL insertion syntax error. Taking value as field

I have written the following SQL command to create a database, then create a table and then add data to the created_db.created_table.

However it seems to be wrong. Since SQL takes the values as the fields name. What is the problem with it?

I also use Microsoft SQL server 2012 Enterprise Edition.

CREATE DATABASE dbsample;
CREATE TABLE passwording (
     passwording_id INT
     ,passwording_username VARCHAR(256)
     ,passwording_date INT
);

INSERT INTO passwording (passwording_username, passwording_date) VALUES("myUserName", 754254354);

And the error I'm getting:

Msg 207, Level 16, State 1, Line 11
Invalid column name 'myUserName'.

Upvotes: 1

Views: 69

Answers (5)

Aniket
Aniket

Reputation: 181

INSERT INTO passwording
VALUES ('myUserName',754254354);

Upvotes: 0

Amul Harad
Amul Harad

Reputation: 146

Use single quote instead of double quotes for myUserName

Now u can run below query

CREATE DATABASE dbsample;
CREATE TABLE passwording (
     passwording_id INT
     ,passwording_username VARCHAR(256)
     ,passwording_date INT
);

INSERT INTO passwording (passwording_username, passwording_date) VALUES('myUserName', 754254354);

Upvotes: 1

Nagaraj S
Nagaraj S

Reputation: 13474

remove " in "myUserName" use 'myUserName'

INSERT INTO passwording 
        (passwording_username,passwording_date) 
VALUES ('myUserName',754254354);

Working Fiddle

Upvotes: 2

MusicLovingIndianGirl
MusicLovingIndianGirl

Reputation: 5947

In your table, you have specified datatype INT for column passwording_date. But you have entered value as 25/12/2011 which is a string. Would that work? You should have used Date datatype right?

EDIT: With reference to your updated question, why use "" to insert a string value to your table. Your query should rather be

INSERT INTO passwording (passwording_username, passwording_date) VALUES('myUserName', 754254354);

Upvotes: 1

Registered User
Registered User

Reputation: 8395

The passwording_date is defined as a INT instead of a DATE data type. This will cause a conversion failure message. Also, the passwording_id is not being set in your code. You may want to specify identity. Finally, the date format is in a British style, so you may want to explicitly cast it to a datetime data type with the corresponding style.

Here's a revised version of the code:

CREATE TABLE passwording (
     passwording_id INT IDENTITY(1, 1) NOT NULL
     ,passwording_username VARCHAR(256) NOT NULL
     ,passwording_date DATE NOT NULL
);

INSERT INTO passwording (passwording_username, passwording_date) VALUES('myUserName', CONVERT(DATETIME, '25/12/2011', 103));

Upvotes: 1

Related Questions