Insert NULL values into stored procedure in ASP
Posted on 2004-08-25
I've got an odd problem here with calling a stored procedure - I've tried various combinations but none seem to work. It's something I can do easily in an inline SQL statement... I've got the following asp code:
Set objCMD = Server.CreateObject("ADODB.Command")
.ActiveConnection = DB_CONN_STRING
.CommandText = "dbo.spUpdateDelegate"
.CommandType = 4
.CommandTimeout = 0
.Prepared = true
.Parameters.Append .CreateParameter("@pk_Delegate", 3, 1, 4, Request(x & "_pk_Delegate"))
If Request(x & "_fk_OutFlight") <> "" Then
.Parameters.Append .CreateParameter("@fk_OutFlight", 3, 1, 4, Request(x & "_fk_OutFlight"))
If Request(x & "_fk_ReturnFlight") <> "" Then
.Parameters.Append .CreateParameter("@fk_ReturnFlight", 3, 1, 4, Request(x & "_fk_ReturnFlight"))
.Parameters.Append .CreateParameter("@Modifier", 200, 1, 50, session("svMgrInitials"))
and the following sp:
CREATE PROCEDURE [dbo].[spUpdateDelegate]
@fk_Outflight int = NULL,
@fk_ReturnFlight int = NULL
SET fk_Outflight = @fk_Outflight,
fk_ReturnFlight = @fk_ReturnFlight,
Modifier = @Modifier,
DateModified = getDate()
WHERE pk_Delegate = @pk_Delegate
Yet it's failing with:
Error converting data type varchar to int. as.
I simply want to keep the fields fk_Outflight and fk_ReturnFlight as NULLS if they don't contain any value in the form field.
Any help would be appreciated.