adding subquery in insert into

[jaik22]

[COLOR=#242729][FONT=Arial]"insert into temp_cost(RecordID,ClassID, Classtype, ClassName, TypeOrder, CreateDate)[/FONT][/COLOR][COLOR=#242729][FONT=Arial]  values('" & strRecordID &[/FONT][/COLOR]"','"& strClassID &"','"[COLOR=#242729][FONT=Arial]& strClassType & "', '" & strClassName & "', '" & strTypeorder & "', '" & strCreateDate & "')"[/FONT][/COLOR]

This code works, but I am trying to add a subquery to auto populate ClassID.

[COLOR=#242729][FONT=Arial]"insert into temp_cost(RecordID,ClassID, Classtype, ClassName, TypeOrder, CreateDate) select ('" & strRecordID & "', select classid from employee where classcode=&strClassType& , '" & strClassType & "', '" & strClassName & "', '" & strTypeorder & "', '" & strCreateDate & "')"[/FONT][/COLOR]

This is what I've tried and it's not looking up classid from the employee table. How can I make this work?

 

[jmacleary]

This is completely untested, but try

 "insert into temp_cost(RecordID,ClassID, Classtype, ClassName, TypeOrder, CreateDate) select ('" & strRecordID & "', (select classid from employee where classcode='strClassType') , '" & strClassType & "', '" & strClassName & "', '" & strTypeorder & "', '" & strCreateDate & "')"

 

[jaik22]

Thank you for the reply, But I am getting an error message "Incorrect syntax near','."
Is there any ways to make this work?

Thank you for your support!

 

[jmacleary]

Yeah silly me. Again untested:

"insert into temp_cost(RecordID,ClassID, Classtype, ClassName, TypeOrder, CreateDate) values('" & strRecordID & "', (select classid from employee where classcode='strClassType') , '" & strClassType & "', '" & strClassName & "', '" & strTypeorder & "', '" & strCreateDate & "')"

The online syntax checker I use says the syntax is valid but I think it may need some further qualification of the inner select statement.