Solved

Insert NULL values into stored procedure in ASP

Posted on 2004-08-25
36
3,589 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:equentin
  • 13
  • 10
  • 7
  • +2
36 Comments
 
LVL 11

Expert Comment

by:ram2098
ID: 11890871
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
 
LVL 11

Expert Comment

by:Otana
ID: 11890911
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
 
LVL 11

Expert Comment

by:ram2098
ID: 11890942
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
 

Author Comment

by:equentin
ID: 11890989
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
 
LVL 11

Expert Comment

by:Otana
ID: 11891000
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
 
LVL 9

Expert Comment

by:crescendo
ID: 11891024
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
 
LVL 11

Expert Comment

by:ram2098
ID: 11891044
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
 
LVL 11

Expert Comment

by:ram2098
ID: 11891051
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
 

Author Comment

by:equentin
ID: 11891110
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
 
LVL 11

Expert Comment

by:Otana
ID: 11891122
exec sp_prepexecrpc 45, 2, 1, 'dg'  --> last parameter is varchar, not int. Your parameters are in the wrong order.
0
 
LVL 11

Expert Comment

by:ram2098
ID: 11891128
This answers your problem
...

see the fourth parameter you are passing

it is 'dg'

but, declared as @fk_ReturnFlight int
0
 
LVL 11

Expert Comment

by:Otana
ID: 11891131
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
 
LVL 11

Expert Comment

by:ram2098
ID: 11891136
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
 
LVL 11

Expert Comment

by:ram2098
ID: 11891185
Otana: you are very fast...:)
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11891187
The order that the parameters are created is unimportant. They are named, so the stored procedure knows exactly what it is getting.
0
 
LVL 11

Expert Comment

by:ram2098
ID: 11891204
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
 
LVL 9

Expert Comment

by:crescendo
ID: 11891235
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
 

Author Comment

by:equentin
ID: 11891252
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 9

Expert Comment

by:crescendo
ID: 11891290
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
 
LVL 11

Expert Comment

by:ram2098
ID: 11891297
So, what was coming in profiler now..after this change..is it same?
0
 
LVL 11

Expert Comment

by:Otana
ID: 11891308
ram2098: thnx :)

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

Expert Comment

by:ram2098
ID: 11891311
Yes..Crescendo solutions will also work..because it calls the stored procedure with parameter names
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11891385
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
 
LVL 9

Expert Comment

by:crescendo
ID: 11891392
That way you are supplying all the parameters, so there shouldn't be a problem.
0
 

Author Comment

by:equentin
ID: 11891517
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
 
LVL 1

Expert Comment

by:Parag_Gujarathi
ID: 11891567
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
 
LVL 9

Expert Comment

by:crescendo
ID: 11891570
@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
 

Author Comment

by:equentin
ID: 11891617
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
 
LVL 9

Expert Comment

by:crescendo
ID: 11891625
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
 
LVL 9

Expert Comment

by:crescendo
ID: 11891641
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
 
LVL 9

Expert Comment

by:crescendo
ID: 11891667
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
 

Author Comment

by:equentin
ID: 11891674
That expression's looking up a value of a form field
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11891682
Sorry, I was in a rush and didn't read it properly, you're building the name of a Request variable.
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11891694
It's better to put Request.Querystring. Your shorthand works because Querystring is the default property.
0
 
LVL 9

Accepted Solution

by:
crescendo earned 500 total points
ID: 11891737
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
 

Author Comment

by:equentin
ID: 11891766
Ok!  That'll do - spent waaaaaaaaaaay too long on something so silly.  Thanks again.
eq
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now