access multiple inserts

i am trying to do the multiple insert in the accesss database but encountring errors:

now i am getting erros is: errors are attched in the code window



<cfif IsDefined('arguments.structform.groupAssociation') AND arguments.structform.groupAssociation NEQ "">
            <cfquery name="deleteItemCats" datasource="#request.dsn#" username="#request.user#" password="#request.pass#">
        DELETE FROM GroupUser  
        WHERE userid IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.structform.USERNAME#" list="yes">)
        </cfquery>
            <cfif listlen(arguments.structform.groupAssociation)>
          <cfset NewID = #arguments.structform.username#>
             <cfquery name="insertItemCats" datasource="#request.dsn#" username="#request.user#" password="#request.pass#">
                <cfloop index="x" list="#arguments.structform.groupAssociation#">
                INSERT INTO GroupUser (userID,GroupID) values (#NewID#, #x#)
                </cfloop>
             </cfquery>
        </cfif>
          </cfif>
1st error:

 Error Executing Database Query.
Missing semicolon (;) at end of SQL statement.
 
The error occurred in C:\inetpub\wwwroot\Project1\com\tools.cfc: line 113
Called from C:\inetpub\wwwroot\Project1\complete.cfm: line 3
Called from C:\inetpub\wwwroot\Project1\com\tools.cfc: line 113
Called from C:\inetpub\wwwroot\Project1\complete.cfm: line 3

111 :           <cfset NewID = #arguments.structform.username#>
112 :              <cfquery name="insertItemCats" datasource="#request.dsn#" username="#request.user#" password="#request.pass#">
113 :                 <cfloop index="x" list="#arguments.structform.groupAssociation#">
114 :                 INSERT INTO GroupUser (userID,GroupID) values (#NewID#, #x#)
115 :                 </cfloop>

VENDORERRORCODE 	  3092
SQLSTATE 	   
SQL 	   INSERT INTO GroupUser (userID,GroupID) values (4, 2) INSERT INTO GroupUser (userID,GroupID) values (4, 3) INSERT INTO GroupUser (userID,GroupID) values (4, 4)
DATASOURCE 	  helpsystem
Resources: 

if i ADD THE ; AT THE END THEN I GET THIS ERROR

 Error Executing Database Query.
Characters found after end of SQL statement.
 
The error occurred in C:\inetpub\wwwroot\Project1\com\tools.cfc: line 113
Called from C:\inetpub\wwwroot\Project1\complete.cfm: line 3
Called from C:\inetpub\wwwroot\Project1\com\tools.cfc: line 113
Called from C:\inetpub\wwwroot\Project1\complete.cfm: line 3

111 :           <cfset NewID = #arguments.structform.username#>
112 :              <cfquery name="insertItemCats" datasource="#request.dsn#" username="#request.user#" password="#request.pass#">
113 :                 <cfloop index="x" list="#arguments.structform.groupAssociation#">
114 :                 INSERT INTO GroupUser (userID,GroupID) values (#NewID#, #x#);
115 :                 </cfloop>

VENDORERRORCODE 	  3092
SQLSTATE 	   
SQL 	   INSERT INTO GroupUser (userID,GroupID) values (4, 2); INSERT INTO GroupUser (userID,GroupID) values (4, 3); INSERT INTO GroupUser (userID,GroupID) values (4, 4);
DATASOURCE 	  helpsystem
Resources:

Open in new window

LVL 16
Gurpreet Singh RandhawaCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shinesh PremrajanEngineering ManagerCommented:
Give a try on this, not sure

INSERT INTO GroupUser (userID,GroupID) values (4, 2),(4, 3),(4, 4);
Gurpreet Singh RandhawaCEOAuthor Commented:
well how, if i provid extra comma, it will always end the comma the the end also. which will again generate error!
Patrick MatthewsCommented:
myselfrandhawa,

Instead of submitting the inserts all as one batch, which Jet is not going to like, try to either issue the inserts one at a time, or to restructure as something like this:

INSERT INTO GroupUser (userID, GroupID)
SELECT z.userID, z.GroupID
FROM
    (SELECT 4 AS userID, 2 AS GroupID
    UNION ALL
    SELECT 4 AS userID, 3 AS GroupID
    UNION ALL
    SELECT 4 AS userID, 4 AS GroupID) AS z

I have no idea how to build up that statement in ColdFusion, but that is the statement you want to execute.

Patrick
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Gurpreet Singh RandhawaCEOAuthor Commented:
hmm! the trouble is it is to insert the value dynamically and i do not like access because the same above i have done above for mysql and mssql and it works very fine!

But how access seems confusing

CF experts please guide me
azadisaryevCommented:
ms access (jet db engine) does not support multiple sql statements in one query.

you have to run multiple queries inside cfloop:

<cfset NewID = #arguments.structform.username#>
<cfloop index="x" list="#arguments.structform.groupAssociation#">
  <cfquery name="insertItemCats" ...>
  INSERT INTO GroupUser (userID,GroupID) values (#NewID#, #x#)
  </cfquery>
</cfloop>

Azadi

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gurpreet Singh RandhawaCEOAuthor Commented:
Hmm! Thanks Gret It works Just like MSSQL. the syntax i provided works like mysql but this one and mssql works this way! cheers buddy
Patrick MatthewsCommented:
myselfrandhawa,

I must say, I am a little surprised for having gotten no credit for diagnosing the problem (that Jet cannot run multiple SQL statements) and suggesting a way to fix it.

No doubt azadisaryev should receive most of the credit for providing the code, of course!

Patrick
Gurpreet Singh RandhawaCEOAuthor Commented:
I am extremely Sorry Dude! i will consider these points furthur in clarification if i encounter anything in future! although if modification can be done, i will do it from my side!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.