Query Syntax error

Question

Please help me correct the following query:

SQL = "insert into tblContract (Empid, Start1, Finish1, Store1, " & _
                    "Start2, Finish2, Store2 ) " & _
      "values ('" & Me.txtEmpNo.Value & _
                    "',select max(testid) FROM tbltesting,'" & _     
                    Me.txtContSunStart1.Value & "', '" & _
                    Me.txtContSunFinish1.Value & "','" & _
                    Me.txtContSunStore1.Value & "','" & _
                    Me.txtContSunStart2.Value & "', '" & _
                    Me.txtContSunFinish2.Value & "','" & _
                    Me.txtContSunStore2.Value & "')"

Problem is here :

select max(testid) FROM tbltesting

Any other options ?

score 2 · Answer 1 · answered Jul 13 '09 at 12:52

2

Sub-queries should be enclosed in parentheses: (select max(testid) FROM tbltesting)

Note that your SQL engine will probably not support sub-queries in the INSERT statement, so you should insert using a INSERT...SELECT query instead.

answered Jul 13 '09 at 12:52

Blixt

49,547
13
120
153

Quassnoi · Answer 2 · 2009-07-13T13:12:52.960

1

Just remove VALUES:

SQL =  "
       INSERT INTO tblContract (Empid, Start1, Finish1, Store1, Start2, Finish2, Store2)
       SELECT " & Me.txtEmpNo.Value & "', MAX(testid), '" &
       Me.txtContSunStart1.Value & "', '" & Me.txtContSunFinish1.Value & "','" & 
       Me.txtContSunStore1.Value & "','" & Me.txtContSunStart2.Value & "', '" & 
       Me.txtContSunFinish2.Value & "','" & Me.txtContSunStore2.Value & "' " &
       "FROM tbltesting'"

Since you have 7 target fields and 8 SELECT list expressions, this won't compile until you provide an extra field in the INSERT clause where you want your MAX(testid) to go to.

edited Jul 13 '09 at 13:12

answered Jul 13 '09 at 12:52

Quassnoi

413,100
91
616
614

@Jonathan: it's perfectly possible to put them into the select list just as well – Quassnoi Jul 13 '09 at 12:56
Could please rewrite the entire query ?? – Jul 13 '09 at 13:04
It is, but you would need to show that in action - especially since the first value in the list is not the value from the sub-query. It is also complex because you'd have to use a GROUP BY clause citing an 'alias' for each constant. – Jonathan Leffler Jul 13 '09 at 13:05
Please tell me what details you need ? – Jul 13 '09 at 13:08
Ok for the below query it says syntax error : "INSERT INTO tblContract (Empid, Start1, Finish1, Store1, Start2, Finish2, Store2) SELECT " & Me.txtEmpNo.Value & "', MAX(testid), '" &Me.txtContSunStart1.Value & "', '" & Me.txtContSunFinish1.Value & "','" &Me.txtContSunStore1.Value & "','" & Me.txtContSunStart2.Value & "', '" &Me.txtContSunFinish2.Value & "','" & Me.txtContSunStore2.Value & "' " & "FROM tbltesting'" – Jul 13 '09 at 13:18
"INSERT INTO tblContract (Empid,testid,Start1, Finish1, Store1, Start2, Finish2, Store2) SELECT " & Me.txtEmpNo.Value & "', MAX(testid), '" &Me.txtContSunStart1.Value & "', '" & Me.txtContSunFinish1.Value & "','" &Me.txtContSunStore1.Value & "','" & Me.txtContSunStart2.Value & "', '" &Me.txtContSunFinish2.Value & "','" & Me.txtContSunStore2.Value & "' " – Jul 13 '09 at 13:26
@Jaison: you have 7 target fields and 8 columns. Where you want you MAX(testid) to go to? – Quassnoi Jul 13 '09 at 13:27
@Jaison: are you sure the query is the reason of the error? What happens if you replace all you fields with literal values? – Quassnoi Jul 13 '09 at 13:36

Jonathan Leffler · Answer 3 · 2009-07-13T13:20:56.413

1

If anything is going to work, you will have to embed the sub-query inside an extra layer of parentheses:

INSERT INTO SomeTable(Col1, Col2, Col3)
    VALUES(val1, (SELECT MAX(testid) FROM tbltesting), val3);

Be aware that you are vulnerable to SQL Injection attacks.

Using IBM Informix Dynamic Server 11.50 in a database with a 'table of elements', the following works:

create temp table t(i integer, j integer, k integer);
insert into t values(1, (select max(atomic_number) from elements), 2);
select * from t;

1   118     2

This is correct given the current data in the table of elements.

edited Jul 13 '09 at 13:20

answered Jul 13 '09 at 12:53

Jonathan Leffler

730,956
141
904
1,278

Note the answer from @onedaywhen: you have a mismatch between the number of columns listed and the number of values provided. The 'Compile error argument not optional' message might be related to that mismatch. – Jonathan Leffler Jul 13 '09 at 13:25
no no ..still the query not working ..even i give the same number of columns – Jul 13 '09 at 13:33
OK - it may be that this construct is not supported by MS Access, then. It is quite probably a reasonable but non-standard extension of SQL (using a construct that is allowed elsewhere in SQL in a context where the SQL standard does not require it to be allowed). – Jonathan Leffler Jul 13 '09 at 20:26
@Jonathan Leffler: Given the Access context, please explain how the cited SQL statement is in any way shape or form vulnerable to SQL injection. – David-W-Fenton Jul 13 '09 at 22:29
It depends on what can be put into any of the variables such as `Me.txtContSunStart1.Value`; they seem to be user inputs and if they are not strictly typed, then it could contain text along the lines of the example in the XKCD cartoon. Any time a user-supplied value is converted to a string (or is treated as a string) and is not protected from misinterpretation, there is a chance of SQL Injection. There isn't enough code to be sure either way - at least, not to my eyes. But because there was no self-evident protection when building the SQL statement, there is a chance of SQL Injection. – Jonathan Leffler Jul 14 '09 at 00:36

score 1 · Answer 4 · answered Jul 13 '09 at 13:13

1

You need to re-write your VALUES clause as a SELECT query.

You have seven columns in your INSERT clause and eight in your VALUES clause. From the column names I guess your subquery

select max(testid) FROM tbltesting

is missing a destination. Guessing it may be called starting_testid; also guessing data types (Access database engine ANSI-92 Query Mode syntax):

CREATE PROCEDURE AddContract
(
 :Empid INTEGER, 
 :Start1 DATETIME, 
 :Finish1 DATETIME,
 :Store1 VARCHAR(20), 
 :Start2 DATETIME,
 :Finish2 DATETIME,
 :Store2 VARCHAR(20)
)
AS
insert into tblContract 
( 
 Empid, starting_testid, 
 Start1, Finish1, Store1, 
 Start2, Finish2, Store2
)
SELECT :Empid, max(testid), 
       :Start1, :Finish1, :Store1, 
       :Start2, :Finish2, :Store2
  FROM tbltesting;

answered Jul 13 '09 at 13:13

onedaywhen

55,269
12
100
138

Well done spotting the mismatch between the number of named columns and the number of values. – Jonathan Leffler Jul 13 '09 at 13:22
Standard SQL would require a GROUP BY clause when you mix the aggregate and the non-aggregate values in the select-list. Does that work OK in M Access - or MS SQL Server? – Jonathan Leffler Jul 13 '09 at 13:23
no no ..still the query not working ..even i give the same number of columns – Jul 13 '09 at 13:27
"Standard SQL would require a GROUP BY clause when you mix the aggregate and the non-aggregate values in the select-list" -- I'm not sure that's correct. If you replace the parameter values with literals then the Mimer SQL-92 Validator (http://developer.mimer.com/validator/parser92/index.tml) says it is entry level SQL-92. The standard for procs is SQL/PSM but I don't know of a validator for that ;) Also, not many SQLs support the SQL/PSM standard, the Access database engine certainly doesn't! :) – onedaywhen Jul 13 '09 at 13:44
@Jonathan Leffler: "Does that work OK in M Access" -- I tested it for the Access database engine and it indeed works... – onedaywhen Jul 13 '09 at 13:45
@Jaison: I've guessed data types and a column name, so the chances of it working are small. Also note you need to put the Access UI into ANSI-92 Query Mode or use an OLE DB connection e.g. ADO code. – onedaywhen Jul 13 '09 at 13:46
@onedaywhen: Why would you answer an Access question with a reference to Mimer SQL-92 Validator? How is that even remotely relevant? – David-W-Fenton Jul 13 '09 at 22:27
I received a comment (Jonathon Leffler) about standard SQL. The Mimer SQL-92 validator tells you whether SQL text complies with any level of SQL-92 Standard SQL. I used the validator to challenge the point about Standard SQL's requirement or otherwise for a GROUP BY clause. So in summary 1) OT perhaps but he started it; 2) I didn't answer a question, I commented on a comment; 3) portability should always be a consideration ;) – onedaywhen Jul 13 '09 at 22:52

Query Syntax error

4 Answers4

Linked

Related