troubleshooting Question

Coldfusion to insert a structure dynamically in SQL throws error

Avatar of Errol Farro
Errol FarroFlag for Aruba asked on
ColdFusion Language
2 Comments2 Solutions137 ViewsLast Modified:
I am trying dynamically to insert a structure into MS SQL using the below code.

When I run the code I get the  message.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '<'.

However, when running the resolved SQL no error is shown.

Any help is greatly appreciated

=========================
To create table
=========================
USE [tfdata]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[mortgEdit1](
	[meId] [int] IDENTITY(1,1) NOT NULL,
	[meBatchNo] [int] NULL,
	[meModule] [nvarchar](64) NULL,
	[meActive] [nvarchar](16) NULL,
	[meAction] [int] NULL,
	[meComments] [nvarchar](256) NULL,
	[meDateCreated] [datetime] NULL,
	[meDateUpdated] [datetime] NULL,
	[meCreatedBy] [nvarchar](64) NULL,
	[meUpdatedBy] [nvarchar](64) NULL,
 CONSTRAINT [mortgEdit1_pk] PRIMARY KEY CLUSTERED 
(
	[meId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO



=========================
CF Code
=========================

<cfset strucmortgEdit1 = structnew()>
<cfset strucmortgEdit1.meComments = "This is a test">
<cfset strucmortgEdit1.meActive = "Activie">
<cfset strucmortgEdit1.meBatchNo = 250>

<cfset keyList = structKeyList(strucmortgEdit1)>	
<cfset keyValueList = "">
<cfset counter = 1>	

<cfloop List="#keyList#" index="listItem">
    <cfset keyValueList = keyValueList & '<cfqueryparam cfsqltype="cf_sql_varchar" value="#strucmortgEdit1[listItem]#">'>
    <cfif counter LT listLen(keyList)>
        <cfset keyValueList = keyValueList & ",">
        <cfset counter = counter + 1>
    </cfif>
</cfloop>


<!--- This resolved SQL runs without a problem --->
<cfquery name="x1">
insert into 
	mortgEdit1(MEACTIVE,MECOMMENTS,MEBATCHNO) 
values 
	(<cfqueryparam cfsqltype="cf_sql_varchar" value="Activie">,<cfqueryparam cfsqltype="cf_sql_varchar" value="This is a test">,<cfqueryparam cfsqltype="cf_sql_varchar" value="250">)
</cfquery>


 
<!--- insert row to table. This one throws the error --->
<cfquery name="yourQueryName" >
	insert into 
		mortgEdit(#keyList#)
	values 
	(#keyValueList#)
</cfquery>
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros