Unable to pass a parameter to a stored proc

[bra1niac]

I'm trying to pass a null value to my stored proc.

On my asp page, I first deal with the variable like so:

ClassID = IIf(Request("ddlClass&quot = "", NULL,Request("ddlClass&quot)

I then use the command object to send my params to the stored proc:

Set cmd = CreateObject("ADODB.Command&quot
cmd.ActiveConnection = conn
cmd.CommandText = "mySP"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@ClassID",adInteger , adParamInput, , classid)
cmd.Execute

NOTE: There ARE other params that I have left out for simplicity sake. In this case, what works for one works for all.

This, however, gives me no results when I should have X number of rows returned.

I execute the very same stored proc in Query Analyzer like so:
mySP null, 1,3,5

This gives me the result set that I expect to get.

My question: Is there an issue passing null to the database from an asp page being that the variables aren't typed?

I've tried virtually everything I could think of. I just can't seem to get my recordset returned to me... I do, in fact, bind the recordset to the command object to display the results, and my recordcount is always -1

"It's easier to ask forgiveness than it is to get permission." - Rear Admiral Dr. Grace Hopper

 

[mwolf00]

"This, however, gives me no results when I should have X number of rows returned."

If rowws are to be returned you need a recordset

cmd.execute
set rs = cmd.execute

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[bra1niac]

I have one a bit later on:

Set rs = CreateOjbect("ADODB.Recordset&quot
rs.Open cmd,,adOpenStatic,adLockBatchOptimistic

This seems to work, normally.

However, I changed my code to match your suggestion and tested it out. It gave me the exact same result I've been seeing.

I still think the issue is in how asp transports that null value to sql server. I know in .net I have to convert it to dbNull before it will work, but I have no idea what I'm supposed to do with it in classic asp.

"It's easier to ask forgiveness than it is to get permission." - Rear Admiral Dr. Grace Hopper

 

[mwolf00]

why not pass the word null in quotes (as a string "NULL&quot and have the SP convert it back to null

Another obvious question, does the sp allow nulls?

another method(no command object needed)

cn.execute ("EXEC mySP " & classID)



Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[bra1niac]

I can't pass it in as a string because the parameters collection and the stored proc are both expecting an int.

The declaration of the variable in the stored proc:
@ClassID int = NULL,

I wouldn't be having such a tough time with this if I knew what to point the finger at. If I didn't see the results I am supposed to see when I run the sp from query analyzer, then I would start thinking about addressing the sp first. However, I seems to work great. It has me a bit stymied right now.

I don't see that it's likely I can go without the command object. It's no small sp I'm dealing with. I need the ability to have my params in variable form to reuse them a few times and I'm not sure building a sql string will allow me to do that properly.

I thought your last suggestion was a stroke of genius. Here's what it looked like:
cmd.CommandText = "mySP " & classid

Didn't seem to change a thing, however. That one really bummed me out. Thought we had a class A work-around...

"It's easier to ask forgiveness than it is to get permission." - Rear Admiral Dr. Grace Hopper

 

[mwolf00]

this should work...

ClassID = IIf(Request("ddlClass&quot = "", "NULL",Request("ddlClass&quot)
cn.execute ("EXEC mySP " & classID)


NOTE that I didn't put any quotes around the value...
It's the same as using "EXEC mySP null" in query analyzer...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[bra1niac]

are you saying to execute the sp off the connection object? if so, how do you bind back to the recordset?

"It's easier to ask forgiveness than it is to get permission." - Rear Admiral Dr. Grace Hopper

 

[mwolf00]

set rs = cn.execute("EXEC mySP " & classID)

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[baddos]

Just put the keyword NULL not in quotes though....

Dim classid
classid = Request("ddlClass&quot
If IsNull(classid) OR Trim(classid) = "" Then
classid = NULL
End If
cmd.Parameters.Append cmd.CreateParameter("@ClassID",adInteger , adParamInput, , classid)