Getting Invalid character value for cast specification. error when asp page calls sp

I am working on an asp app to convert it from id's to UUID's.   Using the code below i can successfully update a employee's record,  but when I try to add a new record i get the error below.


What am I doing wrong?



Microsoft OLE DB Provider for SQL Server error '80040e21'

Invalid character value for cast specification.

employee.asp, line 50

asp page---
 
	' create and open ADODB objects
	dim objConn, objRS, objCmd
	set objConn = server.CreateObject("ADODB.Connection")
	set objRS = server.CreateObject("ADODB.Recordset")
	set objCmd = server.CreateObject("ADODB.Command")
	objConn.ConnectionString = application("DSN")
	objConn.Open 
	objRS.ActiveConnection = objConn
	objCmd.ActiveConnection = objConn
	
	' update the record if submitted by form
	if Request.Form("employee") <> "" then 
		strEmployee_Key = Request.Form("employee")
		strCompany_Key = Request.Form("company")
 
		if Request.Form("delete") <> "Delete" then
			objCmd.CommandText = "sp_AddEmployee"
			objCmd.CommandType = adCmdStoredProc
			objCmd.Parameters.Refresh
			objCmd.Parameters("@Company_Key").Value = strCompany_Key 
			objCmd.Parameters("@Employee_Key").Value = strEmployee_Key
			objCmd.Parameters("@FirstName").Value = ucase(Request.Form("firstname"))
			objCmd.Parameters("@LastName").Value = ucase(Request.Form("lastname"))
			objCmd.Parameters("@Title").Value = ucase(Request.Form("title"))
			objCmd.Parameters("@Phone").Value = ucase(Request.Form("phone"))
			objCmd.Parameters("@Fax").Value = ucase(Request.Form("fax"))
			objCmd.Parameters("@Email").Value = ucase(Request.Form("email"))
			objCmd.Parameters("@TID").Value = Request.Form("tcode")
			'objCmd.Parameters("@OID").Value = Request.Form("ocode")
			'objCmd.Parameters("@ZID").Value = Request.Form("zcode")
			'objCmd.Parameters("@QID").Value = Request.Form("qcode")
			'objCmd.Parameters("@AddCode").Value = ucase(Request.Form("addcode"))
		     objCmd.Parameters("@User_Key").Value = session("User_Key")
			 
			 set objReturn = objCmd.Execute
				
			strEmployee_Key = objReturn(0)
			
			strMessage = "Employee Record Updated"
		else
			if strEmployee_Key <> "00000000-0000-0000-0000-000000000000" then 
				' delete the record
				objCmd.CommandText = "exec sp_DeleteEmployee '" & strEmployee_Key & "', '" & session("User_Key") & "'"
				objCmd.Execute
				strMessage = "Employee Record Deleted"
				strEmployee_Key = "00000000-0000-0000-0000-000000000000"
			else
				strMessage = "Nothing to Delete!"
			end if
		end if
	end if
 
 
 
 
 
 
stored procedure--
 
 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
 
 
 
ALTER PROCEDURE [dbo].[sp_AddEmployee] 
	@Employee_Key uniqueidentifier,
	@Company_Key UniqueIdentifier,
	@FirstName varchar(50),
	@LastName varchar(50),
	@Title varchar(50),
	@Phone varchar(50),
	@Fax varchar(50),
	@Email varchar(255),
	@TID int,
	--@OID int,
	--@ZID int,
	--@QID int,
	--@AddCode varchar(60),
	@User_Key UniqueIdentifier
AS
begin
set nocount on
if @Employee_Key = '00000000-0000-0000-0000-000000000000' or @Employee_Key is null
begin
select @Employee_Key=Newid()
 
 
	-- new employee, so create a new record first
	insert into tblEmployee (Employee_Key,Company_Key) values (@Employee_Key,@Company_Key)
	-- Add log
	insert into tblEventLog (User_Key, Event_Key, Description) values (@User_Key, '6658b02c-9a9c-43cf-b63b-3afaad76168d',cast(@Employee_Key as varchar(40)))
end
 
-- update the rest of the record
update tblEmployee set FirstName = @FirstName,
	LastName = @LastName, 
	Title = @Title, 
	Phone = @Phone,
	Fax = @Fax,
	Email = @Email, 
	TID = @TID
--	OID = @OID,
--	ZID = @ZID,
--	QID = @QID,
--	AddCode = @AddCode
where Employee_Key = @Employee_Key
-- Add log
insert into tblEventLog (User_Key, Event_Key, Description) values (@User_Key, '188143f4-e805-401e-a3c1-c102c74dd6cd', cast(@Employee_Key as varchar(40)))
-- Return Employee_Key to calling application
 
select @Employee_Key as Employee_Key
end

Open in new window

polobruceAsked:
Who is Participating?
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

Line 90 of your code snipet. You might want to check that line out - you may need to case newid() to varchar( 36 ) .

Cheers
  David
0
 
boro_bobCommented:
It looks like you are casting your employee key as a VarChar.

cast(@Employee_Key as varchar(40))

Could that be the problem? Check your database table and see if that column is Varchar or UniqueIdentifier.
0
 
polobruceAuthor Commented:
no it's a varchar field so I don't see that as being an issue.  This code works right on update just not when inserting a new record.
0
 
Anthony PerkinsCommented:
Please attend to your open questions.  The following questions are now considered abandoned:
1 12/21/2007 250 Wierd Session issue Open ColdFusion Applic&
2 10/29/2007 500 Extending table data with custom fields columns Open SQL Server 2& & 
3 08/23/2007 250 cfif statement within onrequest application.cf& Open ColdFusion Applic&
4 08/20/2007 500 Dynamic Image in Adobe flex using cfm page n& Open Macromedia Flash
5 04/26/2007 500 SonicWall ActiveDirectory VPN Setup - Crede& Open VPN & 
6 03/14/2007 250 Parse full name into first, middle last and Upda& Open SQL Server 2&
0
All Courses

From novice to tech pro — start learning today.