Link to home
Start Free TrialLog in
Avatar of Errol Farro
Errol FarroFlag for Aruba

asked on

Coldfusion to insert a structure dynamically in SQL throws error

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

Open in new window




=========================
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>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America 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
SOLUTION
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