Coast Line
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.struc tform.grou pAssociati on') AND arguments.structform.group Associatio n 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.structfo rm.USERNAM E#" list="yes">)
</cfquery>
<cfif listlen(arguments.structfo rm.groupAs sociation) >
<cfset NewID = #arguments.structform.user name#>
<cfquery name="insertItemCats" datasource="#request.dsn#" username="#request.user#" password="#request.pass#">
<cfloop index="x" list="#arguments.structfor m.groupAss ociation#" >
INSERT INTO GroupUser (userID,GroupID) values (#NewID#, #x#)
</cfloop>
</cfquery>
</cfif>
</cfif>
now i am getting erros is: errors are attched in the code window
<cfif IsDefined('arguments.struc
<cfquery name="deleteItemCats" datasource="#request.dsn#"
DELETE FROM GroupUser
WHERE userid IN (<cfqueryparam cfsqltype="cf_sql_integer"
</cfquery>
<cfif listlen(arguments.structfo
<cfset NewID = #arguments.structform.user
<cfquery name="insertItemCats" datasource="#request.dsn#"
<cfloop index="x" list="#arguments.structfor
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:
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
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
ASKER
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
But how access seems confusing
CF experts please guide me
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 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
ASKER
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!
INSERT INTO GroupUser (userID,GroupID) values (4, 2),(4, 3),(4, 4);