I'm getting a datatype error: "Application uses a value of the wrong type for the current operation" when executing the following stored procedure:
CREATE PROCEDURE dbo.Insert_Temp_ContactInfo
@.sessionid varchar(50),
@.FirstName varchar(50) = NULL,
@.LastName varchar(50) = NULL,
@.SchoolName varchar(50) = NULL,
@.address varchar(50) = NULL,
@.City varchar(50) = NULL,
@.State int = NULL,
@.Zip varchar(5) = NULL,
@.Phone varchar(10) = NULL,
@.Email varchar(50) = NULL,
@.CurrentCustomer varchar(3) = NULL,
@.ImplementationType int = NULL,
@.ProductType int = NULL,
@.Comment varchar(500) = NULL
AS
--check if a current record exists
SET NOCOUNT ON
begin
UPDATE dbo.Temp_ContactInfo
SET
FirstName = @.FirstName,
LastName = @.LastName,
SchoolName = @.SchoolName,
Address = @.address,
City = @.City,
State = @.State,
Zip = @.Zip,
Phone = @.Phone,
Email = @.Email,
CurrentCustomer = @.CurrentCustomer,
ImplementationType = @.ImplementationType,
ProductType = @.ProductType,
Comment = @.Comment
WHERE
sessionid = @.sessionid
If @.@.Rowcount = 0
INSERT INTO dbo.Temp_ContactInfo
(sessionid,
FirstName,
LastName,
SchoolName,
address,
City,
State,
Zip,
Phone,
Email,
CurrentCustomer,
ImplementationType,
ProductType,
Comment)
VALUES
(@.sessionid,
@.FirstName,
@.LastName,
@.SchoolName,
@.address,
@.City,
@.State,
@.Zip,
@.Phone,
@.Email,
@.CurrentCustomer,
@.ImplementationType,
@.ProductType,
@.Comment)
end
GO
This is code I'm using to call the procedure:
set InsertTempInfo = Server.CreateObject("ADODB.Command")
With InsertTempInfo
.ActiveConnection = MM_DBConn_STRING
.CommandText = "dbo.Insert_Temp_ContactInfo"
.CommandType = 4
.CommandTimeout = 0
.Prepared = true
.Parameters.Append .CreateParameter("@.sessionid", 200, 1,50, usrid)
.Parameters.Append .CreateParameter("@.FirstName", 200, 1,50,fname)
.Parameters.Append .CreateParameter("@.LastName", 200, 1,50,lname)
.Parameters.Append .CreateParameter("@.SchoolName", 200, 1,50,schoolname)
.Parameters.Append .CreateParameter("@.address", 200, 1,50,address)
.Parameters.Append .CreateParameter("@.City", 200, 1,50,city)
.Parameters.Append .CreateParameter("@.State", 3, 1,4,state)
.Parameters.Append .CreateParameter("@.Zip", 200, 1,5,zip)
.Parameters.Append .CreateParameter("@.Phone", 200, 1,10,phone)
.Parameters.Append .CreateParameter("@.Email", 200, 1,50,email)
.Parameters.Append .CreateParameter("@.CurrentCustomer", 200, 1,3,currentcustomer)
.Parameters.Append .CreateParameter("@.ImplementationType", 3, 1,4,implementationtype)
.Parameters.Append .CreateParameter("@.ProductType", 3, 1,4,producttype)
.Parameters.Append .CreateParameter("@.Comment", 200, 1,500,comment)
.Execute()
End With
Set InsertTempInfo = Nothing
the error is thrown on the following line:
.Parameters.Append .CreateParameter("@.State", 3, 1,4,state)
I'm using a table to hold data that I can pass back to the original form page and re-populate the fields that were not validated correctly. The stored procedure either inserts or updates the record in the temp table I've created.
So, currently, as I'm testing, I'm just passing empty values to all the parameters and the @.state parameter is failing and throwing the error.
I've double checked that the table has the state column set to integer datatype
The column is set as follows:
Name datatype length Allow Nulls
--------------
State int 4 checked
I have tried setting the default value for every column to Null in the table and then also not using a default value. Either way, I still recieve the same error?
Not sure what else to look at?
It seems the problem might be that instead of a null value being passed to the parameter that it is actually empty. Can passing an empty value to a column of datatype integer cause this problem? If so, is there a way to correct it?
Thanks for any help.Basic thing to check: Make sure the data type you are passing to the stored procedure is of the same type as the stored procedure is expecting
