This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01BFDF6D.767FEBEC
Content-Type: text/plain;
charset="iso-8859-1"
I'm looking for a way of updating a SQL Server 7.0 table using a stored proc
and the CFSTOREDPROC tag in cold fusion.
Heres the procedure (just a simple update):
CREATE PROCEDURE sp_UpdateCDROM ( @CDROMID int, @Title varchar(64),
@FullTitle varchar(128) = null,
@Supplier varchar(64) = null,
@DiskNo smallint = null,
@CDKey varchar(64) = null,
@Category smallint = null,
@Keywords varchar(256) = null,
@LoanedTo varchar(64) = null) AS
UPDATE CDCatalog
SET Title = @Title,
FullTitle = @FullTitle,
Keywords = @Keywords,
Supplier = @Supplier,
Category = @Category,
CDKey = @CDKey,
DiskNo = @DiskNo,
LoanedTo = @LoanedTo
WHERE CDROMID = @CDROMID
And I have verified that this works as intended by testing in the Query
Analyzer. However, when I come to use the CFSTOREDPROC tag, like this:
<cfstoredproc procedure="sp_UpdateCDROM" datasource="HRDB">
<cfprocparam type="in" dbvarname="@CDROMID" value="#FORM.CDROMID#"
cfsqltype="CF_SQL_INTEGER">
<cfprocparam type="in" dbvarname="@Title" value="#FORM.Title#"
cfsqltype="CF_SQL_VARCHAR">
<cfprocparam type="in" dbvarname="@FullTitle" value="#FORM.FullTitle#"
cfsqltype="CF_SQL_VARCHAR">
<cfprocparam type="in" dbvarname="@Supplier" value="#FORM.Supplier#"
cfsqltype="CF_SQL_VARCHAR">
<cfprocparam type="in" dbvarname="@DiskNo" value="#FORM.DiskNo#"
cfsqltype="CF_SQL_SMALLINT">
<cfprocparam type="in" dbvarname="@CDKey" value="#FORM.CDKey#"
cfsqltype="CF_SQL_VARCHAR">
<cfprocparam type="in" dbvarname="@Category" value="#FORM.Category#"
cfsqltype="CF_SQL_SMALLINT">
<cfprocparam type="in" dbvarname="@Keywords" value="#FORM.Keywords#"
cfsqltype="CF_SQL_VARCHAR">
<cfprocparam type="in" dbvarname="@LoanedTo" value="#FORM.LoanedTo#"
cfsqltype="CF_SQL_VARCHAR">
</cfstoredproc>
I always get the following error message:
ODBC Error Code = 22005 (Error in assignment)
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQL = "sp_UpdateCDROM"
Data Source = "HRDB"
Is one of cfsqltypes wrong in the cfprocparam tag? Do I need to feed in the
sizes of the parameters also? It's odd because other stored procedures that
I've written work fine when they are called from Cold Fusion.
James
| james page | database developer |
| UPC TV interactive |
| w. +44 (2)0 7533 6573
| [EMAIL PROTECTED]
------_=_NextPart_001_01BFDF6D.767FEBEC
Content-Type: text/html;
charset="iso-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 5.00.2314.1000" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial size=2><SPAN class=750523212-26062000>I'm looking for a
way of updating a SQL Server 7.0 table using a stored proc and the CFSTOREDPROC
tag in cold fusion.</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=750523212-26062000></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=750523212-26062000>Heres the procedure
(just a simple update):</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=750523212-26062000></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=750523212-26062000>CREATE PROCEDURE
sp_UpdateCDROM ( @CDROMID int, @Title varchar(64), <BR> @FullTitle
varchar(128) = null, <BR> @Supplier varchar(64) = null,<BR> @DiskNo
smallint = null,<BR> @CDKey varchar(64) = null,<BR> @Category smallint
= null,<BR> @Keywords varchar(256) = null,<BR> @LoanedTo varchar(64) =
null) AS</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=750523212-26062000> UPDATE
CDCatalog<BR> SET Title = @Title,<BR> FullTitle =
@FullTitle,<BR> Keywords = @Keywords,<BR>
Supplier = @Supplier,<BR> Category =
@Category,<BR> CDKey = @CDKey,<BR> DiskNo =
@DiskNo,<BR> LoanedTo = @LoanedTo<BR> WHERE CDROMID =
@CDROMID</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=750523212-26062000></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=750523212-26062000>And I have verified
that this works as intended by testing in the Query Analyzer. However, when I
come to use the CFSTOREDPROC tag, like this:</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2><cfstoredproc procedure="sp_UpdateCDROM"
datasource="HRDB"><BR><cfprocparam type="in" dbvarname="@CDROMID"
value="#FORM.CDROMID#" cfsqltype="CF_SQL_INTEGER"><BR><cfprocparam
type="in" dbvarname="@Title" value="#FORM.Title#"
cfsqltype="CF_SQL_VARCHAR"><BR><cfprocparam type="in"
dbvarname="@FullTitle" value="#FORM.FullTitle#"
cfsqltype="CF_SQL_VARCHAR"><BR><cfprocparam type="in"
dbvarname="@Supplier" value="#FORM.Supplier#"
cfsqltype="CF_SQL_VARCHAR"><BR><cfprocparam type="in" dbvarname="@DiskNo"
value="#FORM.DiskNo#" cfsqltype="CF_SQL_SMALLINT"><BR><cfprocparam
type="in" dbvarname="@CDKey" value="#FORM.CDKey#"
cfsqltype="CF_SQL_VARCHAR"><BR><cfprocparam type="in"
dbvarname="@Category" value="#FORM.Category#"
cfsqltype="CF_SQL_SMALLINT"><BR><cfprocparam type="in"
dbvarname="@Keywords" value="#FORM.Keywords#"
cfsqltype="CF_SQL_VARCHAR"><BR><cfprocparam type="in"
dbvarname="@LoanedTo" value="#FORM.LoanedTo#"
cfsqltype="CF_SQL_VARCHAR"><BR></cfstoredproc><BR></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=750523212-26062000>I always get the
following error message:</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=750523212-26062000></SPAN></FONT> </DIV>
<DIV><SPAN class=750523212-26062000>
<P><FONT face=Arial size=2>ODBC Error Code = 22005 (Error in assignment) </FONT>
<P>
<P><FONT face=Arial size=2>[Microsoft][ODBC SQL Server Driver]Invalid character
value for cast specification </FONT>
<P>
<P><FONT face=Arial size=2>SQL = "sp_UpdateCDROM" </FONT>
<P><FONT face=Arial size=2>Data Source = "HRDB"</FONT></P>
<P> </P>
<P><FONT face=Arial size=2><SPAN class=750523212-26062000>Is one of cfsqltypes
wrong in the cfprocparam tag? Do I need to feed in the sizes of the parameters
also? It's odd because other stored procedures that I've written work fine when
they are called from Cold Fusion.</SPAN></FONT></P>
<P><FONT face=Arial size=2><SPAN
class=750523212-26062000></SPAN></FONT> </P>
<P><FONT face=Arial size=2><SPAN
class=750523212-26062000>James</SPAN></FONT></P>
<P></SPAN> </P></DIV>
<P><FONT face="Courier New" size=2> | james
page | database
developer |</FONT> <BR><FONT
face="Courier New" size=2> | UPC TV
interactive | </FONT><BR><FONT face="Courier New"
size=2> | w. +44 (2)0 7533 6573</FONT> <BR><FONT
face="Courier New" size=2> | [EMAIL PROTECTED]</FONT>
</P>
<DIV> </DIV></BODY></HTML>
------_=_NextPart_001_01BFDF6D.767FEBEC--
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
