SQL - Syntax error near ','

Question

I can't for the life of me figure out why this query won't work no matter which one of my local databases I try (oracle, mysql, microsoft sql).

INSERT INTO testtable
VALUES ( 'testvalue' , 12345678, 123.04, 0, '1950-01-03' )

For example, with Microsoft SQL Server I get an Error near ','.

With MySQL I get

You have an error in your SQL syntax near '12345678, 123.04, 0, )' at line 2.

I've tried playing with it, I've looked at W3 school for the syntax of INSERT INTO. Everything looks good. What could it be?

Thank you!

EDIT:

As requested: here's the layout for mysql

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| TestString | varchar(600) | NO   |     | NULL    |       |
| TestInt    | int(11)      | NO   |     | NULL    |       |
| TestDouble | double       | NO   |     | NULL    |       |
| Testbool   | tinyint(1)   | NO   |     | NULL    |       |
| TestDate   | date         | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

Also, it should be noted I can run a parameterized query using these values and it will work just fine. It's only when I go to manually create the query that it's a problem.

can you print the results of the query "DESCRIBE testtable;" in mysql? — ajon, Sep 14 '12 at 04:18
Are you constructing the statement dynamically in the client code? — Andriy M, Sep 14 '12 at 04:56

Mike Christensen · Answer 1 · 2012-09-14T04:48:08.443

There is nothing wrong with your schema or syntax, as you can see from this SQL Fiddle.

My guess is that you're running more than one statement at a time and not properly delimiting each one. For example:

INSERT INTO testtable
VALUES ( 'testvalue' , 12345678, 123.04, 0, '1950-01-03' )

INSERT INTO testtable
VALUES ( 'testvalue' , 12345678, 123.04, 0, '1950-01-03' )

Will cause an error. However:

INSERT INTO testtable
VALUES ( 'testvalue' , 12345678, 123.04, 0, '1950-01-03' ); --End statement with ;

INSERT INTO testtable
VALUES ( 'testvalue' , 12345678, 123.04, 0, '1950-01-03' );

...will run fine. It's also possible that you have a stray open-quote somewhere that isn't being closed, causing part of your statement to be the ending part of another string constant. Verify exactly what you're running, as the code you've posted will work fine as-is.

That would be what I think is happening. Something is getting caught between the end of the first string and the beginning of the second. However, I can't figure out how. Or why. — Thenin, Sep 14 '12 at 05:18

xkeshav · Answer 2 · 2012-09-14T07:07:32.493

1

format of the value depends on the dataType of your columns, but other hand always wrap you value with ' except int dataType

TRY

INSERT INTO testtable
VALUES ( 'testvalue' , '12345678', '123.04', '0', '1950-01-03' );

edited Sep 14 '12 at 07:07

answered Sep 14 '12 at 04:16

xkeshav

53,360
44
177
245

When I do this I get the same error but this time '(,,,') is the error message details. – Thenin Sep 14 '12 at 04:29
check you table columns isn't there any space before and after column name. – xkeshav Sep 14 '12 at 04:34

score 0 · Answer 3 · answered Sep 14 '12 at 04:31

0

INSERT INTO testtable(column1,column2,column3,column4,column5) 
VALUES ( 'testvalue' , 12345678, 123.04, 0, '1950-01-03' )

answered Sep 14 '12 at 04:31

Vikram Jain

5,498
1
19
31

score 0 · Answer 4 · answered Sep 14 '12 at 05:03

0

INSERT INTO testtable (TestString,TestInt,TestDouble,Testbool,TestDate ) 
VALUES ( 'testvalue' , '12345678', '123.04', '0', '1950-01-03' )

Try This...

answered Sep 14 '12 at 05:03

Dinesh Guptha

97
7

SQL - Syntax error near ','

4 Answers4

Linked