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 & "'"

Answer 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'

Answer 2

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.

Answer 3

There are a number of issues I potentially see.

1. Column names shouldn't be quoted, i.e. INTO UserTimeStamp('username','id') should be INTO UserTimeStamp(username, id)

2. Column fields, unless literal strings, shouldn't be quoted either. i.e. SELECT ('username','id') should be SELECT username, id.

3. 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);