i get this error Incorrect syntax near ','. on sql server 2008 R2

Question

0.00/5 (No votes)

See more:

this is my code

SQL

Insert Into Teacher1(Id_Teacher, Name, Familyname, Phone, Mobile, Email, Adress, Fk_Sex, Fk_Major, Fk_Education)

Values( ,"Kamran", "Mortazavi", 8899100, 09126254019, "K.m.r@yahoo.com", , 2, 3,2)

this is error :

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

what is wrong ? how to fix it ?

Posted 7-Jul-15 0:55am

brandon1999

Updated 28-Mar-18 5:14am

Suvendu Shekhar Giri

v4

Add a Solution

Comments

[no name] 7-Jul-15 7:10am

Supply the correct type and number of values.

2 solutions

Solution 1

SQL does not support default-value-like writing in INSERT INTO ... VALUES query...
You can not skip values but adding one more comma instead of...

SQL

Values( !!!,"Kamran", "Mortazavi", 8899100, 09126254019, "K.m.r@yahoo.com", !!!, 2, 3,2)

You have to add values or remove the fields...

Posted 7-Jul-15 1:14am

Kornfeld Eliyahu Peter

Comments

Sanjoy Das2 25-Oct-18 6:35am

in following code I am getting Error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'NULL'.

SELECT FirstName + ' ' + LastName + '(' + UserCode + ')' as 'User Name',TBLTRANSJOBS.JobName as 'Job Name', TBLTRANSDOCUMENTS.DocName as 'Loan Name',Convert(Varchar(25),AssignedDate,131) as 'Assigned Date',Convert(Varchar(25),
(SELECT RIGHT('0' + CAST(Datediff(second,AssignedDate,GetDate()) / 3600 AS VARCHAR),2) + ':' + RIGHT('0' + CAST((Datediff(second,AssignedDate,GetDate()) / 60) % 60 AS VARCHAR),2) + ':' + RIGHT('0' + CAST(Datediff(second,AssignedDate,GetDate()) % 60 AS VARCHAR),2) as 'Elapsed Time'
FROM ((TBLTRANSDOCUMENTS inner join TBLTRANSJOBS ON TBLTRANSJOBS.JobID = TBLTRANSDOCUMENTS.JobID) inner join TBLTRANSUSERS on TBLTRANSDOCUMENTS.ExtractionAssigned=TBLTRANSUSERS.UserID) WHERE KeyedDate is NULL and ExtractionAssigned is not NULL

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Suvendu Shekhar Giri · Accepted Answer · 2015-07-07T01:22:00

Solution 3

You haven't provided value for Id_Teacher and Adress. You need to provide a value or you can pass NULL or just ignore the column from the INSERT statement.

1. Ignoring Columns

SQL

Insert Into Teacher1(Name, Familyname, Phone, Mobile, Email, Fk_Sex, Fk_Major, Fk_Education)
 
Values("Kamran", "Mortazavi", 8899100, 09126254019, "K.m.r@yahoo.com", 2, 3,2)

But for this query to execute successfully, you need to have IDENTITY on for the column Id_Teacher
2. Passing NULL

SQL

Insert Into Teacher1(Id_Teacher, Name, Familyname, Phone, Mobile, Email, Adress, Fk_Sex, Fk_Major, Fk_Education)
 
Values( NULL,"Kamran", "Mortazavi", 8899100, 09126254019, "K.m.r@yahoo.com",NULL , 2, 3,2)

Important
I strongly recomend you to use parameterized query or stored procedure to avoid SQL Injection.
Read this tutorial:[^]

Hope, it helps :)

Posted 7-Jul-15 1:22am

Suvendu Shekhar Giri

Updated 7-Jul-15 1:24am

v2

Comments

brandon1999 7-Jul-15 7:32am

i copy this code :

Insert Into Teacher1(Id_Teacher, Name, Familyname, Phone, Mobile, Email, Adress, Fk_Sex, Fk_Major, Fk_Education)

Values( 1, 'Kamran', 'Mortazavi', 8899100, 09126254019, 'K.m.r@yahoo.com',NULL , 2, 3,2)

and paste it in sql but i get this error code :

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.

what to do now ?

Suvendu Shekhar Giri 7-Jul-15 7:46am

Ok. Don't worry. Can you share the table structure (i.e, datatypes of each field/column)?

brandon1999 7-Jul-15 7:57am

here :

tools.dynamicdrive.com/imageoptimizer/data/m9vyk81/Untitled.jpg

Suvendu Shekhar Giri 7-Jul-15 8:02am

No, I am not able to view the image. Can you write here like Id_Teacher int,Name varchar(50),..... ?

brandon1999 7-Jul-15 8:06am

here :

Name nvarchar(50) Checked

FamilyName nvarchar(50) Checked

Phone int Checked

Mobile int Checked

Email nvarchar(MAX) Checked

Adress nvarchar(MAX) Uncahecked

Fk_Sex int Checked

Fk_Major int Checked

Fk_Education int Checked

Suvendu Shekhar Giri 7-Jul-15 8:15am

Phone and Mobile fields can't store value more than the max size of INT as you have declared them as of INT datatype. That's causing the arithmatic overflow. My suggestion is to change them to varchar(15). You need not declare a field to be type of numeric unless you want any arithmatic operations to be done on them.
If you change this then you also have to change the query so that it can accept string as values i.e, need to wrap values in a pair of single quotes.

brandon1999 7-Jul-15 8:20am

thank you so much. it's done now. thank you again.

Suvendu Shekhar Giri 7-Jul-15 8:29am

Glad to know that it helped :)