incorrect syntax error near ,

Question

I have recently changed my database from access to a .mdf and now I am having problems getting my code to work.

One of the problems im having is this error "incorrect syntax near ,".

I have tried different ways to try fix this for example putting brackets in, moving the comma, putting spaces in, taking spaces out but I just cant get it.

I would be so grateful if anyone could help me.

My code is:

SqlStr = "INSERT INTO UserTimeStamp ('username', 'id') SELECT ('username', 'id') FROM Staff WHERE password = '" & passwordTB.Text & "'"

Assuming you're looking for `username` and `id` columns, then that's not proper SQL syntax: `"INSERT INTO UserTimeStamp (username, id) SELECT username, id FROM Staff WHERE password = '" & passwordTB.Text & "'"` — valverij, Dec 19 '13 at 16:11

score 1 · Accepted Answer · answered Dec 19 '13 at 16:15

1

Assuming you're looking for username and id columns, then that's not proper SQL syntax.

The main issues are that you're column names are enclosed in single quotes and in parentheses in your select. Try changing it to this:

SqlStr = "INSERT INTO UserTimeStamp (username, id) SELECT username, id FROM Staff WHERE password = '" & passwordTB.Text & "'"

That will get sent off to SQL like this:

INSERT INTO UserTimeStamp (username, id) 
    SELECT username, id 
    FROM Staff
    WHERE password = 'some password'

answered Dec 19 '13 at 16:15

valverij

4,871
1
22
35

This answer still subjects user to SQL injection attack. I would recommend using a method to secure the parameter for ad-hoc query or to use stored procedure. – Kyle B. Dec 19 '13 at 16:27
Basically, a user sends SQL commands through an input that gets inserted into your query where you're concatenating strings (in your case right here: `password = '" & passwordTB.Text & "'"`). Here's a good (but old) article on them and how to avoid them: [SQL Injection Attacks and Some Tips on How to Prevent Them](http://www.codeproject.com/Articles/9378/SQL-Injection-Attacks-and-Some-Tips-on-How-to-Prev). In .NET, you'd avoid them using [SqlParameterCollection.AddWithValue](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue(v=vs.110).aspx) – valverij Dec 19 '13 at 17:13
I wasn't sure of what programming language you were using, though, so I left that bit out. If you *are* using a .NET language, though, then that MSDN link contains some good examples. If not, you can surely find some by just throwing your programming language name + "parameterized queries" into Google – valverij Dec 19 '13 at 17:15
Yeah I am using a .Net language. Thanks valverij! – KerrieJMc Dec 20 '13 at 14:38

adamk · Answer 2 · 2013-12-23T14:44:21.897

0

Try wrapping column names in square brackets like so:

INSERT INTO employee ([FirstName],[LastName]) SELECT [FirstName],[LastName] FROM Employee where [id] = 1

Edit: Also drop the parentheses surrounding the selected fields.

edited Dec 23 '13 at 14:44

answered Dec 19 '13 at 16:16

adamk

61
1
3

This has absolutely nothing to do with the above question – valverij Dec 19 '13 at 16:17
@valverij It does. It's a valid but MSSQL-specific way of quoting identifiers, unlike the single quotes in the question. – Dec 19 '13 at 16:19

score 0 · Answer 3 · answered Dec 19 '13 at 16:26

There are a number of issues I potentially see.

Column names shouldn't be quoted, i.e. INTO UserTimeStamp('username','id') should be INTO UserTimeStamp(username, id)
Column fields, unless literal strings, shouldn't be quoted either. i.e. SELECT ('username','id') should be SELECT username, id.
You are putting yourself at risk for T-SQL injection by quoting your parameter like that. You should consider using a stored procedure, or use a well tested function to secure your parameters if you are doing ad-hoc queries.

SqlStr = "INSERT INTO UserTimeStamp (username, id) SELECT username, id FROM Staff WHERE password = " + MyQuoteFunction(passwordDB.Text);

incorrect syntax error near ,

3 Answers3