Errol Farro
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]Incorrec t syntax near '<'.
However, when running the resolved SQL no error is shown.
Any help is greatly appreciated
=========================
To create table
=========================
=========================
CF Code
=========================
When I run the code I get the message.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrec
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.