Advertisement

05.13.2008 at 11:43AM PDT, ID: 23398952
[x]
Attachment Details

Only passing some parameters to TSQL stored procedure getting coldfusion error

Asked by polobruce in ColdFusion Application Server, MS SQL Server

Tags: Adobe, Coldfusion, 8

I've created a stored procedure in microsoft sql 2005 db.  I am trying to pass just the filepath, filename, size right now because they are all new records im inserting.  

Why is it that in coldfusion I have to create <cfprocparam's for all parameters and make them null in order for this to work.

What am i doing wrong?
Start Free Trial
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
TSQL
 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
 
 
 
ALTER PROCEDURE [dbo].[usp_InsertOrUpdateDocumentsLibrary] 
@AbstractKey uniqueidentifier = NULL,
@CreateDate datetime = NULL,
@ModifyDate datetime = NULL,
@Creator varchar(100) = NULL,
@Title varchar(200) = NULL,
@Keywords varchar(200) = NULL,
@xapMMDocumentID uniqueidentifier = NULL,
@format varchar(100) = NULL,
@ContentMain varchar(MAX) = NULL,
@FilePath varchar(200) = NULL,
@FileName varchar(100) = NULL,
@FileLocationURL varchar(200) = NULL,
@DateAdded datetime = NULL,
@Size int,
@Processed bit 
AS
If Exists (Select * From tblDocumentsLibrary WHERE Abstract_Key = @AbstractKey
or (FilePath = @FilePath and FileName=@FileName))
  UPDATE tblDocumentsLibrary
 
SET Abstract_Key = @AbstractKey,CreateDate = @CreateDate,ModifyDate = @ModifyDate,Creator = @Creator,Title = @Title,Keywords = @Keywords,xapMMDocumentID = @xapMMDocumentID,format = @format,ContentMain = @ContentMain,FilePath = @FilePath,FileName = @FileName,FileLocationURL = @FileLocationURL,DateAdded = @DateAdded,Size = @Size,Processed = @Processed 
WHERE Abstract_Key = @AbstractKey
 
ELSE  
  INSERT INTO dbo.tblDocumentsLibrary 
  ( Abstract_Key,CreateDate,ModifyDate,Creator,Title,Keywords,xapMMDocumentID,format,ContentMain,FilePath,FileName,FileLocationURL,DateAdded,Size,Processed
  )
  VALUES ( @AbstractKey,@CreateDate,@ModifyDate,@Creator,@Title,@Keywords,@xapMMDocumentID,@format,@ContentMain,@FilePath,@FileName,@FileLocationURL,@DateAdded,@Size,@Processed 
  )
 
 
 
 
coldfusion calling:
 
<cfloop query="abstractfiles">
<cfstoredproc procedure="usp_InsertOrUpdateDocumentsLibrary" datasource="svc" debug="yes">
	<cfprocparam type="in" dbvarname="@AbstractKey" value="" null="yes" cfsqltype="cf_sql_varchar">
    <cfprocparam type="in" dbvarname="@FilePath" value="#abstractfiles.directory#" cfsqltype="cf_sql_varchar">
	<cfprocparam type="in" dbvarname="@FileName"  value="#abstractfiles.name#" cfsqltype="cf_sql_varchar">
	<cfprocparam type="in" dbvarname="@Size" value="#abstractfiles.Size#" cfsqltype="cf_sql_numeric">
</cfstoredproc>
</cfloop>
 
 
[+][-]05.13.2008 at 03:05PM PDT, ID: 21559716

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 05:24AM PDT, ID: 21563308

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 06:26AM PDT, ID: 21563874

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 07:36AM PDT, ID: 21564660

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: ColdFusion Application Server, MS SQL Server
Tags: Adobe, Coldfusion, 8
Sign Up Now!
Solution Provided By: SidFishes
Participating Experts: 3
Solution Grade: A
 
 
[+][-]05.14.2008 at 07:46AM PDT, ID: 21564776

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 09:02AM PDT, ID: 21565616

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 09:05AM PDT, ID: 21565636

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628