Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 convert "" to NULL in ASP form, pass to SQL?

Author  Topic 

steelkilt
Constraint Violating Yak Guru

255 Posts
Posted - 2003-01-14 : 08:51:20
I need to convert "" (blank boxes) in my ASP forms to NULLs before sending these values on to an SPROC. I need true NULLs because the fields in question are SMALLINT data types on the SQL Server side.

I have read in various forums that encoding a NULL value in an html/asp form is a bad idea, i.e. that it can lead to funky results for inserts and updates.

My temporary solution is to convert "" values to a placeholder value that is disallowed by jscript anyway, i.e. "99" or some such, then convert the 99 via NULLIF in the SPROC.

I would rather pass a true NULL, for obvious reasons. Is this possible, i.e. what is the proper encoding technique to pass a true NULL?

My code is simple:

if (form.thisfield.value == "") {
form.thisfield.value = 99;
}

thx.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts
Posted - 2003-01-14 : 09:14:00
In asp convert if your form variable has "" set a variable = NULL
Then use the command object and pass that variable make sure you set the size of your parameter to zero.
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts
Posted - 2003-01-14 : 10:48:55
Thanks Valter.

In my ASP processing script, I'm using the parameters collection for these fields, as below:

.parameters.append .createparameter("@Widgets", adsmallint, adparaminput,,request.form("Widgets"))

Users are allowed to enter integers into the Widgets field. If I set the parameter value to zero to handle the passing of NULLs, how will this affect the passing of valid integers?

thx
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2003-01-14 : 10:56:00
I don't think you can handle it with a single CreateParameter call. In JavaScript, you *might* be able to use the ?: expression to substitute the zero length for a null value, and the proper length for a non-null. I'm not sure how to do it in VBScript but there are functions for IsNull, IsEmpty and so on, they might do the trick. Failing that, a normal if...then block can be used to handle it, using two CreateParameter calls.
Go to Top of Page

Onamuji
Aged Yak Warrior

504 Posts
Posted - 2003-01-14 : 11:17:20
if a null value is allowed, why not just set the default value for the parameter == null and then in the server code conditionally include the parameter only if a valid value was entered

if (!isNaN(parseInt(Request.Form["txtAge"])))
command.Parameters.Append(command.CreateParameter("@age", adInteger, adParamInput, 4, parseInt(Request.Form["txtAge"])));
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

1456 Posts
Posted - 2003-01-14 : 13:16:32
quote:

Users are allowed to enter integers into the Widgets field. If I set the parameter value to zero to handle the passing of NULLs, how will this affect the passing of valid integers?


Read Valter's post again steelkit
quote:
make sure you set the size of your parameter to zero.


He said set the size and not the value to zero

OS
Go to Top of Page

aiken
Aged Yak Warrior

525 Posts
Posted - 2003-01-14 : 14:27:41
Here's what I do in similar situations:

.parameters.append .createparameter("@Widgets", adsmallint, adparaminput) 
if request.form("Widgets")<>"" Then
  oCmd("@Widgets")=request.form("Widgets")
End If


(Of course, you didn't supply the name of the command object you're using, so I used oCmd in that sample).

Cheers
-b
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2003-01-14 : 14:33:55
quote:
He said set the size and not the value to zero


Mohdowais, whether steelkilt's response was a typo or honest mistake, you still have the problem of the size being set in the .createparameter statement and you'd have to re-define/re-create the parameter to change the size, which the others addressed.

I am curious why the need to set the size of the parameter to zero. I spent the last two years writing ASP code that had things like:

IF ValFound(Request.Form("txtActiveFlag")) = True Then
cmd.Parameters("ActiveFlag") = Request.Form("txtActiveFlag")
Else
cmd.Parameters("ActiveFlag") = Null
End If

(Note ValFound was a custom-written function to check various definitions of "no value")

I never had any problems with this. What is the problem with not changing the size of the parameter?

------------------------------------------------------
The more you know, the more you know you don't know.
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts
Posted - 2003-01-14 : 16:02:37
Thanks for all the tips.

Re: the suggestion that I include the parameter only if a valid (non null) value is passed, my SPROC is expecting the parameter, so I have to pass a value. This is why I want to pass a NULL when the box on the form is left empty. I'd rather clean up in the ASP script if possible and avoid adding more complexity to the SPROC.

AjarnMark's suggestion makes the most sense to me at this point, since his code tests the parameter value and then sets said value to NULL if no value is found.

AjarnMark -- can I ask you to post some code which details the function you mentioned? Below I have listed the basics of the ASP code which captures my form fields before passing them to the SPROC.

======================================================


<%


myDSN="working"

set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN


set objcmd=server.createobject("ADODB.command")
with objcmd
.activeconnection=myDSN
.commandtext = "sp_working"
.commandtype = adcmdstoredproc
'--set parameters
.parameters.append .createparameter("@Working", adsmallint, adparaminput,,request.form("Working"))
.parameters.append .createparameter("@Occupation", adsmallint, adparaminput,,request.form("Occupation"))



.execute()



End With

%>
Go to Top of Page

Onamuji
Aged Yak Warrior

504 Posts
Posted - 2003-01-14 : 16:15:16
quote:

Re: the suggestion that I include the parameter only if a valid (non null) value is passed, my SPROC is expecting the parameter, so I have to pass a value.


so just change your procedure to

CREATE PROCEDURE myProcedure (
@param1 INT,
@age INT = NULL
) ...

that way you don't have to pass the age parameter and the procedure knows how to handle it ... and of course setting the parameter Value property to vbscript "Null" works fine to...

-- note
I just want to let the world know that coding in all caps or no caps ... is just plain wrong! go back to programming school, or switch to a language that is case sensitive ... coding like that is no excuse, and i would have any programmer that does that fired or at least sent back to school

Edited by - onamuji on 01/14/2003 16:16:43
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts
Posted - 2003-01-14 : 16:44:20
I think my original post was not very clear, in retrospect. I need to allow users the option of passing NULL, i.e. they can pass a valid INT as well. So I don't want to hard code a NULL in the SPROC.

I guess I need something like

@age INT = (NULLIF (@age, ""))

but I suspect the "" will spawn a data type crash.

I need to do some testing...
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2003-01-14 : 16:54:54
Use something like -1 or some other numeric value that is not at all possibly valid in your table, and have NullIf() do the replacement.
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts
Posted - 2003-01-14 : 17:07:35
Yes, Rob, that's what I'm doing now. I was just hoping for a more elegant solution. <sigh>
Go to Top of Page

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts
Posted - 2003-01-14 : 17:35:17
quote:

command.Execute( RecordsAffected, Parameters, Options )
Parameters
This optional parameter specifies a Variant array of parameter values passed with an SQL statement and is not used by the OLE DB Provider for AS/400 and VSAM


You can use the Parameters parameter (sounds weird) to pass in an array of your parameter values.

Array(param1,param2,NULL)

like so:
command.Execute (,Array(param1,param2,NULL),)


in the
command.CreateParameter (Name, Type, Direction, Size, Value)

Size is optional so just leave it blank
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2003-01-15 : 02:50:21
quote:
AjarnMark -- can I ask you to post some code which details the function you mentioned?

Steelkilt, do you mean my custom ValFound function? (I actually took this from a company where I used to work, so can't take credit as author).

This is a VBScript function that I just kept in an include file called CommonTools.asp which was a file that got included into 99.9% of my ASP pages because it contained the functions that were used nearly all the time. Here ya go:

Function ValFound(sVal)
  'Normally we consider an empty string, a blank string, and Null all as equally "no-value".  
  'In order to make the code easier to read, use this function instead of typing in all three 
  'tests each time.  Often this will be used in a conditional such as: 
  'If ValFound(Request.Form("txtFirstName")) Then ...
	
  If Not IsEmpty(sVal) And Not IsNull(sVal) And Trim(sVal) <> "" Then
    ValFound = True
  Else
    ValFound = False	
  End If
End Function


------------------------------------------------------
The more you know, the more you know you don't know.

Edited by - AjarnMark on 01/15/2003 02:52:48
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site