asked on

Insert NULL values into stored procedure in ASP

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")
With objCMD
      .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"))
      End If
      If Request(x & "_fk_ReturnFlight") <> "" Then
            .Parameters.Append .CreateParameter("@fk_ReturnFlight", 3, 1, 4, Request(x & "_fk_ReturnFlight"))
      End If
      .Parameters.Append .CreateParameter("@Modifier", 200, 1, 50, session("svMgrInitials"))
      .Execute()
End With

and the following sp:

CREATE PROCEDURE [dbo].[spUpdateDelegate]

@pk_Delegate int,
@Modifier varchar(50),
@fk_Outflight int = NULL,
@fk_ReturnFlight int = NULL

AS

UPDATE tblDelegates

SET      fk_Outflight = @fk_Outflight,
      fk_ReturnFlight = @fk_ReturnFlight,
      Modifier = @Modifier,
      DateModified = getDate()

WHERE pk_Delegate = @pk_Delegate
GO

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.

ram2098

Can you run the SQL profiler and see what are the values that are passing to this procedure..

If you can paste that here..we can help you with the problem.

Otana

Here's a workaround:

When you want to pass NULL, pass a value of which you're certain it's never going to get in that variable, for instance -1. Then, in your stored procedure, check for that value, and replace with NULL:

IF @Parameter = -1 THEN SET @Parameter = NULL

ram2098

Otana:

I think what equentin doing will work since they are optional parameters and he is passing a value based on an if clause (so by default it takes NULL value if it is not passed).

equentin

ASKER

Thans for the feedback guys, but I don't know how to use the profiler and I've spent way too long on this problem, so I'm just going to re-write as an inline sql statement.

Otana

It should work but maybe for some reason the string "NULL" is passed or something like that?

equentin, does the error only occur when those fields are empty? It might be the error has nothing to do with those fields.
And are you sure those fields ARE empty, and that you don't get a [space] or something?

crescendo

equentin:

How is Modifier declared in your table? Is it an int? The error message seems to be suggesting that it can't save @Modifier (a varchar) in to an int (the Modifier column).

ram2098

Go to Start Menu ==> Programs ==> Microsoft SQL Server ==> Profiler

Login with your userid and password.

Execute your asp code..profiler will show your how you are executing the procedure. This way, you can solve the problem on your own.

ram2098

I mean after logging into profiler, go to your program and execute your asp code..then it will show all the sql statements that are executed by your program in the profiler

equentin

ASKER

I'm not entirley sure how usefull this will be but here is some of the relevant info from the profiler trace:

Without paramaters:

exec sp_prepexecrpc 44, 'dg'

declare @P1 int
set @P1=2
exec sp_prepexecrpc @P1 output, N'dbo.spUpdateDelegate', 44, 'dg'
select @P1

With Paramaters:

exec sp_prepexecrpc 45, 2, 1, 'dg'

declare @P1 int
set @P1=NULL
exec sp_prepexecrpc @P1 output, N'dbo.spUpdateDelegate', 45, 2, 1, 'dg'
select @P1

Any ideas much appreciated.

Otana

exec sp_prepexecrpc 45, 2, 1, 'dg' --> last parameter is varchar, not int. Your parameters are in the wrong order.

ram2098

This answers your problem
...

see the fourth parameter you are passing

it is 'dg'

but, declared as @fk_ReturnFlight int

Otana

In your code, move following line:

.Parameters.Append .CreateParameter("@Modifier", 200, 1, 50, session("svMgrInitials"))

above the IF statements, and the order should be correct.

ram2098

Modify the code this way..it will work

Set objCMD = Server.CreateObject("ADODB.Command")
With objCMD
.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"))
.Parameters.Append .CreateParameter("@Modifier", 200, 1, 50, session("svMgrInitials"))
If Request(x & "_fk_OutFlight") <> "" Then
.Parameters.Append .CreateParameter("@fk_OutFlight", 3, 1, 4, Request(x & "_fk_OutFlight"))
End If
If Request(x & "_fk_ReturnFlight") <> "" Then
.Parameters.Append .CreateParameter("@fk_ReturnFlight", 3, 1, 4, Request(x & "_fk_ReturnFlight"))
End If
.Execute()
End With

ram2098

Otana: you are very fast...:)

crescendo

The order that the parameters are created is unimportant. They are named, so the stored procedure knows exactly what it is getting.

ram2098

Crescendo: But, see the profiler how it is getting the proc..it is not coming with the parameter names.

I agree with you if it is coming with the parameter names.

crescendo

Well, I've never had a problem with the order of the parameters, so I tend to suspect Profiler is not giving the full picture.

equentin

ASKER

Ok - just moving around the order doesn't work.

For example: if there isn't an Outflight but there IS a ReturnFlight, it puts the parameter for ReturnFlight in Outflight. D'oh!

crescendo

equentin:

Looking at your code, I wonder if you are trying to use too much shorthand? Normally, you would add parameters like this:

Dim prm as ADODB.Parameter

Set prm = objCMD.CreateParameter("@pk_Delegate", 3, 1, 4, Request(x & "_pk_Delegate"))
objCMD.Parameters.Append prm

Set prm = objCMD.CreateParameter("@Modifier", 200, 1, 50, session("svMgrInitials"))
objCMD.Parameters.Append prm

Set prm = objCMD.CreateParameter("@fk_OutFlight", 3, 1, 4, Request(x & "_fk_OutFlight"))
objCMD.Parameters.Append prm

Set prm = objCMD.CreateParameter("@fk_ReturnFlight", 3, 1, 4, Request(x & "_fk_ReturnFlight"))
objCMD.Parameters.Append prm

I've never seen or used your shorthand way before, and just wonder if it causing problems. It looks neat, so neat that I would have expected the books to have suggested it, but my books don't.

ram2098

So, what was coming in profiler now..after this change..is it same?

Otana

ram2098: thnx :)

equentin, what happens if you create the paramters but don't put any values in them?

ram2098

Yes..Crescendo solutions will also work..because it calls the stored procedure with parameter names

crescendo

equentin:

If this is taking too long to solve, a quick and dirty solution would be to assign "Null" to the values in question, and pass that to the stored procedure, as in:

If Request(x & "_fk_OutFlight") <> "" Then
.Parameters.Append .CreateParameter("@fk_OutFlight", 3, 1, 4, Request(x & "_fk_OutFlight"))
Else
.Parameters.Append .CreateParameter("@fk_OutFlight", 3, 1, 4, Null)
End If
If Request(x & "_fk_ReturnFlight") <> "" Then
.Parameters.Append .CreateParameter("@fk_ReturnFlight", 3, 1, 4, Request(x & "_fk_ReturnFlight"))
Else
.Parameters.Append .CreateParameter("@fk_ReturnFlight", 3, 1, 4, Null)
End If

crescendo

That way you are supplying all the parameters, so there shouldn't be a problem.

equentin

ASKER

crescendo, yes, that workaround works thank you - though I'm still a bit mystified as to why I'd have to do that - still, no matter. I also tried your method of adding the parameters and that didn't work - I had the following error:

Application uses a value of the wrong type for the current operation.

For the line:
Set prm = objCMD.CreateParameter("@fk_ReturnFlight", 3, 1, 4, Request(x & "_fk_ReturnFlight"))

Whenever a NULL is passed again. Any ideas?

Parag_Gujarathi

its because of the order in which you are passing the parameters is wrong....The code should be as follows :

.Parameters.Append .CreateParameter("@pk_Delegate", 3, 1, 4, Request(x & "_pk_Delegate"))
.Parameters.Append .CreateParameter("@Modifier", 200, 1, 50, session("svMgrInitials"))
If Request(x & "_fk_OutFlight") <> "" Then
.Parameters.Append .CreateParameter("@fk_OutFlight", 3, 1, 4, Request(x & "_fk_OutFlight"))
End If
If Request(x & "_fk_ReturnFlight") <> "" Then
.Parameters.Append .CreateParameter("@fk_ReturnFlight", 3, 1, 4, Request(x & "_fk_ReturnFlight"))
End If
.Execute()

In your case the fk_returnflight was being assigned the value of @modifier, which was a varchar value.

cheers,
Parag.

crescendo

@fk_ReturnFlight is an int, and you are creating a value for it by using the concatenation operator (&) which VBScript will take to mean that the result is a string, not an int.

equentin

ASKER

crescendo, but using the concatenation works fine when there's an int value - only when there's a NULL value is it causing a problem

crescendo

Also, looking at your code, expressions like

x & "_fk_OutFlight"

will never equal an empty string, because they will always be at least "_fk_OutFlight". Should you be looking up a field from a recordset or something?

crescendo

equentin:

<<using the concatenation works fine when there's an int value - only when there's a NULL value is it causing a problem>>

That's because any operation involving a NULL gives a result of NULL.

crescendo

equentin:

That's because any operation involving a NULL gives a result of NULL, so you can't compare it to anything.

When you say there's an int value, you mean in "x"?

equentin

ASKER

That expression's looking up a value of a form field

crescendo

Sorry, I was in a rush and didn't read it properly, you're building the name of a Request variable.

crescendo

It's better to put Request.Querystring. Your shorthand works because Querystring is the default property.

ASKER CERTIFIED SOLUTION

crescendo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

equentin

ASKER

Ok! That'll do - spent waaaaaaaaaaay too long on something so silly. Thanks again.
eq