Error when trying to insert data using <cfstoredproc>

[dnayana]

I have a field (i.e. txtPhoneNo, txtEmail) within my db that allows users to leave the field empty.

The result I am trying to achieve within the db is to insert a space (i.e., ' ') in the field if user left the field empty.

I was trying to check the value using the IIf statement, but receive a "String index out of range: 0" error.

Here's my stored procedure code:

CREATE PROCEDURE [spRequesterAdd]
	(
	/*Add '=null' to fields where the user must provide input */
	@txtFirstName varchar(50)=null,
	@txtLastName varchar(50)=null,
	@intOrganization int=null,
	@txtPhoneNo varchar(50),
	@txtEmail varchar(50)
	)

 AS
	BEGIN
	INSERT INTO 	tblRequester
			(
			txtFirstName,
			txtLastName,
			intOrganization,
			txtPhoneNo,
			txtEmail)
	VALUES
			(			
			RTrim(@txtFirstName),
			RTRIM(@txtLastName),
			RTRIM(@intOrganization),
			RTRIM(@txtPhoneNo),
			RTRIM(@txtEmail)
			)
	END
GO

Here's my <cfstoredproc> code:

<cfstoredproc procedure="spRequesterAdd" datasource="#variables.DSN#" returncode="Yes">

<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@txtFirstName" value="#Form.txtFirstName#" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@txtLastName" value="#Form.txtLastName#" null="No">			
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@intOrganization" value="#Form.intOrganization#" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@txtPhoneNo" value="#IIf(Len(Form.txtPhoneNo) IS 0, DE(" "), "#Form.txtPhoneNo#")#" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@txtEmail" value="#IIf(Len(Form.txtEmail) IS 0, DE(" "), "#Form.txtEmail#")#" null="No">
</cfstoredproc>

Please tell me where I'm going wrong at or if my idea is even feasible. Also, please let me know if there is a better solution to what I'm trying to accomplish.

Thanks in advance to anyone who can be of assistance!!

 

[ecobb]

Try removing the "=null" from your variables at the beginning of your Stored Procedure. And remove the whole "IIf" stuff, too. If there's not a value there, it will just insert a "blank". I insert "blanks" for NULLS all the time with cfstoredproc and I've never run into this problem.



Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher