Insert statement with where clause

Question

Possible Duplicate:
Query Syntax error

I want insert value using the below command

DoCmd.RunSQL = "insert into tblContract(
 Empid,
 Start1,
 Finish1,
 Store1,
 Start2,
 Finish2,
 Store2)
values ('" & Me.txtEmpNo.Value & "','" 
  & Me.txtContSunStart1.Value & "', '"
  & Me.txtContSunFinish1.Value & "','" 
  & Me.txtContSunStore1.Value & "','" 
  & Me.txtContSunStart2.Value & "', '" 
  & Me.txtContSunFinish2.Value & "','" 
  & Me.txtContSunStore2.Value & "')"

But while inserting it should insert in the row where value of a column(testid) in the same table is max(testid) of another table.

This query not working :

SQL =  "
       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 & "' " &
       "FROM tbltesting'"

I am finding an alternative for this . In ms access it gives compile error

You have to clarify a bit what you mean... You can't insert anything into an existsing row, that would be an update. — Guffa, Jul 13 '09 at 14:20
Exact duplicate of http://stackoverflow.com/questions/1119246/query-syntax-error — gbn, Jul 13 '09 at 14:20
You also might want to look at stored procedures or parameterized queries, this would be very unsafe especially if public facing, it's very open to sql injection attacks. — John Boker, Jul 13 '09 at 14:21

score 3 · Answer 1 · answered Jul 13 '09 at 14:20

3

You can use an INSERT SELECT instead of an INSERT VALUES. such as.

INSERT INTO MyTable (ColumnA, ColumnB, ColumnC) SELECT 'A', 'B', MAX(ColumnC) FROM MyOtherTable

On another note though, you should NOT build up your SQL as you are. This is prone to SQL Injection. Someone could quite easily enter "; DROP TABLE tblContract" into one of your text boxes!

answered Jul 13 '09 at 14:20

Robin Day

100,552
23
116
167

Er, Access won't execute multiple statements with the DoCmd.RunSQL command -- it will give you an "invalid characters after end of SQL statement" error message. – David-W-Fenton Jul 13 '09 at 21:57
BTW, that doesn't mean you shouldn't validate user input, just that the cited example of SQL injection can't possibly happen within Access (or any form of Jet/ACE, neither of which support batched SQL statements). – David-W-Fenton Jul 13 '09 at 21:58

score 1 · Answer 2 · answered Jul 13 '09 at 14:21

1

First you will need to grab the value from the other table and then used that to insert into this table, you can also use a trigger to do that. Be aware that you can have the same value twice if 2 users execute the same query at the same time...you will need lock the other table in that case...max() is problematic

why not use identity and then the scope_identity() function to grab after you insert into the other table?

you also understand that what you doing is a sql injection attack waiting to happen

answered Jul 13 '09 at 14:21

SQLMenace

132,095
25
206
225

2

SQL injection on a VBA application can't be that big of a concern. – Matthew Groves Jul 13 '09 at 14:23
Yeah but how long will it be a VBA application? These things often get a life of thier own and get ported – n8wrl Jul 13 '09 at 14:30
mgroves, some people do use Access as a web interface. Also, don't you think you should protect your data from disgruntled employees as well as outsiders? SQl Injection doesn't have to come from outside the company. Besides, learning to think about such things as "Is this open to attack" will help this person's career when he moves away from Access. – HLGEM Jul 13 '09 at 14:34
Please cite an example of someone using Access as a "web interface." It's a nonsensical assertion, actually, if you use a proper definition of both "Access" and "web interface." – David-W-Fenton Jul 13 '09 at 21:59
1

BTW, in regard to Access SQL Injection, this thread http://stackoverflow.com/questions/512174/non-web-sql-injection hashes it out and shows that in general, there's only a very limited set of vulnerabities, and none involve DDL or updates to data -- they are limited to faking WHERE clauses in SELECT statements that will then return all rows. – David-W-Fenton Jul 13 '09 at 22:19

score 1 · Answer 3 · answered Jul 13 '09 at 14:21

1

If I read your question correctly you want

INSERT INTO tblContract(col1..., testId) values
    ('value1',..., (SELECT MAX(testID) FROM tblOther));

Be warned this may lead to concurrency issues

answered Jul 13 '09 at 14:21

Nathan Koop

24,803
25
90
125

This is how I was thinking it needed to be done. – Powerlord Jul 13 '09 at 14:22
4

And then some! This is not a good practice to follow and should be stamped out now! Either know the foreign key value in advance and insert the value with it, or insert both tables in the same transaction. – Randolpho Jul 13 '09 at 14:23

Insert statement with where clause

3 Answers3