Stored procedure - Error converting data type varchar to int. 2

[asiavoices]

Hello all,

I have problem that I cannot seem to narrow down.

Basically, my sp simply adds a new record to my db.

The Error I am getting is:

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to bit.

SQL = "usp_AddContact"

Data Source = "MYDBsource"

The error occurred while processing an element with a general identifier of (CFSTOREDPROC), occupying document position (9:2) to (9:72).

===============================

My observation:

The error seems to have stemmed from my "IsContactActive" field.


=====================================
Here is my form's SP code.

<cfstoredproc procedure=&quot;usp_AddContact&quot; datasource=&quot;#application.ds#&quot;>
<cfprocparam type=&quot;In&quot; cfsqltype=&quot;cf_sql_bit&quot; dbvarname=&quot;@IsContactActive&quot; value=&quot;#form.IsContactActive#&quot;>
<cfprocparam type=&quot;in&quot; cfsqltype=&quot;cf_sql_varchar&quot; dbvarname=&quot;@FirstName&quot; value=&quot;#Trim(form.FirstName)#&quot;>
<cfprocparam type=&quot;in&quot; cfsqltype=&quot;cf_sql_varchar&quot; dbvarname=&quot;@LastName&quot; value=&quot;#Trim(form.LastName)#&quot;>
<cfprocparam type=&quot;in&quot; cfsqltype=&quot;cf_sql_varchar&quot; dbvarname=&quot;@Title&quot; value=&quot;#Trim(form.Title)#&quot; null=&quot;#IIf(Trim(form.Title) EQ &quot;&quot;, 1, 0)#&quot;>
........
</cfstoredproc>



===============================

On my form:

<form name=&quot;addcontactform&quot; action=&quot;process.cfm&quot;>

.....

<input type=&quot;radio&quot; name=&quot;IsContactActive&quot; value=&quot;1&quot; checked> Active
<input type=&quot;radio&quot; name=&quot;IsContactActive&quot; value=&quot;0&quot;> Inactive

....
</form>



===========================

My &quot;usp_AddContact&quot; procedure


CREATE PROCEDURE usp_AddContact
@ContactID int,
@IsContactActive bit,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Title nvarchar(50) = null,
@Company nvarchar(50) = null,
@Address nvarchar(50) = null,
@City nvarchar(50) = null,
@ProvinceID int = null,
@OtherProvince nvarchar(99) = null,
@CountryID int = null,
@ZipCode nvarchar(50) = null,
@Phone nvarchar(50) = null,
@Cell nvarchar(50) = null,
@Fax nvarchar(50) = null,
@Emergency nvarchar(50) = null,
@Email nvarchar(50) = null,
@Notes text = null,
@IP nvarchar(16) = null

OUTPUT AS

BEGIN TRANSACTION

INSERT INTO tbl_Contacts
(IsContactActive, FirstName, LastName, Title, Company, Address, City, ProvinceID, OtherProvince, CountryID, ZipCode, Phone, Cell, Fax, Emergency, Email, Notes, IP)
VALUES (@isContactActive, @FirstName, @LastName, @Title, @Company, @Address, @City, @ProvinceID, @OtherProvince, @CountryID, @ZipCode, @Phone, @Cell, @Fax, @Emergency, @Email, @Notes, @IP)


SET @ContactID = @@identity

COMMIT TRANSACTION
GO



=========================================

MY tbl_Contact table

CREATE TABLE [dbo].[tbl_Contacts] (
[ContactID] [int] IDENTITY (1, 1) NOT NULL ,
[IsContactActive] [bit] NOT NULL ,
[IsContactDelete] [bit] NULL ,
[FirstName] [nvarchar] (80)
[LastName] [nvarchar] (80)
[Title] [nvarchar] (80)
[Company] [nvarchar] (100)
[Address] [nvarchar] (125)
[City] [int] NULL ,
[ProvinceID] [int] NULL ,
[OtherProvince] [nvarchar] (100)
[CountryID] [int] NULL ,
[Zipcode] [nvarchar] (25)
[Phone] [nvarchar] (20)
[Cell] [nvarchar] (20)
[Fax] [nvarchar] (20)
[Emergency] [nvarchar] (20)
[nvarchar] (100)
[Notes] [ntext]
[DateCreated] [datetime] NULL ,
[DateModified] [datetime] NULL ,
[IP] [nvarchar] (16)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



======================


Any ideas? It seems fine to me...

I'd like to use stored procedures to do all database transaction rather than cfquery to make it more efficient.

Thanks,

Christopher

 

[davtri]

Christopher,

Try trimming your passed variables, maybe even a test for isnumeric on that misbehaving variable.

This problem tends to occur when a variable is passed that the SQL server can not see as a numeric value (it could be getting &quot;0 &quot; instead of &quot;0&quot;.

You can also change the format that is calling the stored proc to something like this

<cfquery name=&quot;whatever&quot; datasource=&quot;whatever&quot;>
exec stored_proceedure_name variable='#trim(form.variable)#', variable2 = '#trim(form.variable2)#', etc.....
</cfquery>


Hope this helps,

David Giffin
Davtri Interactive Design

http://www.davtri.com

 

[asiavoices]

Hi David,

Thanks for your feedback....

I found out what the problem was... it was the order of the <cfprocparam> variables compared to that in my stored procedures. I found out by process of elimination that the db was accepting the incorrect fields into the table.

Thought I'd like to share this with everyone,

Thanks,

Christopher