I am trying to execute a stored procedure that will insert into a table. After inserting into the first table, I want to get the id of the last record inserted, and insert into other tables using that value as a foreign key, while at the same time inserting other values. (Using CF 4.0, SQL Server 7.0) I keep getting the error: "ODBC Error Code = 07001 (Wrong number of parameters)[Microsoft][ODBC SQL Server Driver]COUNT field incorrect"
Here is the stored proc:
================================
CREATE PROCEDURE [spAddTask]
-- Purpose: Insert into tblTask table and related data into child tables: tblAssignee,tblDepartment,tblSystem,tblResource
-- Declare variables for inserts to all 4 tables
@tskTitle varchar(75),
@tskSDesc varchar(150),
@tskDDesc varchar(255),
@tskSDate datetime,
@tskEDate datetime,
@tskPriLvl smallint,
@tskStat smallint,
@tskAssignor varchar(50),
@assigneeName varchar(50),
@departmentName varchar(50),
@systemName varchar(50),
@resourceName varchar(50),
@tskID int OUTPUT
AS
begin tran
-- Insert into tblTask
INSERT INTO tblTask ([tskTitle], [tskSDesc], [tskDDesc], [tskSDate], [tskEDate], [tskPriLvl], [tskStat], [tskAssignor])
VALUES (@tskTitle, @tskSDesc, @tskDDesc, @tskSDate, @tskEDate, @tskPriLvl, @tskStat, @tskAssignor)
-- Retrieve the automatically generated tskID VALUE from the tblTask table
SET @tskID = @@IDENTITY
-- Insert new values into tblAssignee table
DECLARE @AssInsert varchar(2000)
SET @AssInsert = 'INSERT INTO tblAssignee ([tskID], [assigneeName]) VALUES (@tskID, @assigneeName)'
exec(@AssInsert)
-- Insert new values into tblResource table
DECLARE @ResInsert varchar(2000)
SET @ResInsert = 'INSERT INTO tblResource ([tskID], [resourceName]) VALUES (@tskID, @resourceName)'
exec(@ResInsert)
-- Insert new values into tblSystem table
DECLARE @SysInsert varchar(2000)
SET @SysInsert = 'INSERT INTO tblSystem ([tskID], [systemName]) VALUES (@tskID, @systemName)'
exec(@SysInsert)
-- Insert new values into tblDepartment table
DECLARE @DepInsert varchar(2000)
SET @DepInsert = 'INSERT INTO tblDepartment ([tskID], [departmentName]) VALUES (@tskID, @departmentName)'
exec(@DepInsert)
Return
commit tran
GO
=====================================
Now here is my CF code, trying to execute the stored proc:
==========================================================
Thanks in advance for your help.
April
Here is the stored proc:
================================
CREATE PROCEDURE [spAddTask]
-- Purpose: Insert into tblTask table and related data into child tables: tblAssignee,tblDepartment,tblSystem,tblResource
-- Declare variables for inserts to all 4 tables
@tskTitle varchar(75),
@tskSDesc varchar(150),
@tskDDesc varchar(255),
@tskSDate datetime,
@tskEDate datetime,
@tskPriLvl smallint,
@tskStat smallint,
@tskAssignor varchar(50),
@assigneeName varchar(50),
@departmentName varchar(50),
@systemName varchar(50),
@resourceName varchar(50),
@tskID int OUTPUT
AS
begin tran
-- Insert into tblTask
INSERT INTO tblTask ([tskTitle], [tskSDesc], [tskDDesc], [tskSDate], [tskEDate], [tskPriLvl], [tskStat], [tskAssignor])
VALUES (@tskTitle, @tskSDesc, @tskDDesc, @tskSDate, @tskEDate, @tskPriLvl, @tskStat, @tskAssignor)
-- Retrieve the automatically generated tskID VALUE from the tblTask table
SET @tskID = @@IDENTITY
-- Insert new values into tblAssignee table
DECLARE @AssInsert varchar(2000)
SET @AssInsert = 'INSERT INTO tblAssignee ([tskID], [assigneeName]) VALUES (@tskID, @assigneeName)'
exec(@AssInsert)
-- Insert new values into tblResource table
DECLARE @ResInsert varchar(2000)
SET @ResInsert = 'INSERT INTO tblResource ([tskID], [resourceName]) VALUES (@tskID, @resourceName)'
exec(@ResInsert)
-- Insert new values into tblSystem table
DECLARE @SysInsert varchar(2000)
SET @SysInsert = 'INSERT INTO tblSystem ([tskID], [systemName]) VALUES (@tskID, @systemName)'
exec(@SysInsert)
-- Insert new values into tblDepartment table
DECLARE @DepInsert varchar(2000)
SET @DepInsert = 'INSERT INTO tblDepartment ([tskID], [departmentName]) VALUES (@tskID, @departmentName)'
exec(@DepInsert)
Return
commit tran
GO
=====================================
Now here is my CF code, trying to execute the stored proc:
==========================================================
Code:
<cfif IsDefined("form.cmdAdd")>
<CFSTOREDPROC PROCEDURE="spAddTask" DATASOURCE="MyCAMP_Dev">
<CFPROCPARAM TYPE="In" DBVARNAME=@tskTitle VALUE="#Trim(form.txtTaskTitle)#" CFSQLTYPE="CF_SQL_VARCHAR">
<CFPROCPARAM TYPE="In" DBVARNAME=@tskSDesc VALUE="#Trim(form.txaShortDescription)#" CFSQLTYPE="CF_SQL_VARCHAR">
<CFPROCPARAM TYPE="In" DBVARNAME=@tskDDesc VALUE="#Trim(form.txaDetailedDescription)#" CFSQLTYPE="CF_SQL_VARCHAR">
<CFPROCPARAM TYPE="In" DBVARNAME=@tskSDate VALUE="#Trim(form.txtStartDate)#" CFSQLTYPE="CF_SQL_DATE">
<CFPROCPARAM TYPE="In" DBVARNAME=@tskEDate VALUE="#Trim(form.txtEndDate)#" CFSQLTYPE="CF_SQL_DATE">
<CFPROCPARAM TYPE="In" DBVARNAME=@tskPriLvl VALUE="#Trim(form.optPriorityLevel)#" CFSQLTYPE="CF_SQL_SMALLINT">
<CFPROCPARAM TYPE="In" DBVARNAME=@tskStat VALUE=0 CFSQLTYPE="CF_SQL_SMALLINT">
<CFPROCPARAM TYPE="In" DBVARNAME=@tskAssignor VALUE="#NT_USER#" CFSQLTYPE="CF_SQL_VARCHAR">
<CFPROCPARAM TYPE="In" DBVARNAME=@assigneeName VALUE="#Trim(form.hidTaskAssignees)#" CFSQLTYPE="CF_SQL_VARCHAR">
<CFPROCPARAM TYPE="In" DBVARNAME=@departmentName VALUE="#Trim(form.txtAffectedDepartments)#" CFSQLTYPE="CF_SQL_VARCHAR">
<CFPROCPARAM TYPE="In" DBVARNAME=@systemName VALUE="#Trim(form.txtAffectedSystems)#" CFSQLTYPE="CF_SQL_VARCHAR">
<CFPROCPARAM TYPE="In" DBVARNAME=@resourceName VALUE="#Trim(form.txtAdditionalResources)#" CFSQLTYPE="CF_SQL_VARCHAR">
<CFPROCPARAM TYPE="Out" DBVARNAME=@tskID VARIABLE="x" CFSQLTYPE="CF_SQL_INTEGER">
</CFSTOREDPROC>
<p>New task added successfully. <br><br>
<a href="maintask.cfm">Click here to view all tasks.</a></p>
<cfelse>
......
</cfif>Thanks in advance for your help.
April
