Link to home
Start Free TrialLog in
Avatar of Coast Line
Coast LineFlag for Canada

asked on

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

Avatar of Shinesh Premrajan
Shinesh Premrajan
Flag of India image

Give a try on this, not sure

INSERT INTO GroupUser (userID,GroupID) values (4, 2),(4, 3),(4, 4);
Avatar of Coast Line

ASKER

well how, if i provid extra comma, it will always end the comma the the end also. which will again generate error!
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
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
ASKER CERTIFIED SOLUTION
Avatar of azadisaryev
azadisaryev
Flag of Hong Kong image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
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!