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.
equentinAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
crescendoConnect With a Mentor Commented:
OK, you can't pass NULL from a form, so you mean that there's no value? You need to check like this:

    If Request(x & "_fk_OutFlight") Is Nothing Then

That will tell you if no value was passed, so you then use NULL...

     If Request(x & "_fk_OutFlight") Is Nothing Then
          .Parameters.Append .CreateParameter("@fk_OutFlight", 3, 1, 4, Null)
     Else
          .Parameters.Append .CreateParameter("@fk_OutFlight", 3, 1, 4, Request(x & "_fk_OutFlight"))
     End If
     If Request(x & "_fk_ReturnFlight") Is Nothing Then
          .Parameters.Append .CreateParameter("@fk_ReturnFlight", 3, 1, 4, Null)
     Else
          .Parameters.Append .CreateParameter("@fk_ReturnFlight", 3, 1, 4, Request(x & "_fk_ReturnFlight"))
     End If
0
 
ram2098Commented:
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.
0
 
OtanaCommented:
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
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
ram2098Commented:
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).
0
 
equentinAuthor Commented:
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.
0
 
OtanaCommented:
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?
0
 
crescendoCommented:
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).
0
 
ram2098Commented:
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.
0
 
ram2098Commented:
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
0
 
equentinAuthor Commented:
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.
0
 
OtanaCommented:
exec sp_prepexecrpc 45, 2, 1, 'dg'  --> last parameter is varchar, not int. Your parameters are in the wrong order.
0
 
ram2098Commented:
This answers your problem
...

see the fourth parameter you are passing

it is 'dg'

but, declared as @fk_ReturnFlight int
0
 
OtanaCommented:
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.
0
 
ram2098Commented:
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
0
 
ram2098Commented:
Otana: you are very fast...:)
0
 
crescendoCommented:
The order that the parameters are created is unimportant. They are named, so the stored procedure knows exactly what it is getting.
0
 
ram2098Commented:
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.
0
 
crescendoCommented:
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.
0
 
equentinAuthor Commented:
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!
0
 
crescendoCommented:
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.
0
 
ram2098Commented:
So, what was coming in profiler now..after this change..is it same?
0
 
OtanaCommented:
ram2098: thnx :)

equentin, what happens if you create the paramters but don't put any values in them?
0
 
ram2098Commented:
Yes..Crescendo solutions will also work..because it calls the stored procedure with parameter names
0
 
crescendoCommented:
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
0
 
crescendoCommented:
That way you are supplying all the parameters, so there shouldn't be a problem.
0
 
equentinAuthor Commented:
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?
0
 
Parag_GujarathiCommented:
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.
0
 
crescendoCommented:
@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.
0
 
equentinAuthor Commented:
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
0
 
crescendoCommented:
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?
0
 
crescendoCommented:
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.
0
 
crescendoCommented:
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"?
0
 
equentinAuthor Commented:
That expression's looking up a value of a form field
0
 
crescendoCommented:
Sorry, I was in a rush and didn't read it properly, you're building the name of a Request variable.
0
 
crescendoCommented:
It's better to put Request.Querystring. Your shorthand works because Querystring is the default property.
0
 
equentinAuthor Commented:
Ok!  That'll do - spent waaaaaaaaaaay too long on something so silly.  Thanks again.
eq
0
All Courses

From novice to tech pro — start learning today.